Export Outlook Emails To Excel – VBA Code – 5,700+ Free Downloads

Export Emails From Outlook to Excel with Date and Time

To Export Outlook email data to Excel & archive mails, we have 3 options here.

  1. Menu option for Outlook to Excel
  2. Outlook VBA: Export emails from Outlook to Excel VBA with date and time.
  3. Archive Outlook Emails

Lets start to learn all possible methods, one by one.

1. How to Export Outlook Emails To Excel?

To extract data from Outlook to Excel:

  1. Click File menu or Office Ribbon in Outlook.
  2. Select “Import and Export”.
  3. Choose “Export to a file” & press Enter.
  4. Select “Microsoft Excel 97-2003” & Enter.
  5. Choose ‘Outlook Folder to Export’ & click next.
  6. Type Output Excel File path to export Email data.
  7. Map Outlook field headers with output Excel column headers [optional].
  8. Click Finish.

All the email items from the chosen PST folder will be downloaded to Excel.

It will have email fields like sender, email body, date and time etc.,

More Advanced Options are here… Download Outlook Mail Attachments to local Folder

2. Outlook VBA: Copy Email Fields to Excel with Date and Time

Here is the VBA code to extract emails from Outlook to Excel.

Download & Try this Excel before using the Code Downloaded 6,192 Times

Before executing the code, make a note of these points.

  • Go to Tools -> References & enable “Microsoft Outlook nn.n Object Library. (nn.n is version of Object Library that varies depending on your Office Installation)
  • Change “Mail box Name” & “Folder Name” from where you need to extract the email data.
  • Outlook application should be running in your machine.

Note: If you are executing this code in Outlook VBA, then above reference is not required.

Export Outlook emails to Excel with date and time

This is how we can create an alternate to traditional archive Outlook emails built in option and maintain our own readable Email repository.

To execute this code, press F5 and system will extract data from Outlook to Excel from the mentioned mailbox and folder.

Note: The limitations of this Export Outlook to Excel code are:

  • Error:
    • Double & Single quotes in below code might change to improper symbols while copying. Check it if you get any execution or compilation error.
    • If there are any meeting invites or items other than a Email, then the above code will throw error. Remove the mail items that this code cannot read and re-execute the code.
  • Warning:
    • This VBA does not extract complete MIME details of email, as how it is stored in Outlook archive folder. This method only stores the mail content in Excel just for future reference.
  • Information:
    • Attachments from emails are not skipped. It will export only text content. For more information on downloading attachment to a system folder, you can get to know by reading this post “Outlook Attachments”.

3. How to Archive email in Outlook? – On Demand

To archive Outlook email to a PST folder, follow these steps.

  1. In File Menu, click “Archive” (shortcut: Alt + f + r).
  2. Choose Outlook mail folder to be archived.
  3. Choose date for ‘Archive items older than’.
  4. Enter path for archive PST file & click Ok.

Outlook will start to move all the emails matching the date criteria to your archive pst file. Archiving old emails will improve the performance of Outlook considerably. Once your Outlook takes more time to load or search emails, then try to run a archival process on that specific email folder.

There are 2 options available to archive Outlook emails – Outlook Auto Archive & On demand.we are going to see the automatic archival settings next.

4. How to Schedule Outlook Auto Archive?

To schedule the process of archiving Outlook emails at regular time interval, follow these steps.

  1. In Tools Menu, click “Options” (shortcut: Alt + t + o).
  2. Choose ‘Others’ tab & click “AutoArchive”.
  3. Enter Outlook Emails Autoarchive settings
    1. Time Frequency.
    2. Eligibility Date criteria for cleaning up older emails.
    3. Output PST Folder path.
  4. Click Ok when all settings are entered.

By using this method, you can make the Outlook to run archival process after office hours.

Usually, your PC slows down if you have large number of older emails in your Inbox. So, try to schedule a archival after office hours.

Misc about Export Outlook Emails To Excel or Email Archiving

In this page, we learned multiple ways to read all the Emails from a specific Outlook Folder and write the details like Email Subject, Sender Name, Received Time, Body etc., to Excel.

People do lot of search for the code in this article, but still some might get confused about what is the actual need to export Outlook emails to Excel.

Transferring data from one MS Office application to another, but why?

With Excel, after exporting they can apply options like Filter, Vlookup, Pivot Chart, and Pivot Table.

More Tricks: Send Personalized Outlook Email from Excel to multiple Recipients

Anyways Microsoft has provided option through Outlook VBA to read email content and export the mail body, contacts, addresses, Calendar and Tasks to Excel or Text file. Outlook also has option for VBA, but it is fun to integrate it with Excel VBA.

Related: Read and Update Multiple Appointments to Outlook Calendar from Excel

