Fetch Email Addresses – Quick Install & Test Code
- Open MS Outlook and Select any mail in Inbox or any other Folder
- Open new Excel Workbook, Alt+F11, Copy Paste the code in this Article
- In Excel VB Editor go to Tools->References->Add Microsoft Outlook nn.n Object Library
- Press F5.
Also Read: Install WhatsApp in Windows PC
All Email-Ids (“To”) in the mail you have selected is extracted to Excel. Sample Template is attached at the end of this article.
Fetch Recipients Email Ids to Excel
Some mails that we receive from Outlook have too many mail ids in “To”, “CC” or “BCC” sections.
Also Read: Execute Windows Apps from Excel
If you want to extract these mails ids to an Excel Sheet for any tracking purpose, you have to copy them, paste to notepad and choose each contacts separately or you can choose to import it into excel with delimiter as “;”. Either ways, you will get only the contact name but not the actual Email Id.
Read Selected Mail Details from Outlook
Using the below code, you can directly extract Email ID from a selected Outlook Mail. Follow the instructions mentioned in Quick Install at the beginning of this post.
Download Excel Macro To Import Email Ids from Outlook Mailto Excel Downloaded 499 Times
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Visit our website for more Tips and Tricks ' ' ---- Officetricks.com ---- ' ' ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Sub EmailAddress_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 Dim RecipList As Recipients Dim addtype As OlAddressEntryUserType 'Get Subject of Selected Email ID On Error GoTo Fatal_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 Set RecipList = Outlook.ActiveExplorer.Selection.Item(1).Recipients ThisWorkbook.Sheets(1).Cells(1, 2) = "Number Of Mail IDs: " & RecipList.Count iRow = 2 'Process Each Mail Contact For MailIdx = 1 To RecipList.Count 'Check whether Contact already has a Mail ID addtype = RecipList.Item(MailIdx).AddressEntry.AddressEntryUserType If addtype <> olExchangeUserAddressEntry Then GoTo Error_Fetch_Next: End If 'Get Mail Address On Error GoTo Error_Fetch_Next: ThisWorkbook.Sheets(1).Cells(MailIdx + 2, 1) = RecipList.Item(MailIdx).AddressEntry.GetExchangeUser.PrimarySmtpAddress GoTo Process_Next_Contact Error_Fetch_Next: ThisWorkbook.Sheets(1).Cells(MailIdx + 2, 1) = RecipList.Item(MailIdx).Address If addtype = 30 Then ThisWorkbook.Sheets(1).Cells(MailIdx + 2, 2) = "" Else ThisWorkbook.Sheets(1).Cells(MailIdx + 2, 2) = addtype End If Process_Next_Contact: Next MsgBox "Process Completed" Exit Sub Fatal_Error: MsgBox "Fatal Error: Check Outlook is running & any Email is selected to Process" End Sub
You can see in First sheet of Excel, the Email Subject and Email Ids of contacts in “To” are fetched. In similar way, you can modify this code to fetch the mail ids of contacts in ‘CC’ or ‘BCC’.
To know more about the Email properties, study more on Outlook, Recipients, MailItem properties from MSDN library. Here you will be able to get additional information and syntax for the commands used above.
More Tips: Add and Read Outlook Calendar Appointments