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

By | January 18, 2017

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.

 

Vishal Monpara is a full stack Solution Developer/Architect with 12 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.

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

  1. Deepak Sharma

    Thanks for your reply Vishal.

    Actually, whenever there are more than 100 emails it is giving this error but for lesser number it is working fine….
    Sometimes it’s working fine on numerous emails as well but sometimes it is giving error on even 1 mail.

    Could you please advise why is it so.

  2. Jayakumar Krishnamoorthy

    Hi Vishal,

    I want to filter the emails of specific data range received between two dates and time and move it to outlook folder from specific email address. is that possible to do it on the above code.

    Thanks
    Jay

  3. Vishal Monpara Post author

    Hello Deepak,

    I had a success getting thousands of emails in a single run so unless I can replicate the issue on my end, it is difficult to find the root cause.

    Regards,
    Vishal Monpara

  4. Alex

    Hi Vishal,

    Thank you for sharing your code. This will save me a great deal of time for my use case.

    I do have one question regarding the format in which the date is returned. Do you know of a way to convert the format to an acceptable format for excel to read within the macro? Currently the only solution is to convert it manually via a formula of = (cell with date information) * 1.

    Regards,
    Alex

  5. Vishal Monpara Post author

    Hi Alex,

    The code converts the date into string. The other alternative is to convert it within the VBA code.

    Regards,
    Vishal Monpara

  6. Luke

    Hi Vishal

    I usually just skim through websites like these, but this excel file and macro was literally the most perfect piece of code for my situation, and it is very much appreciated.

    Keep up the good work!

    Kind regards

    Luke

  7. Janarthanan

    Hi Vishal,

    Thanks for sharing this code. I need a clarification from you, can we extract complete body of the email? FYI. the current code is extracting up to 32,767 characters.

    Requesting your help on this.

    Regards,
    M Janarthanan

  8. Vishal Monpara Post author

    Hello Janarthanan,

    I have not explored the option to extract the full body. You may want to explore the VBA code documentation for full body extract.

    Regards,
    Vishal Monpara

Leave a Reply

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