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

December 02, 2024
Kumarapush

Export Emails From Outlook to Excel with Date and Time

[ Table of Contents ]
I. Export Emails From Outlook to Excel with Date and Time
II. 1. How to Export Outlook Emails To Excel?
III. 2. Outlook VBA: Copy Email Fields to Excel with Date and Time
IV. 3. How to Archive email in Outlook? – On Demand
V. 4. How to Schedule Outlook Auto Archive?
VI. Misc about Export Outlook Emails To Excel or Email Archiving
VII. Additional References

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 8,988 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

Option Explicit
'This Code is Downloaded from OfficeTricks.com
'Visit this site for more such Free Code
Sub VBA_Export_Outlook_Emails_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 sFolders 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 = "MailBox Name"
 
    'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
    Pst_Folder_Name = "Folder Name" 'Sample "Inbox" or "Sent Items"
 
    'To directly a Folder at a high level
    'Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)
    
    'To access a main folder or a subfolder (level-1)
    For Each Folder In Outlook.Session.Folders(MailBoxName).Folders
        If VBA.UCase(Folder.Name) = VBA.UCase(Pst_Folder_Name) Then GoTo Label_Folder_Found
        For Each sFolders In Folder.Folders
            If VBA.UCase(sFolders.Name) = VBA.UCase(Pst_Folder_Name) Then
                Set Folder = sFolders
                GoTo Label_Folder_Found
            End If
        Next sFolders
    Next Folder

Label_Folder_Found:
     If Folder.Name = "" 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"
    
    'Export eMail Data from PST Folder to Excel with date and time
    oRow = 1
    For iRow = 1 To Folder.Items.Count
        'If condition to import mails received in last 60 days
        'To import all emails, comment or remove this IF condition
        If VBA.DateValue(VBA.Now) - VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60 Then
           oRow = oRow + 1
           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"
    Set Folder = Nothing
    Set sFolders = Nothing
    
End_Lbl1:
End Sub

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

Related Articles

Excel VBA Create New Workbook – With Name – Copy Save Close

Outlook Vba to Send Email with Attachments – Easy Code

VBA Msgbox with Yes No Cancel Options

130 thoughts on “Export Outlook Emails To Excel – VBA Code – 5,700+ Free Downloads”

  2. 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^_^

    1. 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.

          1. 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.

    1. 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.

    2. 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.

      1. 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.

        1. 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.

          1. 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!

    1. 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).

  7. 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

    1. 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.

    1. 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.

      2. 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.

            1. 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.

              1. 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.

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

  10. 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?

    1. 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.

  11. 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.

  12. 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.

    1. 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?

  13. 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 🙂

  14. 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

    1. 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.

      1. 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????

  15. 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.

    1. 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……

      1. 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.

      1. 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)

      2. 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 ?

        1. 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

          1. 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

          2. 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

            1. 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.

  18. 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!

    1. 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.

  19. 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

    1. 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

        1. 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

          1. 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

  20. 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

    1. 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.

    1. 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.

      1. 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.)

        1. 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.

  24. 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..

  25. 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.

    1. 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.

    1. 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.

      1. 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.

  27. 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.

  28. 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!

    1. 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.

      1. 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!

  29. 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!

  31. 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!

    1. 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.,

        1. 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.

          1. 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.

            1. 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.

  32. 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

    1. 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.

  33. 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

  34. 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.

    1. 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”.

      1. 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!

  35. 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.

  36. 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!

  37. 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

  38. 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.

  39. 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.

  40. 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

  41. 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

  42. 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.

    1. 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”.

  44. 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?

    1. 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.

    1. 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.

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

Leave a Reply