Export Outlook From, To, Subject, Receive Date and other meta data into Excel
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
- Download the Macro enabled Excel spreadsheet
- If you see the security warning, click on “Enable Macro” button for this spreadsheet to work
- You will see the spreadsheet with two tabs. The first tab contains a button. Second tab contains the result.
- Click on the “Get Outlook Data” button
- It will prompt the user to select the Outlook folder/subfolder from which you want to export the data.
- Select the appropriate folder and click “OK”.
- 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.
- If the Developer tab is not available in Excel, go to File > Options > Customize Ribbon > Developer > Check it and click OK button
- Now go to Developer tab > Visual Basic
- 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
- Remove the single quote character that is in the beginning of the line.
- Close this main window
- Save the Excel spreadsheet
- Close the spreadsheet
- 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.
64 thoughts on “Export Outlook From, To, Subject, Receive Date and other meta data into Excel”
Hello Dharmendran,
This post also contains details on how to make a change in this spreadsheet. Follow the steps to make change as per your need.
Regards
Vishal Monpara
Hi Vishal Monpara
Thanks for sharing the great Idea!
Could you help me to add one column for Start date ?
Regards,
Dharma
Thanks Vishal. This was great!
-Himansu
Hello Karthik,
Once you get everything exported, you may use Excel filters to get what you need. If your situation requires complex scenario that cannot be handled using Excel, you need to parse body text within VBA code
Regards,
Vishal Monpara