Additional References

  • A

    I get an error message that says variables not defined for MailboxName and Pst_Folder_Name. Any idea how to fix it?

    • https://officetricks.com/ kumarapush

      Hi, I missed to declare the variables before using them. Add the below 2 lines just before using the variables to get rid of the error message.
      Dim Pst_Folder_Name
      Dim MailboxName

  • TCA

    Hi Kumarapush, is below correct? However what is the proper data type?

    Dim Pst_Folder_Name

    Dim MailboxName

    Dim Folder As Outlook.MAPIFolder

    Dim iRow As Integer

    Thanks in Advance^_^

    • https://officetricks.com/ kumarapush

      Hi TCA,
      Thanks for visiting the page.

      Yes. It is allowed to define variables without mentioning the data type. If you want to define them with a proper data type, these 2 fields are of type “String”. Hope this helps.

  • Ajinkya

    I am receiving a Error stating Compiling Error- Sub or Function not defined.

    • https://officetricks.com/ kumarapush

      Please post the Line or command at which this error is occurring, so that it is easy to resolve.

      • adnanjalooudi

        I’m getting the attached snapshot

        • adnanjalooudi

          here is the snapshot
          Set Folder = Outlook.Session.Folders(MailboxName).Folders(Pst_Folder_Name)

          • https://officetricks.com/ kumarapush

            Due to technical problems in website, the reply got delayed.
            Please open your Microsoft outlook application, and try to execute this code. Also ensure that the folder name you have assigned the variable “Pst_Folder_Name” exists under the MailboxName.

  • Guest

    I love your work here but I can’t seem to get it working I get this error message would you know why?

    • https://officetricks.com/ kumarapush

      Due to few technical difficulties with site, the reply got delayed.
      Did you follow the steps explained in step 4 in this article. This error will not occur if you are able to include the reference as explained in point 4. Please reply, if you are still not able to execute the code.

  • David Tran

    I love your work here but I can’t seem to get it working I get this error message would you know why? I have attached a snapshot.

    • https://officetricks.com Moderator

      Hi David,
      couldnt view the snapshot, please send the error snapshot to officetricks123@gmail.com to resolve the problem.

      Thank You

    • https://officetricks.com/ kumarapush

      Due to technical difficulties with he website, the reply got delayed.
      Did
      you follow the steps explained in step 4 in this article. This error
      will not occur if you are not able to include the reference to “Microsoft Outlook nn.n Object Library” as explained in
      point 4. Please reply, if you are still not able to execute the code.

      • David Tran

        I actually did follow the instructions from step 4. I had the file in the object library ticked and also Dim Folder As Outlook.MAPIFolder is where there is an error. However could I ask what “Mailbox Name” is? Should it just be Tran, David (AU)? Even when I use that there is the same error.

        • https://officetricks.com/ kumarapush

          I am not able to understand why this is not working for you David !!!
          If you don’t mind, is it possible for you to send a screen shot of your Outlook (displaying the all the folder names & mailbox names, hiding your mails 🙂 ) and also send your VBA code to my email – kumarapush123@gmail.com. This would help in finding the code issue.
          If this is not possible, then I can connect to your machine through Chrome Remote connection and resolve the issue.

          • David Tran

            Okay I tried to give as much information as possible. However even though I put inbox in the folder name I really want Local Pick Pack but that won’t work either and it is a folder in so I am just testing inbox and it still won’t output anything either. Alright I would not mind remote connection I just want to know if it can be sorted out with these snapshot.

            Thanks for the help!

  • thewiseguy31

    Hi Kumarapush. I was hoping you could help. I am getting this error. Are you able to help?

    Thanks!

    • https://officetricks.com/ kumarapush

      Hi TheWiseGuy, Did you follow all the 4 steps mentioned above the code?

      “Go to Tools -> References and enable check box at “Microsoft Outlook nn.n Object Library (nn.n is version of Object Library that varies depending on your Office Installation)”

      If you are getting error message even after adding this reference, Send/eMail me a screen shot of error message and name of your Outlook (to verify your Mailbox + PST folder name).

  • Kala

    Hi,

    I would like to extract only table from an outlook e-mail message into an excel file… Could somebody help me with sample codes to acheive this.. We have an urgent requirement in our project like this.
    Quick assistance would be greatly appreciated with gratitude.

    Regards
    Kala

    • https://officetricks.com/ kumarapush

      Instead of “Folder.Items.Item(iRow).Body” use “Folder.Items.Item(iRow).HTMLBody” and assign the HTML format of your mail to a Variable.

      Now, go to this page: https://officetricks.com/download-html-table-to-excel/
      Use the code in that article to Download HTML Table to Excel.
      Create an object “Set HTML_Content = CreateObject(“htmlfile”)” and assign the Mailitem.HTMLBody content to that article & use it to copy table content to Excel.

      Hope this helps. Please reply, if you still face any issue.

  • Nalin Samarasinghe

    Thank you very much… this has help me a lot today when I got lot of failure notifications from My DB to my e-mail.

    • https://officetricks.com/ kumarapush

      I am glad this article helped. 🙂

  • Eli

    hi. can you tell me how i would add in date parameters to this code so that I don’t export from so long ago?

    • https://officetricks.com/ kumarapush

      Use a IF condition as given below inside the FOR loop.

      IF VBA.DateValue(VBA.Now) – VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60 THEN

      This IF condition will be TRUE if mail is received within 60 days from Today's date.

      • http://www.leafxl.com Stewart Setter

        I’m showing some good results with this code, but I can’t seem to get this date parameter right, can you drop me an example of how this looks inside the FOR loop please.

        • https://officetricks.com/ kumarapush

          Updated the code with Date Parameters before Exporting to Excel..

      • Samad Bilgi

        HI Kumarapush

        Thanks for the Code its working fine. but when we Insert 60-days code. Ist giving the error Massage “Runtime error 214 732567 80020009” Please Have a look at Code.

        Thanks again.

        • Samad Bilgi

          HI Kumara I resolved the issue thanks for the code again

          • https://officetricks.com/ kumarapush

            Happy to know.
            It would help others if you could write here, how you solved the issue?

            • samad

              Hi Kumara below is the screen shot for the error. but i not getting desired result for less than 60 days mails. Can you please have look into it. I will be very Tahnkfull to you.

              • https://officetricks.com/ kumarapush

                You didnt mention what is the error message?

                Email me Teamviewer or any other remote connection passcode to connect to your computer and resolve the issue.

                Note: Dont post any passcodes here in the comments.

                • samad

                  Hi Kumara
                  the Error Code is “Run Time Error ‘438’:
                  Object Doesn’t Support this Property or method. Apologies we dont have teamveiwer Accese.

                  • https://officetricks.com/ kumarapush

                    Not sure whether you have added needed references that are mentioned in the article. Give me a Contact ID or Chat ID that I could use to discuss and resolve the issue.

  • Scotty

    Thanks so much for this. How can I add column headers? ie in the column that has the Sender’s Name have “Sender’s Name” as the top of the column?

    • https://officetricks.com/ kumarapush

      If you need to insert Columns headers, then you have to modify the for loop (For iRow = 1 To Folder.Items.Count).

      You have to first print the Column header before this loop and make this Loop to start from row 2. If you know even basics of VBA, then it is a easy process. If it is tough for you, mail me, I will give you the code.

    • https://officetricks.com/ kumarapush

      Due to Many requests from reader to include Headers, Updated the Code to Include Column Headers while Exporting mail to Excel.

  • Mohammed Nizam

    hi,

    I need to extract the all mails from Generic mail box to an Excel, which should be automated.
    so that can you please help me out to have it done.
    Thanks.

    • https://officetricks.com/ kumarapush

      That can be done by using the code in this article. Let me know if you are facing any issue with this code?

  • Isaac Jonas

    First of all, thank you for putting the work in to developing this macro. Do you know if this can be done with emails in your Sent Items? I know your comments leave it as an option, but I’ve been trying to make it work for hours now and I’m at a loss. Every time I run it, it has a runtime error 438 “Object doesn’t support this property or method” at ReceivedByDate and CC. It only seems to happen to my sent emails; I tried moving them to a separate folder and even into my Inbox (which I know the commands that are giving me trouble work on), but the macro fails when it hits emails sent from me.

    Here’s my code. If you have any idea why this could be happening, I would greatly appreciate reading your input on the situation.

    Thank you again.

    • https://officetricks.com/ kumarapush

      Hai Isaac, Thanks for the comments.

      I tried to run it in my Outlook and it just ran fine with my sent Items and the fields that you have mentioned (ReceivedByName and CC).

      Because, I am not able to recreate the issue in my system. :-). Could we connect with Teamweaver or Google Desktop app to resolve this issue?

  • Cesar Ordonez

    Very nice article, thanks a lot! I was able to run it without any problems, however in lines 40 and 41 maybe you need to change “iRow” to “oRow” in Cells property otherwise you will lost titles for columns 5 and 6 🙂

    • https://officetricks.com/ kumarapush

      Wow. That was a good catch Cesar Ordonez. I am glad that you took time to notify me. Thank you. (Modified the code as per your note).

  • Haydn

    Hi, Great piece of code, it works when I use “Inbox” as my folder, but I have a folder called “Data Requests” which sits inside “Folders”. When I change the folder name to “Data Requests” I get an error, The attempted operatio failed.An object could not be found

    • https://officetricks.com/ kumarapush

      I was expecting that someone is going to have this issue very soon and here it is. The above code cannot read PST Sub-folder. But I will post a fix soon.
      For now, you can manually move the sub folder up one level as a main folder under PST and use this code.

      • Loian0200309

        Kumarpush, Thanks so much for your code! It is almost exactly the code I was looking for. Need some tweeking so I can pull from the correct folder in Outlook. I was also wondering if you was able to find a fix for reading PST sub-folders????

        • https://officetricks.com/ kumarapush

          I am yet to resolve it. Will post once I get a solution.

  • http://www.game-modo.com Richard Dixon

    Great stuff. Been looking for something like this. Just one quick question, say I want to import gain with new emails. Is there anyway it can look to see if the email has already been imported? If so don’t import the data onto excel. I was thinking of using this on a daily basis, just dont want to be importing in old emails.

    • Loian0200309

      Hey Richard, Have you every gotten this resolved? I am looking for a way to not import what i’ve already imported over to excel also……

      • https://officetricks.com/ kumarapush

        If you have to cross check for already imported emails, then it needs additional logic that verifies Excel data against Outlook. Instead you can insert a Date logic that to exclude exporting Old emails.

        I have recently updated the code with a Date Parameter. Please check it.

  • Ravi Mangal

    I am getting “run time error 2147221233 automation error” while running this Macro.

  • Ravi Mangal

    I am getting “run time error 2147221233 automation error” while running this macro

    • https://officetricks.com/ kumarapush

      What is the MS Outlook & Excel version that you are running? Also, Let me know whether you followed instructions described in article (before the code).

      • Ravi Mangal

        I am using Outlook 2010 and Excel 2010. I have done :Tools -> References and enable check box at “Microsoft Outlook nn.n Object Library (nn.n is version of Object Library that varies depending on your Office Installation)

      • Ravi Mangal

        Yes, Now I can Run this macro.Can you please help me out to fix below points.:

        1. How we can give a path for different Excel sheet ..For example I want o/p of this macro in somewhere else then where I need to change.

        2. Can we filter the Downloading mails like I want to download only the mails which is sent to abcd@xyz.com.
        For this where I need to change ?

        • https://officetricks.com/ kumarapush

          1. This macro writes the output in Sheets(1). In case if you want write to 2nd sheet, then replace all Sheets(1) to Sheets(2) . If you have a named sheet, assume “OutputSheet” then replace Sheets(1) to Sheets(“OutputSheet”)
          2. Use this condition inside the Loop before writing any output:
          IF VBA.UCASE(Folder.Items.Item(1).To) = VBA>UCASE(“abcd@xyz.com”) Then
          ‘Code to write results to output sheet
          END IF

          • Ravi Mangal

            Thanks Kumara,

            Thanks for your reply.
            I mean to ask , if I want output in workbook which is in another location say C/myfolder/Somewhere.xlsx

            • https://officetricks.com/ kumarapush

              Please search for term “Refer external Excel file” in this site. There is a free code to access external file also.

          • Ravi Mangal

            Hi Kumar ,

            I am getting “Compile error: Output mismatch”

            I am writing below code to filter a subject :

            IF VBA.UCASE(Folder.Items.Item(1).To) = VBA>UCASE(“abcd@xyz.com”) Then

            • Ravi Mangal

              Hi Kumara,

              Thanks for your continued support.

              Now there is no error, but there is no output in sheet1 which should be filtered by subject “Friday”

              I have so many sent Items which contains “Friday” string .

              For Example : “Friday 10th April – 2G Refresh RANSOG”

              I am using below full code :

              Sub Download_Outlook_Mail_To_Excel()
              ‘Add Tools->References->”Microsoft Outlook nn.n Object Library”
              ‘nn.n varies as per our Outlook Installation
              Dim Folder As Outlook.MAPIFolder
              Dim iRow As Integer, oRow As Integer
              Dim MailBoxName As String, Pst_Folder_Name As String

              ‘Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
              MailBoxName = “ravi.rv.kumar@ericsson.com”

              ‘Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
              Pst_Folder_Name = “Sent Items” ‘Sample “Inbox” or “Sent Items”

              Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)
              If Folder = “” Then
              MsgBox “Invalid Data in Input”
              GoTo end_lbl1:
              End If

              ‘Read Through each Mail and export the details to Excel for Email Archival
              ThisWorkbook.Sheets(1).Activate
              Folder.Items.Sort “Received”

              ‘Insert Column Headers
              ThisWorkbook.Sheets(1).Cells(1, 1) = “Sender”
              ThisWorkbook .Sheets(1).Cells(1, 2) = “Subject”
              ThisWorkbook.Sheets(1).Cells(1, 3) = “Date”
              ThisWorkbook.Sheets(1).Cells(1, 4) = “Size”
              ThisWorkbook.Sheets(1).Cells(1, 5) = “EmailID”
              ThisWorkbook.Sheets(1).Cells(1, 6) = “Body”

              ‘Insert Mail Data
              For iRow = 1 To Folder.Items.Count
              oRow = iRow + 1

              If VBA.UCase(Folder.Items.Item(1).Subject) = VBA.UCase(“RE:”) Then

              ThisWorkbook.Sheets(1).Cells(oRow, 1).Select
              ThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).SenderName
              ThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).Subject
              ThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).ReceivedTime
              ThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).Size
              ThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress
              ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).Body
              End If

              Next iRow
              MsgBox “Outlook Mails Extracted to Excel”

              end_lbl1:
              End Sub

              Please help me out take output.

      • Ravi Mangal

        Its working fine , actually I put wrong mailboxname.After putting right one its working fine now.

  • Loian0200309

    Kumarpush, I really appreciate the code. This code brings in the body of the email, which is what I have been looking for for the past few days. But I would like the code to go one step further with the body of the email and I was wondering if you can help me. I would like to extract specific wordings in the body of the email to different columns in excel. For example, the body of email is all in the same format, but I need to exact the name, address, zipcode, and phone# in the body of the email and put into excel, under seperate header column called: Name, Address, ZipCode, and Phone#. etc… Can you help me please? I look foward to your help. Thank you so much for your time and effort with helping. VERY MUCH APPRECIATED!

    • https://officetricks.com/ kumarapush

      Please email me a sample mail content after extracting it with above code. That would help me in progressing with a code.

      Note: If you have already got solution to this problem, please post your answer here, so that it would help others.

  • robstar

    Hi,
    Thanks so much for the code! However I seem to have run into a problem: i cannot set the time less the 17 days when I clearly have emails that I’ve received these past 17 days.
    Also we have a lot of emails coming in per day and we are just looking for emails sent by a particular sender, which line of code can I add to only export those?
    Thanks a lot

    • robstar

      Sorry just for additional info, when I try to change the number of day(<=60), I get "error 430: class does not support automation or does not support expected interface"
      Thanks

      • https://officetricks.com/ kumarapush

        Please share your valid email id with TeamViewer Id and passcode to connect to your system and resolve issue.
        Not able to get any clue on why it is not working in your scenario.

        • robstar

          I’m sorry I’ve resolved the timing issue, it was something to do with outlook itself, but thanks for taking the time to help me.
          Is there anyway that I can only export the emails from a certain sender, it would help my company a lot. Thanks

          • https://officetricks.com/ kumarapush

            Modify the IF condition inside the For Loop as below and replace the Sender Email Id in this condition. It should work.

            If VBA.DateValue(VBA.Now) – VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60
            AND VBA.Ucase(Folder.Items.Item(iRow).SenderEmailAddress) = VBA.Ucase("SenderEmailID@Mailserver.com") Then

            • robstar

              Great, thank you so much!

          • Mikko

            Hi Robstar! I’m facing the same problem. The code does not export newest emails. How did you resolve this issue?

  • Witloof

    Hi,

    I’ve been trying out this macro & I get a “Run-Time error ‘1004’:
    Application-defined or ojbect-defined error”

    My Workbook is limited to 9 lines, 1 being the header titles, and my inbox has a Folder.items.Count of 10 so the script should fill 11 lines.

    I’ve checked the oRow & iRow logic & to me it seems solid, so I don’t know why he can’t run through the last 2 lines.
    Is there something I’m overlooking?
    Maybe excel settings?

    Thank you for your time

    • Witloof

      Hi,

      apparently it has something to do with the body, if I comment out the line that copies the body I don’t get an error.

      • https://officetricks.com/ kumarapush

        I am not able to understand why it is not working. Please mail me with Teamviewer Passcode, so that I could help you in resolving the issue. ( kumarapush123@gmail.com )

  • Dileep

    Hi, I would like to download email received date and folder name from all .pst’s. could you please help me with the code.

    • https://officetricks.com/ kumarapush

      It can be done by merging the below process.
      #1 – This loop traverse through all the PST folder in your Outlook Mailbox.
      For Each Folder In Outlook.Session.Folders(MailBoxName).Folders

      #2 – The code below this label reads all the Emails in a PST folder.
      Label_Folder_Found:

      Merging these 2 process will allow you to read all emails in all folders.

  • sraul07

    im new to this programming. can anybody help me? below is the error that im getting

    • https://officetricks.com/ kumarapush

      You cannot define your Email ID as string. Why did you replace the keyword Pst_Folder_Name to your Email id?
      Please compare the code in this page and the line marked as red in your screenshot.

  • Paul

    im new to this programming. can anybody help me? below is the error that im getting

    • paul

      It’s happend at Folder.Items.Sort “Received”

      • paul

        The problem is that I use a dutch program, received need to be chance in “ontvangen” dutch for received.
        Now it’s works.
        Is it possible to read only the last email? I receive Forms in de body of the email. And i would read de Name, adress, etc. and put it in to a row with the same title ( name, adress. etc.)

        • https://officetricks.com/ kumarapush

          It is Possible.

          #1. To Read Last Email: You have sorted all the emails based on ‘Mail Received Time’. So, the last Email will be
          at End or at First Of the For Loop. So, get only the First or Last Email.
          #2: To Read Form: Use this command Folder.Items.Item(iRow).Body to read the contents in your Email. This will help in reading Name, Address etc.,and put in the row in Excel.
          If you still have doubts, we can do a TeamViewer Session and resolve it.

  • Manoj Parag

    Hi,
    Thank you for the above code. But I am struggling with the folders option, whereas I wanted to export the sub-to-sub folder in Inbox.
    Please help me with that..

    • https://officetricks.com/ kumarapush

      When you mention as struggling, please let me know what is exact problem you are facing?
      Did you try the code and it shows any error ? or
      It didnot find your sub-to-sub folder?

  • Nick Di Taranto

    Hi,
    Thanks for the code!
    I was hoping you could help me take this code one step further and retrieve specific information in the body of the e-mail (it is a table) and sort it into different cells in Excel. The data I want to sort is the price index for three items.
    Thank you for your help.

    • Nick Di Taranto

      I should also mention that I want the data in different cells because I want to use a Vlookup function to take the data in each cell to a different that is harboring a variety of data from different sources.

  • newoay

    Hi,

    Thanks alot for the code. I’m facing this error while running the code. Any ideas why?

    • https://officetricks.com/ kumarapush

      Please confirm whether you followed the steps explained just above the erroneous line?
      Read the instruction given in comments just above the line that gave error.

      You have to include reference for Outlook Library.

      • newoay

        Hi,

        Oh yea sry i kept thinking I had included the reference when it was Microsoft Office Object Library that I had selected. Thanks for the prompt reply.

        Could I ask if it’s possible for the VBA to download the contents from the Outlook email which has a specific template? E.g. The email contains Name, DOB, I/C and these information are downloaded into Excel of columns “Name”, “DOB”, and “I/C”.

        Thanks.

  • Mervyn Erasmus

    Hi there,
    I would like to include the “ToName” field… I also get a
    Run-time error 6:
    Overflow.

    Hope you can assist.
    Kind regards and greetings from sunny South Africa.

  • Audra Calvo

    Hi there, I am hoping you can assist me with what I’m experiencing with the above code. It is running just fine until it gets to the end of the data (I have it set to pull emails received in the last 45 days) then the workbook just freezes and the MsgBox never pops up with the message saying the extract was completed. I eventually have to hit escape to interrupt the macro and the details of where it got stuck is highlighted in yellow below. Any assistance you can provide me is greatly appreciated!

    • https://officetricks.com/ kumarapush

      Not able to find what the error is with this screenshot. I think the code is taking more time to execute. How many email items are there in your mailbox?
      I would be able to help you quickly if you could provide a TeamViewer access.

      • Audra Calvo

        Unfortunately I’m unable to use TeamViewer as my company handles sensitive data. The email folder currently has approximately 5,400 emails so I believe you are right, it is still trying to execute. I’m going to try running it again tomorrow and leaving it be in hopes that it will complete the process eventually. Thanks so much for your help!

      • imran b

        what will we do for taking so much time

  • Craig Beare

    Hello kumara push!

    Awesome work ! I am receiving the following errror message I am using excel 2013 is this a version issue ?

    “error 438

    “Object does not support this property or method”

    I have set the reference and replaced the mailbox name & folder name … Thanks!

  • Chris

    I am getting a run time error 7 for out of memory when I add the body export code. Any tips on what I can do to avoid this?

  • Eli

    Hi, it’s Eli. I asked about this code a while back and it’s been a huge help for our organization–thank you!

    Now, I’m wondering if there’s a way to take the code one step further. I want to be able to extract specific content from the body of the email. Specifically, I’m looking to somehow pull out the name that a person signs himself as when he write to us. Can you think of a way to get that out as a separate column of data?

    I see so many variations of it, but perhaps the rule is the last line of body text in the email, but before their preset signature. Is that possible?

    Thank you again!

    • https://officetricks.com/ kumarapush

      To extract body of email use this command line – “Folder.Items.Item(iRow).Body”
      To extract a specific content, string operations have to be applied for the content extracted from above command. To give you some hint, these are few string operations that could be applied. ‘Mid, Left, Right, Instr’ etc.,

      • Eli

        Can you give a more detailed hint? I’m so new at VBA that I’d love a little more to start with…

        • https://officetricks.com/ kumarapush

          I doubt whether it is a good idea to take the sender name from Email Signature. Because each person will have signature in different formats and we cannot make a computer to differentiate a name with other signature text.

          As an alternate suggestion, you could also pick the sender name from ‘Folder.Items.Item(iRow).SenderName’. Will this be suffice?

          If you are looking to extract any other detail from mail body, then please share a sample email along with what exactly you are looking to extract.

          • Eli

            Thanks again for your response. What we’re trying to do is lift the way a person is signing his/her name in emails to us so that we know the best way to address them in future emails. Here’s an example:

            We sent a holiday wishes email to all of our customers, and in Salesforce we have their full name stored, and that’s how we addressed them. In the case below, we wrote:

            But in their friendly response back, they signed off as Patti. See here:

            I’d love if we can use VBA to lift the way they sign off–not their official signature so we can store it as the best way to address them in future emails. I was hoping we can make a rule to somehow find that based on the fact that most people use spacing before and after their name.

            • https://officetricks.com/ kumarapush

              Sorry Eli. I guess it is not possible.
              Unless we have a specific template, it is nearly impossible to extract sender name in this way.
              It involves too much AI (artificial intelligence) to differentiate name vs other text in email content.

  • Karun

    Hi Kumar,
    Thanks for the wonderful code. Its been big help for me so far.
    I would request your help to sort out one of the challenge I am facing. Whenever I try to run the code with “Body” , it frequently throws “Out of Memory” error. Most of the times, I will debug it and skip to the next row to get the results. But its tedious process and so many mails are missing (of course I am skipping it).
    Wherever I see these errors I noticed that respective mail contains a “Screenshot” (Picture) in its body not as an attachment.
    Is there a way to import only “Plain Text” from the mail . Or can you help with the syntax of left () in the VBA script.
    Appreciate your help and looking forward for your reply.
    Thanks
    Karun

    • https://officetricks.com/ kumarapush

      Try one of this:
      1. Add this like just before extracting the body to excel. ‘Folder.Items.Item(iRow).BodyFormat = olFormatPlain’

      or
      2. Add “On error resume next’ – this will auto skip the line if any error occurs.

      Drop a comment if these 2 methods did not work.

  • MOHAMMAD SHABBIR TAIBANI

    Hi Kumar , Thanks for sharing code, it is awesome,

    I was looking for similar code, with this I also want to add few things

    On daily basis I want to see Inbox and Sent Item as dashboard in excel ( in order to verify whether all emails are answered or not and in how much time they were replied)

    Thanks in Advance

    • https://officetricks.com/ kumarapush

      Hi Mohammad, I could any solution for this. If you are able to solve it, please post the solution here. That would be helpful to others.

  • Chuck A

    Hi Kumar,

    This is a lovely piece of code and worked quite well for me the last 6 months. However, now I am changing to a new computer (with more memory for Excel stuff) and the code no longer works. I can’t figure out why. I checked the object library and that is the same, as is everything else, file paths, etc. Can you help? My email is a company email and the folder has not changed within that when the computer connects to it.

    I get “Run-time error… The attempted operation failed. An object could not be found.”
    and the debugger claims this to be the faulty line:

    For Each Folder In Outlook.Session.Folders(MailBoxName).Folders

    However, all folders look exactly the same. The macro still works on the original pc, and that is how I am keeping up to date with processing new emails, so it is not a change in the email folder.

    p.s. New pc has Office 2010 and 2013 both installed, but it also doesn’t work on a 3rd pc – an older 32-bit with just Office 2010.

    • https://officetricks.com/ kumarapush

      I am not able to get any clue on how to solve this issue. May be a Teamviewer session would guide towards finding the exact issue or Please raise a issue in Microsoft forums.

      If a teamviewer is possible, please email or text me in gtalk “officetricks123@gmail.com”.

      • Chuck

        Thank you, Kumara! You’ve got sharp eyes! You spotted the issue, which was that during the past 6 months my company name had changed slightly. This resulted in being issued a new email, but they also kept all our old emails active. So I had two emails (old and new) which both worked still. However, on the older pc Outlook was mapped to the previous email and on the newer pc, it set up using my newer email with the different companyname in the path. Thank you, good sir!

      • Shreyas

        Hey, I too got the same error:
        For Each Folder In Outlook.Session.Folders(MailBoxName).Folders
        Could you please help?

  • AD

    Hi, I’m running into the same Run-Time error as the person below. I did the edits and double checked the mailbox name and still having issues. I have Office 2007. I saved the Excel workbook in the same directory as the pst and still getting that error.

    • https://officetricks.com/ kumarapush

      Still not able to find why this issue occurs. please refer below reply.

    • https://officetricks.com/ kumarapush

      Please triple check whether the mailbox name in VBA code is exactly the same as it is displayed in your Outlook App.

  • Kabbil

    Hi Kumar,

    Seriously an amazing code! I have nothing else to say except for thank you!!

    However, I am observing the following error when i run the macro :

    Run-time error 438.

    Could please tell me what is causing this error?

    I have been trying to debug it but can’t seem to find a proper solution.

    Appreciate if you could share your knowledge in establishing a working solution.

    Thank you!

  • Nessim

    Hi Kumar, this code is perfect, thank you for sharing.
    I currently have my mailbox and a shared group mailbox. the code works fine on my mailbox but doesn’t seem to recognise the group mailbox, do you know why?
    Cheers
    Fady

    • https://officetricks.com/ kumarapush

      Is this resolved. if not, please share me a Team viewer session to help out.

  • Narendra Kilari

    Hi Kumar,

    Thanks for your wonderful code,

    My mail body contains the data in Tabular Format, if I ran the macro all values are copying in One cell.
    but I need to copy and paste the mail data in Tabular format on excel , could you please help me out.

  • Narendra Kilari

    Thanks for your wonderful code,

    My mail body contains the data in Tabular Format, if I ran the macro all values are copying in One cell.

    but I need to copy and paste the mail data in Tabular format on excel , could you please help me out.

  • Narendra Kilari

    Thanks for your wonderful VBA code, I need some help

    My mail body contains the data in Tabular Format, if I ran the macro all values are copying in One cell.

    but I need to copy and paste the mail data in Tabular format on excel , could you please help me out.

    Thanks in Advance
    Narendra

    • https://officetricks.com/ kumarapush

      There is another code in this website. Search for extract HTML table to Excel. You can merge that code with the code in this page and extract Table items to different rows.

  • Linkun de Boer

    Great VBA code, but I was wondering if it is possible to only import unread messages from that folder, add that to the excel sheet and then change the state of the mail to read.

    Linkun

  • https://officetricks.com/ kumarapush

    For all those kind readers, in case of any error with this code, please provide me a teamviewer access to debug and help you resolve the issue.
    Its very hard for me to replicate the issues that you are encountering in your laptop.
    I would be more glad to help you.
    Thanks all for your Support.

  • Sanjay

    hi, code is working. Thanks, is there any way I am able to extract a specific line in the body of the email

    • https://officetricks.com/ kumarapush

      Complete content in the body of the email can be fetched with this line ‘Folder.Items.Item(iRow).Body’ . From this, which ever line item you want to extract, can be done.
      Would be easy to devise a solution, if you explain the criteria you mean by “specific line”.

  • mackeg

    I am getting a Compile Error shown below and it marks the main line of the code “Sub Download_Outlook_Mail_To_Excel()” I changed my mailboxname, subfolder and commented out the body to pull in the body. Do I need Outlook running or not when I execute the code?

    • https://officetricks.com/ kumarapush

      Please confirm that you have added the references mentioned in the comment below this line ‘Sub Download_Outlook_Mail_To_Excel()’.
      Also, please run the code after opening the Outlook application.

  • John

    Hi Kumar,

    Is there a way where I can get the number of emails I have responded to and the number of emails I am yet to respond? Thanks!

    • https://officetricks.com/ kumarapush

      I could not find a direct property to check whether a email is responded or not.
      But, this can be done with these below steps:
      #1. Exporting list of emails in Inbox to worksheet1
      #2. Export list of emails in Sent Items to worksheet2
      #3. Cross check ‘Email Subject’ of both list. Items missed in Sent Items are yet to replied.

  • Kaz

    HI

    Its giving me invalid qualifier error for ThisWorkbook.

    • https://officetricks.com/ kumarapush

      Hi, Please email me the screen shot of the error. But it would be easy for me to debug the code if you could give me a Teamviewer access to connect to your computer. (my email: officetricks123@gmail.com)

  • Mohamed Azkarali

    Hi Team,
    I need one clarification, when we received an email in inbox it will show the received date& timing,
    if I’m reply back to the email it shows a sending timing, my question is: if I’m copy past the email from the outlook to excel I’m getting only the received timing. but I need the both received and sending timing.
    can u help me with this.
    my contact email ID: mazkarali@gmail.com