Pull Outlook Emails’ Detail Into Excel Using VBA

Apr 7, 2019 | Office Automation, Outlook | 14 comments

Microsoft Outlook is probably the most popular personal organizer system today competiting with Google’s gmail. One of the major request I have frequently getting is “How do you write a VBA automation to quickly export Outlook Email detail into a Microsoft Excel spreadsheet”.  In this tutorial, I will show you how to write the VBA automation to easily and quickly  export your Outlook email information into Excel.

VBA Script

14 Comments

  1. Danny Engles
    Danny Engles on 2019-04-29 at 9:26 PM

    This is great thanks!

    Just a quick question – if I wanted to change the target folder so it isn’t the default folder and rather a Subfolder, how would I do this? I’ve tried to change the below line and replace the GetDefaultFolder(olFolderInbox) to the folder I need but am getting an error.

    From:
    Set olInboxFolder = olNS.GetDefaultFolder(olFolderInbox)

    To: Set olInboxFolder = olNS.Folders(“me@example.com”).Folders(“Submissions”).Folders(“Funding & Debtors”).Items

    Sorry I’m not great with vba!

    Reply
    • jiejenn
      jiejenn on 2019-04-29 at 1:38 PM

      In Outlook, if you are trying to access subfolders under the Inbox folder, you can use the Folders property to access the subfolder. Below is a sample code to access a subfolder called “Sub Folder” under the Inbox folder.

      Sub Test()
      Dim ns As NameSpace
      Dim olInboxFolder As MAPIFolder, olSubFolder As MAPIFolder
      Dim msg As MailItem

      Set ns = GetNamespace(“MAPI”)
      Set olInboxFolder = ns.GetDefaultFolder(olFolderInbox)
      Set olSubFolder = olInboxFolder.Folders(“Sub Folder”)

      For Each msg In olSubFolder.Items
      Debug.Print msg.Subject
      Next msg

      Set olSubFolder = Nothing
      Set olInboxFolder = Nothing
      Set ns = Nothing

      End Sub

      Reply
    • Mo
      Mo on 2022-05-31 at 10:26 PM

      When running the macro I have, unfortunately, the error User-defined type not defined.what can be done with this?

      Reply
  2. Miguel
    Miguel on 2019-09-16 at 1:51 AM

    Hi. Very neat code.

    I have a question though. In my personal mailbox I have over 7.000 items. However, this method is only taking about 4.300 items, which are not even the latest ones.
    Do you know what could be causing this?

    Also, can you do this with a shared mailbox that is in my profile but it’s not my default folder?

    Thank you

    Reply
    • jiejenn
      jiejenn on 2019-09-16 at 2:25 PM

      I believe the email retrival is only for emails saved in your local drive. If I remember correctly, Outlook automatically archive any email older than 90 days, for those emails archived in Outlook server, you will have to create a separate macro to access the server and retriving the emails.

      Reply
  3. Schueng
    Schueng on 2020-02-27 at 11:27 AM

    Hi,

    I have the same problem as Miguels that the code can only export emails received after 22nd Feb 2020. But I’ve double-checked that Outlook saved emails more than that in my local drive.

    Could you help me with it?

    Reply
  4. Kiefer
    Kiefer on 2020-04-30 at 12:07 PM

    Hey, love this idea and I’m super new to VBA – running the code as you’ve written it comes up with an error:

    User Defined type not defined, and it highlights the “Dim xlApp As Excel.Application” line 2.

    I just wanted to test that it would work and then edit it to run on a subfolder but I can’t seem to get it to work 😦

    Reply
    • Sudha
      Sudha on 2020-06-12 at 6:01 PM

      Go to Tools -> Preferences -> Microsoft Excel (VErsion) enable the check box in the VBE of the outlook. This will resolve the user defined not defined error

      Reply
  5. John
    John on 2020-07-19 at 12:44 AM

    I love this, thanks very much. I run Office Home & Business 2016. I have multiple email accounts set up. Even when I am in the inbox of a different account & run the code, it defaults to my “root” MS account email inbox. Is there a way to modify the code to tell it to use the inbox of another Outlook account?

    Reply
    • Jon
      Jon on 2022-04-26 at 5:08 AM

      Did you ever figure this out? I am having the same problem.

      Reply
  6. VBD
    VBD on 2020-07-23 at 9:02 PM

    Thank you so much!
    I’m currently running this macro (modified a little) in my Archive and it only gets ONE email. I don’t know why but… it should read all 20000 emails in that folder. Is there a way for fixing this?

    Reply
  7. Dennis
    Dennis on 2020-07-24 at 4:40 AM

    Hello,

    How would you create a pop up box where you can enter a specific creationdate time range. Like it pulls all emails from the specific time range you enter and not all that is in the entire folder?

    Thanks!

    Reply
  8. Cozza
    Cozza on 2022-01-19 at 6:05 AM

    Hi there jiejenn,

    This is a great video!

    New to VBA. I want to export other fields straight forward but want to extract data only when the Subject field is either “Quote Status – Open”, “Quote Status – On Hold”, “Quote Status – Closed”.

    And the body of the email.

    Finally (and apologies) when I extract this data from Outlook (to a specified worksheet) is there a way to start on the next blank row when I run it again?

    Many thanks if you can help

    Reply
  9. Max
    Max on 2022-10-10 at 10:38 AM

    Hi Jie,

    Firstly I found you code really useful, thank you so much!

    Although is there a way we can add another code for columns to list down the attachments of the specific mailbox item?

    I tried using this but it gives a blank cell

    xlWB.Worksheets(1).Cells(i + 1, “D”).Value = olItems(i).Attachments

    Thanks!!

    Reply

Submit a Comment

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