How To Import Your Outlook Emails Into Excel With VBA

2017-01-08

VBA

If you get a lot of emails and you need to analyse the data in them, then importing your emails from Outlook into Excel with VBA will save you a lot of time. In this post we’re going to explore a way to import all emails in a given folder that were received after a certain date. Obviously, you will need to have Microsoft Outlook installed on your computer for this to work.

If you’re unfamiliar with VBA, then it’s probably a good idea to read this post about How To Use The VBA Code You Find Online before continuing reading and working with the completed workbook.

For this code you will need the Microsoft Outlook 16.0 Object Library enabled. In the visual basic editor, go to Tools then References and check the box next to it and press OK button to enable it.

In this example I’m going to be importing from an Outlook folder called Inbox/Net Sales Report/Sales. If you wanted to import from a subfolder of Sales then you would need to append another .Folders(“Subfolder Name”) onto the Set Folder = line of code.

I have added named ranges to the workbook as referring to a cell with a named range in VBA rather than the generic cell address such as Range(“A1”) means you can move things around in your workbook without worrying about breaking your code. These are the named ranges the code will use.

  • From_date – This cell will allow the user to input a From Date so that only emails received (and in our Sales folder) after this date are returned.
  • eMail_subject – This cell contains the Subject column heading. Subjects from emails will be imported just below this cell.
  • eMail_date – This cell contains the Date column heading. Date received from emails will be imported just below this cell.
  • eMail_sender – This cell contains the Sender column heading. Sender information from emails will be imported just below this cell.
  • eMail_text – This cell contains the Email Text column heading. The main text body from emails will be imported just below this cell.

Here is the code.

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

i = 1

For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime >= Range("From_date").Value Then
        Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body
        
        i = i + 1
    End If
Next OutlookMail

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

End Sub

It’s a pretty simple VBA procedure, but it can be very useful if you have to deal with a lot of similar emails.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

