Export Outlook From, To, Subject, Receive Date and Other Meta Data Into Excel

Category: MS OutlookTags: , ,

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 Macro Enabled Excel Spreadsheet

If you see the security warning, click on “Enable Macro” button for this spreadsheet to work

Excel macro security warning dialog

You will see the spreadsheet with two tabs. The first tab contains a button. Second tab contains the result.

Excel file with two tabs

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

Excel customize ribbon add developer tab

Now go to Developer tab > Visual Basic

Excel developer tab visual basic button

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

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.

Share

93 comments

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

  1. Marzio says:

    Hello Vishal,
    I thank you so much for this extremely useful tool and very easy to be used.
    You saved me a lot of time for listing emails and emails…

    very thank you from Italy!
    Wish you to have a pretty nice times in your whole life.Ciao Marzio

  2. mounir says:

    hi Vishal Monpara
    is there any video to understand more this ‘Export Outlook’ please

    1. Hello Mounir,

      At this moment, I don’t have any video. You will need to rely on screen shot and steps given on this blog post.

      Regards,
      Vishal Monpara

  3. Dinoop P says:

    Hi Vishal,
    Thank you so much for the macro, it helped me very well.
    Is there any way to generate the report with the following details,
    SrlNo:Sender’s email address :Subject :Received on:Replied on:Follow up completed on: Response time(Minutes):Mail Handler:Folder
    Thanks

    1. Hello Dinoop,

      You need to make a change in the programming based on your need.

      Regards,
      Vishal Monpara

      1. Jayashree Panchal says:

        Hi , how to give specific folder name . It is giving me error

        1. Hello Jayshree,

          You may want to explore programming to check if it is possible or not.

          Regards,
          Vishal Monpara

  4. Roshan says:

    How to rectify the Run time error ‘430’ Class does not support automation or does not support expected interface

    1. Hello Roshan,

      This is a generic error so it is not possible to find the root cause without getting more details.

      Regards,
      Vishal Monpara

  5. Neil Ace Ponce says:

    Hi Vishal,
    Can i asked for a copy of the macro file please?
    I do want to extract email details including the last date modified for my project. Hopefully, this one could help.

    1. Hi Neil,

      Macro is available within Excel file.

      Regards,
      Vishal Monpara

  6. Alex Quezada says:

    Hi,
    I am trying to use this and I get this erroe message in one of the inboxes
    Run Time error 430
    and when whowing debug it points at
    tempString (i + startRow, 1) = .SenderName

    I runs good in other inboxes, but not on this one,
    thanks

    1. Hello Alex,

      It is difficult to find the root cause but seems like that particular record may not have SenderName. In Debug mode, you can drag yellow marker to skip that record and then you can continue with the rest of the records.

      Regards,
      Vishal Monpara

  7. Sarthak Jha says:

    can it also be used with shared folders? I have a common mailbox i need to keep track of emails of that.

    1. Hello Sarthak,

      I have not tried on shared mailbox folder. Based on the code, I assume that it should work without any issue.

      Regards,
      Vishal Monpara

  8. Moza says:

    this way is not an autamatic way correct? thats mean, do I have to repeat this steps after I got each new email? or for any new email I will got it will automatically updated in the excell sheet?

    1. Hello Moza,

      You need to manually run this everytime you want to get an email in Excel sheet.

      Regards,
      Vishal Monpara

      1. Arul says:

        Really Fantastic! How do I run this automatically every day at a scheduled time? that would really help a lot.

        1. Hello Arul,

          You need to manually run it.

          Regards,
          Vishal Monpara

  9. Sagar K says:

    Hi, Vishal it works great. Is it is possible to download the data via date range?

    1. Hello Sagar,

      You may change code as per your need.

      Regards,
      Vishal Monpara

  10. Wigath says:

    Hi,

    Can you tell me how to have the script pull emails with a keyword in the subject line? I want to run this daily, and the subject may contain a date such as 12232019 and I only want to import those emails.

    1. Hello Wigath,

      You need to add “if” condition within code to filter out only needed information. If you don’t have programming background, the easiest way is to extract everything and then use Excel Filter get only needed email.

      Regards
      Vishal Monpara