I had a need to export all the emails’ meta data like From, To, Subject, Receive Date from Outlook. Quickly searching on the internet, I found the script at http://superuser.com/questions/816289/exporting-attachment-file-name-email-metadata-from-outlook-to-excel so I quickly created a spreadsheet and exported all the needed information.
I have made following modification to the code
- Commented out exporting email body data as I don’t need it (if you want email body, follow the steps given at the bottom)
- Error thrown when setting up the auto filter
Other than that code worked fine for my need.
Here are the steps
Download Macro Enabled Excel Spreadsheet
If you see the security warning, click on “Enable Macro” button for this spreadsheet to work
You will see the spreadsheet with two tabs. The first tab contains a button. Second tab contains the result.
Click on the “Get Outlook Data” button
It will prompt the user to select the Outlook folder/subfolder from which you want to export the data.
Select the appropriate folder and click “OK”.
Once completed, it will show the message “Completed” and you will get all the data that you need in the “Outlook Results” tab
How to get Email body?
Based on Richard’s comment, I thought it is a good idea to show how to get Email body. Here are the steps.
If the Developer tab is not available in Excel, go to File > Options > Customize Ribbon > Developer > Check it and click OK button
Now go to Developer tab > Visual Basic
On left hand side, double click on “Module1” and on the right hand side, search for the code which is highlighted in the image below
Remove the single quote character that is in the beginning of the line.
Close this main window
Save the Excel spreadsheet
Close the spreadsheet
Open the spreadsheet again and now you can extract first 900 characters of Email body. If you want more, change this limit within the code in the same line.
Hi Vishal – Thanks for this mailbox data extraction macro. I hve around 10000+emails in my inbox, is there a possiblity to put up a date range on this macro so that I can extract data for a date range as per my need?
Thanks in advance for your help!
Amit
Hi Amit,
You may use msg.SentOn property to filter the data based on the date range.
Hi Vishal,
Gosh, you are talented.. Thank you for the code. it works just fine for my needs however, you mentioned that you commented out the code for the body information. I actually need the body information. is there a way to have that come into the spreadsheet as well.
Thank you again, Vishal.
Rich Latimer – Investments Unit American Red Cross
Hi Richard,
I have updated the post to reflect the steps to uncomment the source code and extract email body.
Thank you
Hi Vishal,
Great Code . Thanks for the info.
I had a question for you, and a check if it is feasible or not – can we get the outlook property from Outlook to Excel using VBA. Specifically, I need the “City” of the sender which I can see on the Lync under ” Outlook Properties” to be exported to excel as one of the column Header.
Thanks again.
Hi Vishal,
I haven’t confirmed but I assume that the Lync information comes from Active Directory. Once you have From address, you may query the Active Directory to find the information. Alternatively, you may also search for that contact within Outlook Contact (locally stored within Outlook and not Active Directory) and find the needed information.
Regards,
Vishal Monpara
Hi Vishal,
Thank you so much for this information. I would like to know if it is possible to have the same items list but for more than one subfolder.
My folders structure have 3 levels, and going 1 by one is a waste of time, if you could help me, I would be appreciated.
Best regards,
Quim
Hi Quim,
You need to modify the code and hard code the values of folders. Use “MsgBox objNamespace.PickFolder, vbOKOnly” to show you the folder name and use that name for hardcoding.
Regards,
Vishal Monpara
Hi Vishal,
It was working fine a few minutes back but just now ,i don’t know what happened,
I am getting Run Time error 91, (Object Variable or with Block Variable not Set) in
with msg
tempString(i + startRow, 1) = .BCC
Please help
Hello Deepak,
This is a generic error from Excel and it is difficult to find out the root cause without looking at the current state of the file. You may want to start from the scratch to see if it fixes the issue.
Thank you
Vishal Monpara
Thanks for your reply Vishal.
Actually, whenever there are more than 100 emails it is giving this error but for lesser number it is working fine….
Sometimes it’s working fine on numerous emails as well but sometimes it is giving error on even 1 mail.
Could you please advise why is it so.
Hello Deepak,
I had a success getting thousands of emails in a single run so unless I can replicate the issue on my end, it is difficult to find the root cause.
Regards,
Vishal Monpara
Hi Vishal,
I want to filter the emails of specific data range received between two dates and time and move it to outlook folder from specific email address. is that possible to do it on the above code.
Thanks
Jay
Hello Jayakumar,
Whatever you mentioned is possible if you know VBA programming. Instead, I would suggest to use Outlook Rules as it is quick and easy to create/use/update as per your need. Here is a link on how to perform this task. Move Outlook emails of specific date range to a folder
Regards,
Vishal Monpara
Hi Vishal,
Thank you for sharing your code. This will save me a great deal of time for my use case.
I do have one question regarding the format in which the date is returned. Do you know of a way to convert the format to an acceptable format for excel to read within the macro? Currently the only solution is to convert it manually via a formula of = (cell with date information) * 1.
Regards,
Alex
Hi Alex,
The code converts the date into string. The other alternative is to convert it within the VBA code.
Regards,
Vishal Monpara
Hi Vishal
I usually just skim through websites like these, but this excel file and macro was literally the most perfect piece of code for my situation, and it is very much appreciated.
Keep up the good work!
Kind regards
Luke
Hi Vishal,
Thanks for sharing this code. I need a clarification from you, can we extract complete body of the email? FYI. the current code is extracting up to 32,767 characters.
Requesting your help on this.
Regards,
M Janarthanan
Hello Janarthanan,
I have not explored the option to extract the full body. You may want to explore the VBA code documentation for full body extract.
Regards,
Vishal Monpara
Hi Vishal,
You’re a genius! This works really great and I think my department will really benefit from this! I wanted to check with you on how would I be able to only import the emails from the folder I selected from a date that I specify? i wanted to place a Date Field beside the Get Outlook Data button. Is this possible?
Looking forward to your reply, thank you and my dearest regards,
Von Rosales
Hi Von,
Yes it is possible. If you or your team can code in VBA, you can create a separate form to get date range and use that date range to filter the messages.
Regards,
Vishal Monpara
Hi,
How would i be able to adapt the code so that new emails will just add to the excel file not replace it?
Thanks
Hello Michael,
You may use the following logic to make it work.
1. You need to get the email only for a certain date range. Date range must not overlap when you run the program on multiple days. This ensures that you are getting only those emails which has never been fetched previously.
2. Before you start fetching the email, make the start position as the last cell of Excel content
Regards
Vishal Monpara
I have downloaded your code and works fine, however Is there any way to substitue : Set strFolderName = objNamespace.PickFolder, by some sentences?
The point is that I need to check the names of the attachments files from a vba macro, without picking the folder.
I have been exploring the web and I am not able to find out a solution.
Thank uoy very much in advance.
Regards
Hello Jose,
You can always hardcode the value of “strFolderName” in that line so it will not show the window to pick the folder name.
Regards,
Vishal Monpara
Hi Vishal Monpara,
Thanks, it works!
What code do I put to get a specific line in the body?
Hello ES,
The code already exists in Excel but it is commented so it does not have any effect. Follow the steps given under “How to get Email body?” within this same blog post to get the body text from outlook.
Thank you
Vishal Monpara
Hi Vishal,
Nice tool, is there a way the tool includes directly all subfolders as well?
Hi Peter,
You may use objNamespace.Folders property to loop through all available folders. You may find the documentation at https://docs.microsoft.com/en-us/office/vba/api/outlook.namespace.folders
Regards,
Vishal Monpara
Thank you Vishal, it works beautifully 🙂
Dear Sir
I am new in VBA. I have tried the template to save all the emails to excel 2007 but it does not work properly and not saving all the emails and throwing error. Though some emails are being saved but not all.
Run-Time error 1004
Application defined or object defined error
Please help any one. I will be grateful to you.
Hello Atindra,
This error is generic and does not provide exact information of what went wrong. It is not possible to determine the root cause and fix it without having access to the same environment that you are using.
Regards,
Vishal Monpara
Vishal,
thanks so much for this!
Would it be possible to alter this script to pull the metadata from a folder somewhere on one’s hard drive, as opposed to an Outlook folder?
Example:
e:\projects\emailtobesorted
Hello John,
Unfortunately I don’t know if this script would work (with little modification) or not when you have email files available on a folder.
Regards
Vishal Monpara
Is it possible to filter results by the sender’s email address or name?
Hello Pamela,
If you know VBA coding, you should be able to make a change to filter for a given email address. If you don’t know VBA coding, export all emails and filter the needed emails using Excel filters.
Regards
Vishal Monpara
how to get the email attachements date wise …
Hello Chandru,
You will need to make the change in the code as per your need. You will be able to save the attachment using https://docs.microsoft.com/en-us/office/vba/api/outlook.attachment.saveasfile function.
Regards,
Vishal Monpara
Thank you so much this code saved me 2 weeks (or more) of work!!!
Really grateful for this advice.
Any tips/ideas how i can
export Emails from Outlook as .msg in automatically generated folders (for each day one folder subsequent to folders per month subsequent to folders per year)?
Greetings from Germany
Hello Jan,
“msg” in this code is of object MailItem. You may use “SaveAs” method for this object to save the item on your disk as per your need. Documentation for this method is available at https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.saveas
Regards
Vishal Monpara
Ok…
Great stuff, problem:
When I use the macro on my Inbox it works just fine, but when I use it on any other folder (subsequent or next to my Inbox) I get an error.
“Run-time error ’91’:
Object variable or With block variable not set
So I am a VBA newbie, but I tried to find the error by running through the code step-by-step.
To me it looks like, that, if I select a different folder than my Inbox, the “folderItem” will not receive any value in the row:
Set folderItem = mailFolderItems.Item(i)
leading to the “msg” not receiving a value (ergo it is “nothing”) which then leads to the error poppong up as soon as I pass the
With msg
line.
Any idea what causes this?
Hi Jan,
There is an issue with the code. Once I get time, I will fix it and test it. Main culprit is “If IsMail(folderItem) Then”. It requires Else condition.
Regards,
Vishal Monpara
How to get Recipient Email Address?
Hello Asad,
“To” represents recipient and “From” represents sender.
Regards,
Vishal Monpara
Hi Mr. Monpara
Thank you so much for this topic
that was so helpful for me
you are a great programmer
Thanks a ton, this is exactly what I was looking for.
Can you please help me to filter on Subject=”AAA”.
And the date range from & to
What I am looking is to import only those mails with the subject = AAA, which the date range from 1st June to 30th June 2019, .
Now my folder has loads of mail, so its taking a lot of time for the macro to fetch the data.
Hello Sri,
You may make change in the macro to get emails for the chosen date range. This way, you will get emails from a specific date loaded in Excel. You may also add additional condition for Subject based on your need within macro.
Regards,
Vishal Monpara
This is AMAZING – I’ve searched for a very long time trying to find something along these lines and have had a ton of personal failures. Thank you!
One thing I’ve noticed is that the most recent message pulled from any folder is about a week old. I’m unsure of how to manipulate the code (aside from sorting the data pulled after the fact) to prioritize the most recent emails. Any tips?
Disregard my question – this works perfectly!
Sir thank you for the script
how to change the date range pls help
I can’t seem to get this to work on a mac. Should it?
Hello Joey,
This solution works only in Windows.
Regards
Vishal Monpara
Hi Vishal, do you know if it’s possible to modify the script to get the data about e-mail signature only, instead of the whole body – like who signed a sent e-mail?
Hi Ela,
I don’t know any way to extract/parse the text from Email body to get sender’s signature.
Regards,
Vishal Monpara
Hey Vishal,
Is there a way to have the e-mails be organized in chronological order by the send date/time?
Hi Ivan,
Once you get data in Excel, you can sort it by SentOn column. I don’t know any way to get data from Outlook in a chronological order.
Regards,
Vishal Monpara
hi Vishal, How do I know if the email has been responded? is there any code to add the Replied date as well?
Hello Yuvapriya,
There is no out of the box property but quick search revealed that there are extended properties available. It requires a additional programming.
Regards,
Vishal Monpara
Hello Vishal ,
Can i export the particular data from the email body using VBA into excel…???
is there any specific Statements be used to export the specific data from email body onto the excel sheet ….?????
please HELP!
Hello Karthik,
Once you get everything exported, you may use Excel filters to get what you need. If your situation requires complex scenario that cannot be handled using Excel, you need to parse body text within VBA code
Regards,
Vishal Monpara
Thanks Vishal. This was great!
-Himansu
Hi Vishal Monpara
Thanks for sharing the great Idea!
Could you help me to add one column for Start date ?
Regards,
Dharma
Hello Dharmendran,
This post also contains details on how to make a change in this spreadsheet. Follow the steps to make change as per your need.
Regards
Vishal Monpara
It really make sense for me.
Hi,
Can you tell me how to have the script pull emails with a keyword in the subject line? I want to run this daily, and the subject may contain a date such as 12232019 and I only want to import those emails.
Hello Wigath,
You need to add “if” condition within code to filter out only needed information. If you don’t have programming background, the easiest way is to extract everything and then use Excel Filter get only needed email.
Regards
Vishal Monpara
Hi, Vishal it works great. Is it is possible to download the data via date range?
Hello Sagar,
You may change code as per your need.
Regards,
Vishal Monpara
this way is not an autamatic way correct? thats mean, do I have to repeat this steps after I got each new email? or for any new email I will got it will automatically updated in the excell sheet?
Hello Moza,
You need to manually run this everytime you want to get an email in Excel sheet.
Regards,
Vishal Monpara
Leave a comment