Export Outlook From, To, Subject, Receive Date and other meta data into Excel

By | January 18, 2017 | 61 Comments
Category: MS Excel Tags: , ,

I had a need to export all the emails’ meta data like From, To, Subject, Receive Date from Outlook. Quickly searching on the internet, I found the script at http://superuser.com/questions/816289/exporting-attachment-file-name-email-metadata-from-outlook-to-excel so I quickly created a spreadsheet and exported all the needed information.

I have made following modification to the code

  • Commented out exporting email body data as I don’t need it (if you want email body, follow the steps given at the bottom)
  • Error thrown when setting up the auto filter

Other than that code worked fine for my need.

Here are the steps

  1. Download the Macro enabled Excel spreadsheet
  2. If you see the security warning, click on “Enable Macro” button for this spreadsheet to work
  3. You will see the spreadsheet with two tabs. The first tab contains a button. Second tab contains the result.
  4. Click on the “Get Outlook Data” button
  5. It will prompt the user to select the Outlook folder/subfolder from which you want to export the data.
  6. Select the appropriate folder and click “OK”.
  7. Once completed, it will show the message “Completed” and you will get all the data that you need in the “Outlook Results” tab

 

How to get Email body?

Based on Richard’s comment, I thought it is a good idea to show how to get Email body. Here are the steps.

  1. If the Developer tab is not available in Excel, go to File > Options > Customize Ribbon > Developer > Check it and click OK button
    Excel customize ribbon add developer tab
  2. Now go to Developer tab > Visual Basic
    Excel developer tab visual basic button
  3. On left hand side, double click on “Module1” and on the right hand side, search for the code which is highlighted in the image below
    Uncomment vba code
  4. Remove the single quote character that is in the beginning of the line.
  5. Close this main window
  6. Save the Excel spreadsheet
  7. Close the spreadsheet
  8. Open the spreadsheet again and now you can extract first 900 characters of Email body. If you want more, change this limit within the code in the same line.

 


About Vishal Monpara

Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1's and 0's from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.

61 thoughts on “Export Outlook From, To, Subject, Receive Date and other meta data into Excel

  1. Vishal Monpara Post author

    Hello Deepak,

    This is a generic error from Excel and it is difficult to find out the root cause without looking at the current state of the file. You may want to start from the scratch to see if it fixes the issue.

    Thank you
    Vishal Monpara


  2. Deepak Sharma

    Hi Vishal,
    It was working fine a few minutes back but just now ,i don’t know what happened,
    I am getting Run Time error 91, (Object Variable or with Block Variable not Set) in
    with msg
    tempString(i + startRow, 1) = .BCC

    Please help


  3. Vishal Monpara Post author

    Hi Quim,
    You need to modify the code and hard code the values of folders. Use “MsgBox objNamespace.PickFolder, vbOKOnly” to show you the folder name and use that name for hardcoding.

    Regards,
    Vishal Monpara


  4. quim

    Hi Vishal,
    Thank you so much for this information. I would like to know if it is possible to have the same items list but for more than one subfolder.

    My folders structure have 3 levels, and going 1 by one is a waste of time, if you could help me, I would be appreciated.
    Best regards,
    Quim


  5. Vishal Monpara Post author

    Hi Vishal,

    I haven’t confirmed but I assume that the Lync information comes from Active Directory. Once you have From address, you may query the Active Directory to find the information. Alternatively, you may also search for that contact within Outlook Contact (locally stored within Outlook and not Active Directory) and find the needed information.

    Regards,
    Vishal Monpara


  6. Vishal Shetty

    Hi Vishal,
    Great Code . Thanks for the info.
    I had a question for you, and a check if it is feasible or not – can we get the outlook property from Outlook to Excel using VBA. Specifically, I need the “City” of the sender which I can see on the Lync under ” Outlook Properties” to be exported to excel as one of the column Header.
    Thanks again.


  7. Vishal Monpara Post author

    Hi Richard,
    I have updated the post to reflect the steps to uncomment the source code and extract email body.
    Thank you


  8. Richard Latimer

    Hi Vishal,
    Gosh, you are talented.. Thank you for the code. it works just fine for my needs however, you mentioned that you commented out the code for the body information. I actually need the body information. is there a way to have that come into the spreadsheet as well.

    Thank you again, Vishal.
    Rich Latimer – Investments Unit American Red Cross



  9. Amit

    Hi Vishal – Thanks for this mailbox data extraction macro. I hve around 10000+emails in my inbox, is there a possiblity to put up a date range on this macro so that I can extract data for a date range as per my need?

    Thanks in advance for your help!

    Amit


Leave a Reply

Your email address will not be published. Required fields are marked *