Knowledge.ToString()

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

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

Comments

131 responses to “Export Outlook From, To, Subject, Receive Date and Other Meta Data Into Excel”

  1. Sedat Avatar
    Sedat

    Hello,

    VBA works. but there is something i want to ask, when capturing CC data, only the names are taken. is it possible to have the full e-mail address taken?
    example: Name Surname not just like this, Name Surname””

    Best regards,
    Sedat

  2. Miguel Avatar
    Miguel

    Hi Vishal,
    This is a great job, many thanks!
    If i want to add more information, is it possible to do it? For instance “Flag completed date”
    Best regards,
    Miguel

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Miguel,

      You may add any additional information available for MailItem object. Documentation is available at https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem

      Regards,
      Vishal Monpara

      1. JB Avatar
        JB

        Hi Vishal,

        If my emails are in folders (.msg) format instead of in Outlook, is there a workaround?

        I will need to extract the email headers on many msg files in a parent folder with multiple folders.

        1. Vishal Monpara Avatar
          Vishal Monpara

          Hi JB,

          It possible to open .msg file and read email headers. It requires custom programming to open the file. But once the file is open, you may utilize the rest of the code to export it into Excel.

          Regards
          Vishal Monpara

  3. Dinesh Avatar
    Dinesh

    Dear Vishal, fantastic job, I was able to get the data extracted from outlook. Need to check if there is any option to extract the outlook calendar events as well? if yes please help me.

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Dinesh,

      Outlook provides export functionality. Go to File > Open & Export > Import/Export > Export to a file > Comma Separated Values. Then choose the “Calendar” and export all the events.

      Regards,
      Vishal Monpara

  4. Lee Avatar
    Lee

    How do you reduce the amount of emails that are copied by a keyword in the subject for example I have a folder that has few thousand emails and I only want to copy over emails that has the word claim in the subject. Thanks

  5. Lee Avatar
    Lee

    First off thank you so much for this FREE file.
    I have a mailbox that has just under 2000 emails and I want to limit this to a specific keyword, I tried the below but doesn’t work and think I am adding to the wrong part.

    Set mailFolderItems = strFolderName.Items
    If instr(strFolderName.Items.Subject, “claim”) then

    If headerRow then

    I added end if to the last line but this is not working can you suggest where I am going wrong? My expected output would be to only pull emails with the subject containing Claim

  6. Reetesh Nair Avatar
    Reetesh Nair

    Hello Vishal,

    First of all thanks a lot for this VBA code.

    However, there is one issue when I’m trying to run this code. hope would be able to help.
    Whenever I run the code I get a “Runtime Error ’91’: Object Variable or With block variable not set” and it pops up at when the code tries to execute “tempString(i+StartRow,1)=.SenderName”.

    Is there a way to fix this?

    Thanks in Advance.

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Reetesh,

      It is possible that somehow sender name is not available and hence code generates error. Right above the line you mentioned, add the following line:

      On Error Resume Next

      It will ignore the error and keep moving to the next line without generating an error.

      Regards,
      Vishal Monpara

  7. Steven Avatar
    Steven

    How can i edit the code to pull from a subfolder?

    Thanks!

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Steven,

      Current code handles pulling emails from a subfolder. When you click on “Get Outlook Data” in Excel, it shows a dialog box to select folder. Here you can select any subfolder you would like and the program will extract emails from that subfolder.

      If you would like to make any changes in existing code, this article also shows how to view and modify code under “How to Get Email Body?” section.

      Regards,
      Vishal Monpara

      1. Steven Avatar
        Steven

        ok – I am trouble shooting and looking to pull all “Undeliverable” emails..

        I believe the typename needs to be changed to ReportItem as opposed to mailItem, how else do i need to alter the code?

        1. Vishal Monpara Avatar
          Vishal Monpara

          Hi Steven,

          The current code pulls only MailItem. There is a condition to ignore everything else which is not a MailItem. You may change the code to only pull ReportItem and use available object properties to populate spreadsheet.

          Regards,
          Vishal Monpara

  8. Sameer Avatar
    Sameer

    Dear Vishal,

    Thanks for the code.

    I was getting mails sent by individuals but coming from a single domain. So i was unable to extract the user’s actual email address from outlook.

    Thanks to your code, I was able to modify it a bit and get the right user email field.

    Appreciate it !!!

  9. Annie Love Bisnar Avatar
    Annie Love Bisnar

    Hi,

    I need help. I have an Agent Production that is getting information from the tool of the Associates. They have this task that uses the email. I want to get the specific information that is required on the Tracker on the selected email. What is the code that I need to modify.

    Hope you can assist.

    Thanks,

    Love

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Annie,

      If you can export everything in Excel, then you can filter data as per your need.

      Regards,
      Vishal Monpara

  10. Akshay Avatar
    Akshay

    Thanks Vishal,
    its is working fine and it is helping me a lot.

  11. Marc Avatar
    Marc

    Can’t get the date range to work once the export is complete. Would be great if you could provide some guidance on how to fix this or how to modify so that the spreadsheet can be sorted by sent or received date/time.

  12. Darcy Avatar
    Darcy

    Hi,
    I’m no VBA expert and couldn’t get a date range working but I can specify the number of emails to return

    change ‘numRows = mailFolderItems.Count

    to numRows = 500

    for the top 500 rows.

  13. Bogdan Stoian Avatar
    Bogdan Stoian

    Thanks for this program. But I would like to make some more changes and I have no idea if it is possible and how. When I take out the emails, they take out the URL and their content. I would like if you know how to stop removing URLs, just the text. I think a code would be written to delete everything between “”, but I have no idea how.

    Sorry for my english.

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Bogdan,

      It is difficult to find the root cause without having a sample data.

      Regards,
      Vishal Monpara

  14. Dan Avatar
    Dan

    Hello.
    First of all thank you for this program. I would like to know what to do if one of the fields is not defined. For example:
    I have an error if the SenderName field is not set (error 91).
    Thanks for your help.
    Regards.

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Dan,

      You may change code as per your data and need.

      Regards
      Vishal Monpara

  15. Harish S Avatar
    Harish S

    Hi Vishal,
    This really helping me a lot.

    I would like to check with you, if there was a date range option it would be more helpful for users to extract the data from specific date range.

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Harish,

      You may change VBA code as per your need.

      Regards,
      Vishal Monpara

      1. Darcy Avatar
        Darcy

        Thanks for this. Is it possible to show us the code that will return a date range please.

  16. Sean Smith Avatar
    Sean Smith

    Hi Vishal, thank you for all of your efforts to create this. Do you know if there is a way to extract the metadata for a calendar invite to extract organizer, subject, location, start day/time and end day/time?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Sean,

      I have looked at Microsoft documentation and it is possible to get the calendar information.

      Regards
      Vishal Monpara

      1. Mark Avatar
        Mark

        Can you please elaborate on how we could extract the email addresses of organizer, to, optional from a calendar invite? I need the actual email addresses and not just the names of the individuals.

  17. Tejaswini Waghchaure Avatar
    Tejaswini Waghchaure

    Hi.. I want to extract group members name to whom a mail is sent. E.g group_name is Sprint@xyz.com and this group has 3 member. Then i want that 3 members name in excel.
    Can you please guide me regarding this..?

  18. saravanan Avatar
    saravanan

    Hi
    I wanted to run a macro. My requirements is

    I want to track the emails replies from specific person for my emails in sent items. How to achieve this

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Saravanan,

      You will need to modify condition in macro based on your need.

      Thank you
      Vishal Monpara

  19. Nitin Gupta Avatar
    Nitin Gupta

    So much help with just 3 clicks , thanks a lot for this solution

  20. Juan Camilo Tovar Rojas Avatar
    Juan Camilo Tovar Rojas

    Gracias, funciona perfecto, gracias

  21. kamalendu Pradhan Avatar
    kamalendu Pradhan

    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. Vishal Monpara Avatar
      Vishal Monpara

      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 Avatar
        kamalendu

        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

  22. Marzio Avatar
    Marzio

    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

  23. mounir Avatar
    mounir

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

  24. Dinoop P Avatar
    Dinoop P

    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. Vishal Monpara Avatar
      Vishal Monpara

      Hello Dinoop,

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

      Regards,
      Vishal Monpara

      1. Jayashree Panchal Avatar
        Jayashree Panchal

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

        1. Vishal Monpara Avatar
          Vishal Monpara

          Hello Jayshree,

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

          Regards,
          Vishal Monpara

  25. Roshan Avatar
    Roshan

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Roshan,

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

      Regards,
      Vishal Monpara

  26. Neil Ace Ponce Avatar
    Neil Ace Ponce

    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. Vishal Monpara Avatar
      Vishal Monpara

      Hi Neil,

      Macro is available within Excel file.

      Regards,
      Vishal Monpara

  27. Alex Quezada Avatar
    Alex Quezada

    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. Vishal Monpara Avatar
      Vishal Monpara

      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

  28. Sarthak Jha Avatar
    Sarthak Jha

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

  29. Moza Avatar
    Moza

    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. Vishal Monpara Avatar
      Vishal Monpara

      Hello Moza,

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

      Regards,
      Vishal Monpara

      1. Arul Avatar
        Arul

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

        1. Vishal Monpara Avatar
          Vishal Monpara

          Hello Arul,

          You need to manually run it.

          Regards,
          Vishal Monpara

  30. Sagar K Avatar
    Sagar K

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Sagar,

      You may change code as per your need.

      Regards,
      Vishal Monpara

  31. Wigath Avatar
    Wigath

    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. Vishal Monpara Avatar
      Vishal Monpara

      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

      1. Lee Garrett Avatar
        Lee Garrett

        After what part of code would you add the if statement as this is what im struggling with?

        1. Vishal Monpara Avatar
          Vishal Monpara

          Hello Lee,

          Instead of making a change in code, I would suggest a different way. Export all the emails in Excel. Then use Excel filter to get only those emails that you are interested in. In your situation, use Excel filter on “Subject” column where “Text Filters” > “Contains” (condition) > “claim” (value).

          Regards,
          Vishal Monpara

  32. Sampurna Avatar
    Sampurna

    It really make sense for me.

  33. Dharmendran Avatar
    Dharmendran

    Hi Vishal Monpara

    Thanks for sharing the great Idea!

    Could you help me to add one column for Start date ?

    Regards,

    Dharma

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Dharmendran,

      This post also contains details on how to make a change in this spreadsheet. Follow the steps to make change as per your need.

      Regards
      Vishal Monpara

  34. Himansu Amin Avatar
    Himansu Amin

    Thanks Vishal. This was great!
    -Himansu

  35. Karthik Avatar
    Karthik

    Hello Vishal ,

    Can i export the particular data from the email body using VBA into excel…???
    is there any specific Statements be used to export the specific data from email body onto the excel sheet ….?????

    please HELP!

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Karthik,

      Once you get everything exported, you may use Excel filters to get what you need. If your situation requires complex scenario that cannot be handled using Excel, you need to parse body text within VBA code

      Regards,
      Vishal Monpara

  36. yuvapriya Avatar
    yuvapriya

    hi Vishal, How do I know if the email has been responded? is there any code to add the Replied date as well?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Yuvapriya,

      There is no out of the box property but quick search revealed that there are extended properties available. It requires a additional programming.

      Regards,
      Vishal Monpara

  37. Ivan Avatar
    Ivan

    Hey Vishal,
    Is there a way to have the e-mails be organized in chronological order by the send date/time?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Ivan,

      Once you get data in Excel, you can sort it by SentOn column. I don’t know any way to get data from Outlook in a chronological order.

      Regards,
      Vishal Monpara

  38. Ela Avatar
    Ela

    Hi Vishal, do you know if it’s possible to modify the script to get the data about e-mail signature only, instead of the whole body – like who signed a sent e-mail?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Ela,

      I don’t know any way to extract/parse the text from Email body to get sender’s signature.

      Regards,
      Vishal Monpara

  39. Joey Avatar
    Joey

    I can’t seem to get this to work on a mac. Should it?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Joey,

      This solution works only in Windows.

      Regards
      Vishal Monpara

      1. Beth Hudgens Avatar
        Beth Hudgens

        Hello Vishal!
        I believe your solution would work well for me, except I am on a Mac.

        I’m not well-versed in VBA and was wondering if you could identify the parts of the macro that might need to be modified to get this to work on Mac. I also checked the link in your macro that you cited as the source for the code, but the target page says the question has been removed from their site. If you have another lead about a resource for helping with this, please advise. Thanks for your time!
        Beth

        1. Vishal Monpara Avatar
          Vishal Monpara

          Hi Beth,

          In technical terms, this VBA macro is creating a COM object. COM object is a concept available only in Windows but not in Mac and hence this VBA macro will not work in Mac.

          The only way I can think of to get Outlook data is to create a VBA macro that can run directly within Outlook (and not in Excel) and save data as CSV (and not Excel because there is no COM support in Mac).

          Regards,
          Vishal Monpara

  40. yuva Avatar
    yuva

    Sir thank you for the script
    how to change the date range pls help

  41. Cory Avatar
    Cory

    Disregard my question – this works perfectly!

  42. Cory Avatar
    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?

  43. sri Avatar
    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.

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

  44. Saman Avatar
    Saman

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

  45. Asad Avatar
    Asad

    How to get Recipient Email Address?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Asad,

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

      Regards,
      Vishal Monpara

  46. Jan Avatar
    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?

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Jan,

      “msg” in this code is of object MailItem. You may use “SaveAs” method for this object to save the item on your disk as per your need. Documentation for this method is available at https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.saveas

      Regards
      Vishal Monpara

  48. chandru Avatar
    chandru

    how to get the email attachements date wise …

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Chandru,

      You will need to make the change in the code as per your need. You will be able to save the attachment using https://docs.microsoft.com/en-us/office/vba/api/outlook.attachment.saveasfile function.

      Regards,
      Vishal Monpara

  49. Pamela Caraway Avatar
    Pamela Caraway

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      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.

      Regards
      Vishal Monpara

  50. John Avatar
    John

    Vishal,

    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?

    Example:

    e:\projects\emailtobesorted

    1. Vishal Monpara Avatar
      Vishal Monpara

      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.

      Regards
      Vishal Monpara

  51. Atindra Kumar Avatar
    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.

    1. Vishal Monpara Avatar
      Vishal Monpara

      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.

      Regards,
      Vishal Monpara

  52. AnonUser Avatar
    AnonUser

    Thank you Vishal, it works beautifully 🙂

  53. Peter Avatar
    Peter

    Hi Vishal,

    Nice tool, is there a way the tool includes directly all subfolders as well?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Peter,

      You may use objNamespace.Folders property to loop through all available folders. You may find the documentation at https://docs.microsoft.com/en-us/office/vba/api/outlook.namespace.folders

      Regards,
      Vishal Monpara

  54. ES Avatar
    ES

    What code do I put to get a specific line in the body?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello ES,

      The code already exists in Excel but it is commented so it does not have any effect. Follow the steps given under “How to get Email body?” within this same blog post to get the body text from outlook.

      Thank you
      Vishal Monpara

  55. Mohammad Nurihan Bin Salikin Avatar
    Mohammad Nurihan Bin Salikin

    Hi Vishal Monpara,

    Thanks, it works!

  56. Jose Luis Romero rayan Avatar
    Jose Luis Romero rayan

    I have downloaded your code and works fine, however Is there any way to substitue : Set strFolderName = objNamespace.PickFolder, by some sentences?
    The point is that I need to check the names of the attachments files from a vba macro, without picking the folder.
    I have been exploring the web and I am not able to find out a solution.
    Thank uoy very much in advance.
    Regards

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Jose,

      You can always hardcode the value of “strFolderName” in that line so it will not show the window to pick the folder name.

      Regards,
      Vishal Monpara

  57. Michael Avatar
    Michael

    Hi,
    How would i be able to adapt the code so that new emails will just add to the excel file not replace it?

    Thanks

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Michael,

      You may use the following logic to make it work.

      1. You need to get the email only for a certain date range. Date range must not overlap when you run the program on multiple days. This ensures that you are getting only those emails which has never been fetched previously.
      2. Before you start fetching the email, make the start position as the last cell of Excel content

      Regards
      Vishal Monpara

  58. Von Rosales Avatar
    Von Rosales

    Hi Vishal,

    You’re a genius! This works really great and I think my department will really benefit from this! I wanted to check with you on how would I be able to only import the emails from the folder I selected from a date that I specify? i wanted to place a Date Field beside the Get Outlook Data button. Is this possible?

    Looking forward to your reply, thank you and my dearest regards,

    Von Rosales

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Von,

      Yes it is possible. If you or your team can code in VBA, you can create a separate form to get date range and use that date range to filter the messages.

      Regards,
      Vishal Monpara

  59. Janarthanan Avatar
    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

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

  60. Luke Avatar
    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

  61. Vishal Monpara Avatar
    Vishal Monpara

    Hi Alex,

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

    Regards,
    Vishal Monpara

  62. Alex Avatar
    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

  63. Jayakumar Krishnamoorthy Avatar
    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

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Jayakumar,

      Whatever you mentioned is possible if you know VBA programming. Instead, I would suggest to use Outlook Rules as it is quick and easy to create/use/update as per your need. Here is a link on how to perform this task. Move Outlook emails of specific date range to a folder

      Regards,
      Vishal Monpara

  64. Deepak Sharma Avatar
    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.

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

  65. Deepak Sharma Avatar
    Deepak Sharma

    Hi Vishal,
    It was working fine a few minutes back but just now ,i don’t know what happened,
    I am getting Run Time error 91, (Object Variable or with Block Variable not Set) in
    with msg
    tempString(i + startRow, 1) = .BCC

    Please help

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Deepak,

      This is a generic error from Excel and it is difficult to find out the root cause without looking at the current state of the file. You may want to start from the scratch to see if it fixes the issue.

      Thank you
      Vishal Monpara

  66. quim Avatar
    quim

    Hi Vishal,
    Thank you so much for this information. I would like to know if it is possible to have the same items list but for more than one subfolder.

    My folders structure have 3 levels, and going 1 by one is a waste of time, if you could help me, I would be appreciated.
    Best regards,
    Quim

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Quim,
      You need to modify the code and hard code the values of folders. Use “MsgBox objNamespace.PickFolder, vbOKOnly” to show you the folder name and use that name for hardcoding.

      Regards,
      Vishal Monpara

  67. Vishal Shetty Avatar
    Vishal Shetty

    Hi Vishal,
    Great Code . Thanks for the info.
    I had a question for you, and a check if it is feasible or not – can we get the outlook property from Outlook to Excel using VBA. Specifically, I need the “City” of the sender which I can see on the Lync under ” Outlook Properties” to be exported to excel as one of the column Header.
    Thanks again.

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Vishal,

      I haven’t confirmed but I assume that the Lync information comes from Active Directory. Once you have From address, you may query the Active Directory to find the information. Alternatively, you may also search for that contact within Outlook Contact (locally stored within Outlook and not Active Directory) and find the needed information.

      Regards,
      Vishal Monpara

  68. Vishal Monpara Avatar
    Vishal Monpara

    Hi Richard,
    I have updated the post to reflect the steps to uncomment the source code and extract email body.
    Thank you

  69. Richard Latimer Avatar
    Richard Latimer

    Hi Vishal,
    Gosh, you are talented.. Thank you for the code. it works just fine for my needs however, you mentioned that you commented out the code for the body information. I actually need the body information. is there a way to have that come into the spreadsheet as well.

    Thank you again, Vishal.
    Rich Latimer – Investments Unit American Red Cross

  70. Vishal Monpara Avatar
    Vishal Monpara

    Hi Amit,
    You may use msg.SentOn property to filter the data based on the date range.

  71. Amit Avatar
    Amit

    Hi Vishal – Thanks for this mailbox data extraction macro. I hve around 10000+emails in my inbox, is there a possiblity to put up a date range on this macro so that I can extract data for a date range as per my need?

    Thanks in advance for your help!

    Amit

Leave a Reply

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