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
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!
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
When running the macro I have, unfortunately, the error User-defined type not defined.what can be done with this?
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
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.
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?
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 😦
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
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?
Did you ever figure this out? I am having the same problem.
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?
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!
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
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!!