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

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

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

  1. Cory

    Disregard my question – this works perfectly!


  2. Cory

    This is AMAZING – I’ve searched for a very long time trying to find something along these lines and have had a ton of personal failures. Thank you!

    One thing I’ve noticed is that the most recent message pulled from any folder is about a week old. I’m unsure of how to manipulate the code (aside from sorting the data pulled after the fact) to prioritize the most recent emails. Any tips?


  3. Vishal Monpara Post author

    Hello Sri,

    You may make change in the macro to get emails for the chosen date range. This way, you will get emails from a specific date loaded in Excel. You may also add additional condition for Subject based on your need within macro.

    Regards,
    Vishal Monpara


  4. sri

    Thanks a ton, this is exactly what I was looking for.
    Can you please help me to filter on Subject=”AAA”.
    And the date range from & to

    What I am looking is to import only those mails with the subject = AAA, which the date range from 1st June to 30th June 2019, .

    Now my folder has loads of mail, so its taking a lot of time for the macro to fetch the data.


  5. Saman

    Hi Mr. Monpara
    Thank you so much for this topic
    that was so helpful for me
    you are a great programmer


  6. Vishal Monpara Post author

    Hello Asad,

    “To” represents recipient and “From” represents sender.

    Regards,
    Vishal Monpara



  7. Vishal Monpara Post author

    Hi Jan,

    There is an issue with the code. Once I get time, I will fix it and test it. Main culprit is “If IsMail(folderItem) Then”. It requires Else condition.

    Regards,
    Vishal Monpara



  8. Jan

    Ok…
    Great stuff, problem:

    When I use the macro on my Inbox it works just fine, but when I use it on any other folder (subsequent or next to my Inbox) I get an error.

    “Run-time error ’91’:
    Object variable or With block variable not set

    So I am a VBA newbie, but I tried to find the error by running through the code step-by-step.

    To me it looks like, that, if I select a different folder than my Inbox, the “folderItem” will not receive any value in the row:

    Set folderItem = mailFolderItems.Item(i)

    leading to the “msg” not receiving a value (ergo it is “nothing”) which then leads to the error poppong up as soon as I pass the

    With msg

    line.

    Any idea what causes this?


Leave a Reply

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