69 Comments

  1. mike

    how would I add additional conditions such as “to sender” and “subject?

    • John

      You would need to change the line of code:
      If OutlookMail.ReceivedTime >= Range("From_date").Value Then
      To something like this:
      If (OutlookMail.ReceivedTime >= Range("From_date").Value and OutlookMail.Subject = Range("Subject_condition").Value and OutlookMail.SenderName = Range("Sender_condition").Value) Then
      You will then need to add Subject_condition and Sender_condition named ranges into the worksheet and these will be where you enter conditions for the subject and sender.

  2. rICHARD

    does this work for outlook clients with multiple inboxes? How so? I’m getting not sub defined error when running. Do i run script from excel or outlook? Confused.

    • John

      The code is run from Excel. Unfortunately, I don’t have multiple inboxes to test anything out on but this stack overflow post looks promising. Let me know if you get it to work.

  3. samad

    i have to import data from inbox so unable to retrieve due to this error “Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders(“Inbox”).Folders(“inbox”)”

    • John

      I believe what you’re looking for is to replace this:

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

      With this:

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox)

  4. Jamison

    Hello,

    I’m trying to get the folder directory to be able to refer to a cell where the folder name (and folder sub names if applicable) will be typed.

    So far, i have
    C1= “=IF(ISBLANK(C2),””,”.Folders(“&C2&”)”)”
    C2= (Folder name)

    =IF(ISBLANK(D2),””,”.Folders(“&D2&”)”)
    D2 = (SubFolder Name)

    A2 = “=CONCATENATE(C1,D1)”

    In theory, the user will input the folder name into C2 and if they have a subfolder, they would place the subfolder in D2 which will then populate the remainder of the set Folder command.

    For example: Inbox–>C2=”Test Folder”–>D2=”Test Subfolder”

    A2 would equal .Folders(“Test Folder”).Folder(“Test Subfolder”)

    How can I get VBA to accept “Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox)” as hard coded and reference cell A2 for the extension?

    Or if it is easier, how could I get “Set Folder” to reference a cell.

    Hopefully the above makes sense, thank you in advance for any insight!

    • John

      Try naming your ranges in the worksheet to something like Test_folder and Test_subfolder. Then you can refer to these cells in the VBA as Range(“Test_folder”) and Range(“Test_subfolder”).

      To set the folder try replacing this.

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

      With something like this (I say like, because I don’t have an outlook email setup anymore and can’t test if it works).

      If IsEmpty(Range("Test_subfolder").Value) = True Then
      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders(Range("Test_folder").Value)
      Else
      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders(Range("Test_folder").Value).Folders(Range("Test_subfolder").Value)
      End If

      • Jamison

        John,

        That worked beautifully! And your code worked great as well!

        Thank you for your help!

        • John

          No problem!

  5. Jason

    Hi John,

    Thanks for your sharing knowledge. It’s what I am looking for. I have a question.

    I’d like to get email from our team mail box which is share mail box. The path is Inbox > subfolder1 > subfolder2. How can I set the path to shared mail box?

    • John

      Unfortunately, I’m not sure if this will work on a shared mailbox, but you can try changing this part of the code.

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

      To this.

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("subfolder1").Folders("subfolder2")

      • Ryan T Dal

        Hi There, I know im kinda late on this thread, but i stumbled across your macro, and i found a way to use this with a shared inbox/folder.

        i added the following lines:
        Dim strMailboxName As String

        strMailboxName = “Internal Team 1” ‘name of shared inbox

        Set Folder = Session.Folders(strMailboxName)
        Set Folder = Folder.Folders(“Inbox”)
        Set Folder = Folder.Folders(“AUDITS”)

        here was my final code:
        Sub GetFromOutlook()

        Dim OutlookApp As Outlook.Application
        Dim OutlookNamespace As Namespace
        Dim Folder As MAPIFolder
        Dim OutlookMail As Variant
        Dim strMailboxName As String
        Dim i As Integer

        strMailboxName = “CMS Internal Team 1”

        Set OutlookApp = New Outlook.Application
        Set OutlookNamespace = OutlookApp.GetNamespace(“MAPI”)
        Set Folder = Session.Folders(strMailboxName)
        Set Folder = Folder.Folders(“Inbox”)
        Set Folder = Folder.Folders(“AUDITS”)

        i = 1

        For Each OutlookMail In Folder.Items
        If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then
        Range(“eMail_subject”).Offset(i, 0).Value = OutlookMail.Subject
        Range(“eMail_date”).Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range(“eMail_sender”).Offset(i, 0).Value = OutlookMail.SenderName
        Range(“eMail_text”).Offset(i, 0).Value = OutlookMail.Body

        i = i + 1
        End If
        Next OutlookMail

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

        End Sub

        HOPE THIS HELPS!

        • John

          Great stuff. Thanks!

  6. ABDUL KHATHAR

    How would we get Sender’s EMail ID as well?

    • John

      I’m not sure I know what an email ID is, but since email addresses are unique identifiers you can use that!

  7. Devesh kumar

    Hi This is really useful, can I get the reply time as well

    • John

      Since emails don’t necessarily have a reply, you could look in your sent folder for OutlookMail.SentOn.

  8. rahul

    Hello,

    I tried to run this code with Office 2016. After a few cycles, it didn’t seem to work. I figure it is to do with updating the Object Library.
    How will the declarations change to work with office 2016 ?
    Thanks for the Help

  9. KAYLA

    Hello There,
    I am a novice to this. However when I paste into my module, change inbox folder locations I am getting “Compile error User defined type not defined.” Per what I found I installed MDAC 2.8 and selected that version in tools->references.

    What am I doing wrong?

    • Kayla

      I was able to make adjustments to the code and have successfully pulled emails into Excel. Thank you all for sharing!

      • John

        Good to hear you figured it out. Maybe you could share the solution to help anyone else having the same problem?

        • Kayla

          It was simple. I needed to open your “Download Example File”. Once I made adjustments specific to my inbox, I was able to pull my emails into the sheet.

          Thank you.
          -Kayla

  10. Vinod

    Hi,

    I downloaded the attached file. It imported all the emails from my specified folder. Then i saved it as a macro enabled file in MS Excel 2016. Later I opened the saved file, to import more new emails, at that instance, it didn’t work. The total row count still stayed the same.

    So cleared the contents (A4 : G 100) and tried to run the macro again, this time it didn’t import any mails, not even the ones that it imported initially. I have no idea why.

    Any help will be appreciated.
    Thank you

    • John

      The code uses a named range in the sheet to output data to and it might be that you deleted the named range?

      • vINOD

        Not really, the names were from A4 to D4, which I never touched. I downloaded a file again from this site and made it run on MS 2016. It worked on the very first instance, but once I saved the file and opened it the next day to import new files into the sheet, it didn’t work. I’m not sure why.

        Thanks in advance for any help.

  11. Raad

    How I can assign my outlook pst file name in this code

    • John

      Unfortunately, I’m not familiar with pst files. Try asking on a forum. Let me know what you find!

  12. Ethan

    HI John.

    I downloaded the file and got this Run-Time error ‘438’ : Object doesn’t support this property or method. it was pin-pointing the code
    “If OutlookMail.ReceivedTime >= Range(“B1″).Value Then”. I am using O365. can you please help me on this.

    Thanks!
    Ethan

    • John

      My code refers to a named range instead of B1. Make sure this is a date in the cell and not text. Download the file again and start over as you might have inadvertently changed something.

      • Ethan

        i downloaded again and did not change any but still have the same error.

  13. Anders

    Hi! I’m totally new to this, so It’s probably my bad, but I’m getting “Compile error: User-defined type not defined” and it’s marking “Dim OutlookApp As Outlook.Application” as the problem line. What am I doing wrong?

    • John

      Hmm, check and make sure you have the Microsoft Outlook 16.0 Object Library enabled. In the visual basic editor, go to Tools then References and check the box next to it and press OK.

      • Ali

        Looked in the VBA editor, clicked on tools, but “References” show just a shadow. Is there away round this?

        Error message was: User-defined type not defined. With the following text at the top highlighted in blue colour.

        Dim OutlookApp As Outlook.Application

        Your tips please, and thanks in advance.

  14. Kishore

    How to pull data from other folder (other than inbox and it’s subfolder) say for example I have created folder name as “personal” and I want to pull data from this folder.

    • John

      You need to change this bit of code to suit your folder structure.

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

      Change to

      Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Personal")

      • Dylan

        Any way to not go into the inbox folder to begin with?

        Say I have a folder outside of the inbox folder labelled “Important” and then a subfolder “Extra Important”

        Right now, when I want to do it, I keep getting an error.

        • John

          Unfortunately, I don’t know off hand and don’t have outlook anymore to test anything out.

  15. Ramalingam

    is it possible to import from specific sender

    • John

      Just filter the results on the specific sender after, or add in a condition to the code.

      Replace this line:
      If OutlookMail.ReceivedTime >= Range("From_date").Value Then

      With this line:
      If OutlookMail.ReceivedTime >= Range("From_date").Value and OutlookMail.SenderName = "Person's Name Goes Here" Then

  16. Muthu

    Sub GetFromOutlook()

    Dim OutlookApp As Outlook.Application
    Dim OutlookNamespace As Namespace
    Dim Folder As MAPIFolder
    Dim OutlookMail As Variant
    Dim i As Integer

    Set OutlookApp = New Outlook.Application
    Set OutlookNamespace = OutlookApp.GetNamespace(“MAPI”)
    Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox)
    i = 1

    For Each OutlookMail In Folder.Items

    If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then
    Range(“eMail_subject”).Offset(i, 0).Value = OutlookMail.Subject
    Range(“eMail_date”).Offset(i, 0).Value = OutlookMail.ReceivedTime
    Range(“eMail_sender”).Offset(i, 0).Value = OutlookMail.SenderName
    Range(“eMail_text”).Offset(i, 0).Value = OutlookMail.Body

    i = i + 1
    End If
    Next OutlookMail

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

    End Sub

    I got error message for below line

    If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then

    • John

      Did you copy and paste the code? It’s referring to a named range (From_date), so that will need to exist in the sheet. Try downloading the template. Also make sure to enable the outlook library.

  17. Muthu

    Hello Friends,

    Please convert the below formula to vba.. i need this formula for how many row’s field data it’s show the result

    =LOOKUP(10^12,MID(C4,ROW(INDIRECT(“1:”&LEN(C4)-9)),12)+0)

    thanks in advance

  18. Alex

    Hi all.

    Any ideas on how to paste the entire email body into multiple cells? I got the above code to work, but it is pasting the whole body into one cell, which is undesirable. Any thoughts?

    Thanks

    • John

      Well, it depends on how you want to parse it out.

      I’d say it’s best to do that after the import by VBA and use either formulas or power query to do what you need.

      • Alex

        Thanks John. I tried that approach but each cell can only fit a limited amount of text after importing. I am looking for a solution similar to copying and pasting an entire email of first, last names and their addresses on contiguous cells in excel (a vertical list). My alternative approach would be to use vba in outlook and export the body of the email into excel, but i have already spent a bit of time on the excel approach. Any ideas?

  19. Baron Feilzer

    Hi John,

    Great code but I’m running into a problem. When I run it, it will work for about 150 emails and then it stops and I get the message: “Run-Time error ‘438’ : Object doesn’t support this property or method.” It points to the code: “If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then”

    It does the same thing when the code runs completely through. Like it I only do it for the last 15 minutes, it copies the correct information over to Excel but then gives me that error code. I’m using Office 2013 and have the correct reference applied. The only thing I changed in the code was the folder path. Any ideas?

  20. Dave

    Hi John,
    This is really useful, I downloaded the template and ran it up and it failed with a 438 on Range(“eMail_date”).Offset(i, 0).Value = OutlookMail.ReceivedTime so I added a loop to stop after x number of items and I have tried OutlookMail.SentOn

    but still get an error after processing so many items.
    So I assumed I had something in my inbox without a recievedTime or Senton time so I tried and then used the variable
    (My outlook is in US date format and excel etc in UK hence the format for comparison later on)
    If OutlookMail.Class olMail Then
    If IsNull(OutlookMail.SentOn) = True Then
    dtIn = DateValue(“01/01/2099”)
    End If
    Else:
    dtIn = Format(OutlookMail.SentOn, “dd\/mm\/yyyy hh:mm:ss”)
    End If

    I tried it without the IsNull(OutlookMail.SentOn) but it failed on the format statement so it looks like I have a mail item without a the date field attributes.

    Do you know of a way of trapping for this so I can add a default date for comparison.

    Thanks
    Dave

    • Mus

      i had the same issue, below resolved the issue ( I think it was hitting error when if condition becomes FALSE so I’m basically terminating when it happens)

      If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then
      Range(“eMail_subject”).Offset(i, 0).Value = OutlookMail.Subject
      Range(“eMail_date”).Offset(i, 0).Value = OutlookMail.ReceivedTime
      Range(“eMail_sender”).Offset(i, 0).Value = OutlookMail.SenderName
      ‘Range(“eMail_text”).Offset(i, 0).Value = OutlookMail.Body

      i = i + 1

      Else
      MsgBox “No more mails”

      Set Folder = Nothing
      Set OutlookNamespace = Nothing
      Set OutlookApp = Nothing
      Exit Sub

      End If

      • John

        Thanks Mus!

  21. Gangadharam

    HI John,

    thanks you so much for sharing knowledge its very useful
    and it working in my system also i would like to use in office not sure it will work or not let you know the status and i will get back to you required help

    thanks lot for good work
    Regards,
    Gangadharam

  22. Edren

    Hi John,

    Thanks for this, this is really useful. May I ask if you know what to do if I have 2 accounts in outlook and want to only choose one of them.

    • John

      Unfortunately, I don’t know off hand and don’t have outlook anymore to test anything out.

  23. MARIO

    Dim OutlookApp As Outlook.Application
    “User defined type not defined” ?

    • John

      Did you enable the Microsoft Outlook 16.0 Object Library?

  24. Michael

    Hi! Thanks for this! It worked great. Quick question though, i’m finding it takes about 30 seconds to import 1 email. Seems like a long time.
    Any ideas why?
    Also, is there away i could adapt it to check if that email is already imported, not to import it?

    Thanks,
    Michael

    • John

      Seems long.

      When I used this, it never took more than a couple seconds to import the last week of emails. Unfortunately, I don’t know what’s going wrong for your particular case. Maybe try limiting the import to last few days.

  25. JOHNATHAN TEJEDA

    Hello John,

    Thank you for your code. I am very impressed with it. We all learn from each other and you have greatly helped me with this code. My question is when I run the code I get a Run-time error ‘1004’; Method ‘Range’ of object’_Global” failed.

    When I debug it points to this line of code: If OutlookMail.ReceivedTime >= Range(“11/10/2018”).Value Then

    I entered 11/10/2018 in the “from date” field. is this correct to do?

    • John

      No. Download the example file and you’ll see there is a named range called From_date. Put your date into this cell in the worksheet and keep the code referencing the named range. Hopefully that makes sense.

  26. Vedrana

    Hello, thanks for the code, everything is working well!
    I have a question, I want to import mails periodicly, as example, today is 3.12.2018., when I click on macro it imports mails from that date, and tomorow when I set 4.12.2018. I want to import mails from that date and etc. but I want to do it on the way if I already have mails from 3.12.2018., that new emails should be added after that, by not pasting that old ones again, just Building table date by date. Is it possible? Thank you.

  27. robin

    Hi John, thanks for your code. it’s very helpful. Can you help me with an issue. I did define a custom column in a folder message view, and use this custom columns to complement the data of a email. i need to get this value from excel, but with your code I try to get with OutlookMail.Item(“NROTICKET”).Value or OutlookMail.NROTICKET.Value and doesn’t work . Nroticket es the name for the custom columns in the view.
    I get the follow error: Run – Time error ‘1004’; Method ‘Range’ of object ‘_Global’ failed.
    Can you give me a hand?
    Thanks

  28. ROY

    Hi John!

    This is extremely helpful! Thank you so much! I realize this post has been up a while, but I’m hoping you are still checking. What if the parent folder is not the Inbox. I created a folder under my account called “PROJECTS”, which would be at the same level as the Inbox (in theory, the same idea as the Deleted Items folder being at the same level of the Inbox). How would the code change then to go to the PROJECTS folder?

    Thanks,
    Roy

  29. sachin kariappa

    Hi john,

    Body of the email has too much data and it’s showing an error message as out of memory. Could you please help me to get rid of this error message?

    If possible please provide a code to skip the download of the particular body of the email whenever the data is too much.

    Thanks in advance for the help

  30. Matt

    I found some code to export an email body to an excel sheet below (modified for my purposes, of course). The issue is, the email body has a lot of line breaks and when it writes to the sheet, Excel automatically wraps the text. I tried to set the range to column A and turn of the wrap text, but it fails every time. I’ve tried different iterations of the range, i.e. A2:A50, etc., but the same thing happens. How can I get the macro to turn off wrap text for either column A or the cell it just wrote to?

    Sub Spectrum_orders(MyMail As MailItem)
    Dim strID As String, olNS As Outlook.NameSpace
    Dim olMail As Outlook.MailItem
    Dim strFileName As String

    Dim oXLApp As Object, oXLwb As Object, oXLws As Object
    Dim lRow As Long

    strID = MyMail.EntryID
    Set olNS = Application.GetNamespace(“MAPI”)
    Set olMail = olNS.GetItemFromID(strID)

    On Error Resume Next
    Set oXLApp = GetObject(, “Excel.Application”)

    If Err.Number 0 Then
    Set oXLApp = CreateObject(“Excel.Application”)
    End If
    Err.Clear
    On Error GoTo 0

    oXLApp.Visible = True

    Set oXLwb = oXLApp.Workbooks.Open(“S:\Personal Folders\Templates\Buyflow import template.xlsm”)

    Set oXLws = oXLwb.Sheets(“Spectrum emails”)

    lRow = oXLws.Range(“A” & oXLApp.Rows.Count).End(xlUp).Row + 1

    With oXLws
    .Range(“A” & lRow).Value = olMail.Body
    .Range(“B” & lRow).Value = olMail.SenderName
    End With

    ‘Range(“A:A”).WrapText = False

    ‘~~> Close and Clean up Excel
    ‘oXLwb.Close (True)
    ‘oXLApp.Quit
    ‘Set oXLws = Nothing
    ‘Set oXLwb = Nothing
    ‘Set oXLApp = Nothing

    ‘Set olMail = Nothing
    ‘Set olNS = Nothing
    End Sub

  31. Madhankumar

    Hello john,

    We really appreciate your Macro.

    We tried, Its so Good to help our Work on the Busy schedule.

    We have One Request,We need Additional Info from the mails, It’s Attachments Names.

    Could you have any Idea. Please advise to get Attainments names.

    Advance Thanks,
    Maddy

  32. Al

    Company software only goes up to Microsoft Outlook *15.0* Object Library. Is there any way to make this work, or does the functionality only exist in 16.0?

  33. 6h057

    Hi.
    Email are received with wrong order.
    I have two emails with dates

    10-04-2020 13:29:38 and
    10-04-2020 13:29:35

    email with date 10-04-2020 13:29:35 is received as last

    • John

      Just sort the list!

  34. khajabhai

    Hi John I am getting error on line

    If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then runtime error ‘range’ of object’_global’ failed

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This