Outlook to Excel & Excel to send mail

[Nitesh Khot]

Hi..

Is there any trigger to check new mail in subfolder of inbox (i.e Inbox then subfolder "Mails FromFriends") then export sender name and subject into excel database then create unique number and send again mail to sender...

if it is possible then how can we write code for the same...

Thanks inn advance...

Nikh

 

[Kmahraz]

Hi Nitesh - I am not an expert; but you may want to take a look at the thread below, i think it's a good start to help with your request:

http://forum.chandoo.org/threads/retrieving-data-from-mails-outlook-using-excel-vba.11773/

Hope this help!

K

 

[Nitesh Khot]

I have gone thr' this but can't get satisfied answer....i.e => How to create unique number when export mail into excel and if i received multiple mails at same time then how to handle this...

 

[Kmahraz]

Add the code below to your existing code to open Outlook, get a reference to the folder and find a particular mail item - changing the Object names, if required, of course.

MsgBox "ID: " & vbTab & oMailItem.EntryID & vbCrLf & _
"Sender:" & vbTab & oMailItem.SenderName & vbCrLf & _
"Subject:" & vbTab & oMailItem.Subject & vbCrLf & _
"Received:" & vbTab & Format$(oMailItem.ReceivedTime, "Dd-mm-yy hh:nn")

I think that each email in outlook has it's unique ID, but am no expert in the emailing process, some of the Ninjas may be able to elaborate if you need more help.

Using the below code, you can directly extract Email ID from a selected Outlook Mail. [Not Tested]

SubEmailAddress_From_Outlook_To_Excel()
'Clear Data Columns to Write Output
ThisWorkbook.Sheets(1).Columns(1).ClearContents
ThisWorkbook.Sheets(1).Columns(2).ClearContents
ThisWorkbook.Sheets(1).Columns(3).ClearContents
DimRecipList AsRecipients
Dimaddtype AsOlAddressEntryUserType
'Get Subject of Selected Email ID
On ErrorGoToFatal_Error:
ThisWorkbook.Sheets(1).Cells(1,1)=Outlook.ActiveExplorer.Selection.Item(1).Subject
MsgBox"You have Selected the Email with Subject: "&ThisWorkbook.Sheets(1).Cells(1,1)
ThisWorkbook.Sheets(1).Cells(2,1)="To"
'Get To, CC & BCC from a Outlook Email
SetRecipList=Outlook.ActiveExplorer.Selection.Item(1).Recipients
ThisWorkbook.Sheets(1).Cells(1,2)="Number Of Mail IDs: "&RecipList.Count
iRow=2
'Process Each Mail Contact
ForMailIdx=1ToRecipList.Count
'Check whether Contact already has a Mail ID
addtype=RecipList.Item(MailIdx).AddressEntry.AddressEntryUserType
Ifaddtype<>olExchangeUserAddressEntry Then
GoToError_Fetch_Next:
EndIf
'Get Mail Address
On ErrorGoToError_Fetch_Next:
ThisWorkbook.Sheets(1).Cells(MailIdx+2,1)=RecipList.Item(MailIdx).AddressEntry.GetExchangeUser.PrimarySmtpAddress
GoToProcess_Next_Contact
Error_Fetch_Next:
ThisWorkbook.Sheets(1).Cells(MailIdx+2,1)=RecipList.Item(MailIdx).Address
Ifaddtype=30Then
ThisWorkbook.Sheets(1).Cells(MailIdx+2,2)=""
Else
ThisWorkbook.Sheets(1).Cells(MailIdx+2,2)=addtype
EndIf
Process_Next_Contact:
Next
MsgBox"Process Completed"
ExitSub
Fatal_Error:
MsgBox"Fatal Error: Check Outlook is running & any Email is selected to Process"
EndSub

Hope this help!

-K-