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

By | January 18, 2017 | 64 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.

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

  1. Karthik

    Hello Vishal ,

    Can i export the particular data from the email body using VBA into excel…???
    is there any specific Statements be used to export the specific data from email body onto the excel sheet ….?????

    please HELP!

  2. Vishal Monpara Post author

    Hello Yuvapriya,

    There is no out of the box property but quick search revealed that there are extended properties available. It requires a additional programming.

    Vishal Monpara

  3. yuvapriya

    hi Vishal, How do I know if the email has been responded? is there any code to add the Replied date as well?

  4. Vishal Monpara Post author

    Hi Ivan,

    Once you get data in Excel, you can sort it by SentOn column. I don’t know any way to get data from Outlook in a chronological order.

    Vishal Monpara

  5. Ivan

    Hey Vishal,
    Is there a way to have the e-mails be organized in chronological order by the send date/time?

  6. Vishal Monpara Post author

    Hi Ela,

    I don’t know any way to extract/parse the text from Email body to get sender’s signature.

    Vishal Monpara

  7. Ela

    Hi Vishal, do you know if it’s possible to modify the script to get the data about e-mail signature only, instead of the whole body – like who signed a sent e-mail?

  8. Joey

    I can’t seem to get this to work on a mac. Should it?

  9. yuva

    Sir thank you for the script
    how to change the date range pls help

Leave a Reply

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