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

    Thank you so much this code saved me 2 weeks (or more) of work!!!

    Really grateful for this advice.

    Any tips/ideas how i can

    export Emails from Outlook as .msg in automatically generated folders (for each day one folder subsequent to folders per month subsequent to folders per year)?

    Greetings from Germany

  2. chandru

    how to get the email attachements date wise …

  3. Vishal Monpara Post author

    Hello Pamela,

    If you know VBA coding, you should be able to make a change to filter for a given email address. If you don’t know VBA coding, export all emails and filter the needed emails using Excel filters.

    Vishal Monpara

  4. Pamela Caraway

    Is it possible to filter results by the sender’s email address or name?

  5. Vishal Monpara Post author

    Hello John,

    Unfortunately I don’t know if this script would work (with little modification) or not when you have email files available on a folder.

    Vishal Monpara

  6. John


    thanks so much for this!

    Would it be possible to alter this script to pull the metadata from a folder somewhere on one’s hard drive, as opposed to an Outlook folder?



  7. Vishal Monpara Post author

    Hello Atindra,

    This error is generic and does not provide exact information of what went wrong. It is not possible to determine the root cause and fix it without having access to the same environment that you are using.

    Vishal Monpara

  8. Atindra Kumar

    Dear Sir
    I am new in VBA. I have tried the template to save all the emails to excel 2007 but it does not work properly and not saving all the emails and throwing error. Though some emails are being saved but not all.

    Run-Time error 1004
    Application defined or object defined error
    Please help any one. I will be grateful to you.

Leave a Reply

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