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

92 comments

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

  1. Juan Camilo Tovar Rojas says:

    Gracias, funciona perfecto, gracias

  2. kamalendu Pradhan says:

    Hi Vishal,

    Thank you so much for this macro.I have a small question in this.In the same macro If i would like to retrieve one specific date data from that folder how can I do that.

    Could you please help me on this.

    1. Hello Kamalendu,

      Quickest way is to export to Excel and then filter out data for a specific date range. Second option is to modify macro to add a condition for specific date.

      Regards,
      Vishal Monpara

      1. kamalendu says:

        Hi Vishal,

        Thank you so much for your reply. I have the below requirement

        I have data for 17th June,22nd june and 23rd june in outlook folder. When I run this macro for 23rd june it works fine.But when I run for 17th June date it is giving me data from 17th,22nd,and 23rd.same when I run 22nd june it is giving me 22nd and 23rd data.But My requirement is whenever I enter any specific date I should get only that particular date data.Not other other dates.

        I created the macro already for this But it does not export any data when I enter the date. could you pls help me on this this.

        Option Explicit

        Sub getDataFromOutlookChoiceFolder()
        Dim OutlookApp As Outlook.Application
        Dim OutlookNamespace As Namespace
        Dim Folder As MAPIFolder
        Dim OutlookMail As Variant
        Dim i As Long

        Set OutlookApp = New Outlook.Application
        Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
        Set Folder = OutlookNamespace.pickfolder

        If Folder.Items.Count = 0 Then
        MsgBox "No emails. Exiting procedure!"
        Exit Sub
        End If

        i = 1

        Dim rngName As Name
        Sheet1.Cells.Clear
        For Each rngName In ActiveWorkbook.Names
        rngName.Delete
        Next

        Range("A1").Name = "email_Subject"
        Range("A1") = "EmailSubject"
        Range("B1").Name = "email_Date"
        Range("B1") = "Email Date"
        Range("C1").Name = "email_Sender"
        Range("C1") = "Email Sender"
        Range("D1").Name = "email_Body"
        Range("D1") = "Email Body"
        Range("E1").Name = "email_Receipt_Date"
        Range("email_Receipt_Date").Value = InputBox("Enter Receipt Date like 20-mar-2020")

        For Each OutlookMail In Folder.Items
        If OutlookMail.ReceivedTime = Range("email_Receipt_Date").Value Then
        Range("email_Subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("email_Subject").Offset(i, 0).Columns.AutoFit
        Range("email_Subject").Offset(i, 0).VerticalAlignment = xlTop
        Range("email_Date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("email_Date").Offset(i, 0).Columns.AutoFit
        Range("email_Date").Offset(i, 0).VerticalAlignment = xlTop
        Range("email_Sender").Offset(i, 0).Value = OutlookMail.SenderName
        Range("email_Sender").Offset(i, 0).Columns.AutoFit
        Range("email_Sender").Offset(i, 0).VerticalAlignment = xlTop
        Range("email_Body").Offset(i, 0).Value = OutlookMail.Body
        Range("email_Body").Offset(i, 0).Columns.AutoFit
        Range("email_Body").Offset(i, 0).VerticalAlignment = xlTop

        i = i + 1
        End If
        Next OutlookMail

        Set Folder = Nothing
        Set OutlookNamespace = Nothing
        Set OutlookApp = Nothing

        End Sub