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

Leave a Reply