Import Mail IDs from Outlook Email to Excel

Fetch Email Addresses – Quick Install & Test Code

Import Mail IDs from Outlook Email to Excel

  • 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.

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.

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.

 


””””””””””””””””””””””””””””””””””””””

‘          Visit our website for more Tips and Tricks                      ‘
‘                —- www.QuadExcel.com —-                                ‘
‘                                                                          ‘
””””””””””””””””””””””””””””””””””””””
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

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.

Courtesy: Office Tricks


One Response

Show all responses
  1. Jayesh Patel
    August 26, 2014

Leave a Reply