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.
👉 Find out more about our Advanced Formulas course!
how would I add additional conditions such as “to sender” and “subject?
You would need to change the line of code:
If OutlookMail.ReceivedTime >= Range("From_date").Value ThenTo something like this:
If (OutlookMail.ReceivedTime >= Range("From_date").Value and OutlookMail.Subject = Range("Subject_condition").Value and OutlookMail.SenderName = Range("Sender_condition").Value) ThenYou 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.
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.
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.
i have to import data from inbox so unable to retrieve due to this error “Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders(“Inbox”).Folders(“inbox”)”
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)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!
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 ThenSet 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
John,
That worked beautifully! And your code worked great as well!
Thank you for your help!
No problem!
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?
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")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!
Great stuff. Thanks!
How would we get Sender’s EMail ID as well?
I’m not sure I know what an email ID is, but since email addresses are unique identifiers you can use that!
Hi This is really useful, can I get the reply time as well
Since emails don’t necessarily have a reply, you could look in your sent folder for
OutlookMail.SentOn.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
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?
I was able to make adjustments to the code and have successfully pulled emails into Excel. Thank you all for sharing!
Good to hear you figured it out. Maybe you could share the solution to help anyone else having the same problem?
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
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
The code uses a named range in the sheet to output data to and it might be that you deleted the named range?
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.
How I can assign my outlook pst file name in this code
Unfortunately, I’m not familiar with pst files. Try asking on a forum. Let me know what you find!
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
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.
i downloaded again and did not change any but still have the same error.
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?
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.
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.
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.
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")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.
Unfortunately, I don’t know off hand and don’t have outlook anymore to test anything out.
is it possible to import from specific sender
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 ThenWith this line:
If OutlookMail.ReceivedTime >= Range("From_date").Value and OutlookMail.SenderName = "Person's Name Goes Here" ThenSub 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
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.
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
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
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.
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?
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?
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
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
Thanks Mus!
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
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.
Unfortunately, I don’t know off hand and don’t have outlook anymore to test anything out.
Dim OutlookApp As Outlook.Application
“User defined type not defined” ?
Did you enable the Microsoft Outlook 16.0 Object Library?
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
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.
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?
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.
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.
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
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
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
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
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
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?
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
Just sort the list!
Hi John I am getting error on line
If OutlookMail.ReceivedTime >= Range(“From_date”).Value Then runtime error ‘range’ of object’_global’ failed