Export Emails From Outlook to Excel with Date and Time
To Export Outlook email data to Excel & archive mails, we have 3 options here.
- Menu option for Outlook to Excel
- Outlook VBA: Export emails from Outlook to Excel VBA with date and time.
- Archive Outlook Emails
Lets start to learn all possible methods, one by one.
1. How to Export Outlook Emails To Excel?
To extract data from Outlook to Excel:
- Click File menu or Office Ribbon in Outlook.
- Select “Import and Export”.
- Choose “Export to a file” & press Enter.
- Select “Microsoft Excel 97-2003” & Enter.
- Choose ‘Outlook Folder to Export’ & click next.
- Type Output Excel File path to export Email data.
- Map Outlook field headers with output Excel column headers [optional].
- Click Finish.
All the email items from the chosen PST folder will be downloaded to Excel.
It will have email fields like sender, email body, date and time etc.,
More Advanced Options are here… Download Outlook Mail Attachments to local Folder
2. Outlook VBA: Copy Email Fields to Excel with Date and Time
Here is the VBA code to extract emails from Outlook to Excel.
Download & Try this Excel before using the Code Downloaded 6,366 Times
Before executing the code, make a note of these points.
- Go to Tools -> References & enable “Microsoft Outlook nn.n Object Library. (nn.n is version of Object Library that varies depending on your Office Installation)
- Change “Mail box Name” & “Folder Name” from where you need to extract the email data.
- Outlook application should be running in your machine.
Note: If you are executing this code in Outlook VBA, then above reference is not required.
Export Outlook emails to Excel with date and time
Export Email from Outlook to Excel VBA CodeVisual Basic1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071Option Explicit'This Code is Downloaded from OfficeTricks.com'Visit this site for more such Free CodeSub VBA_Export_Outlook_Emails_To_Excel()'Add Tools->References->"Microsoft Outlook nn.n Object Library"'nn.n varies as per our Outlook InstallationDim Folder As Outlook.MAPIFolderDim sFolders As Outlook.MAPIFolderDim iRow As Integer, oRow As IntegerDim MailBoxName As String, Pst_Folder_Name As String'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)MailBoxName = "MailBox Name"'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)Pst_Folder_Name = "Folder Name" 'Sample "Inbox" or "Sent Items"'To directly a Folder at a high level'Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)'To access a main folder or a subfolder (level-1)For Each Folder In Outlook.Session.Folders(MailBoxName).FoldersIf VBA.UCase(Folder.Name) = VBA.UCase(Pst_Folder_Name) Then GoTo Label_Folder_FoundFor Each sFolders In Folder.FoldersIf VBA.UCase(sFolders.Name) = VBA.UCase(Pst_Folder_Name) ThenSet Folder = sFoldersGoTo Label_Folder_FoundEnd IfNext sFoldersNext FolderLabel_Folder_Found:If Folder.Name = "" ThenMsgBox "Invalid Data in Input"GoTo End_Lbl1:End If'Read Through each Mail and export the details to Excel for Email ArchivalThisWorkbook.Sheets(1).ActivateFolder.Items.Sort "Received"'Insert Column HeadersThisWorkbook.Sheets(1).Cells(1, 1) = "Sender"ThisWorkbook.Sheets(1).Cells(1, 2) = "Subject"ThisWorkbook.Sheets(1).Cells(1, 3) = "Date"ThisWorkbook.Sheets(1).Cells(1, 4) = "Size"ThisWorkbook.Sheets(1).Cells(1, 5) = "EmailID"'ThisWorkbook.Sheets(1).Cells(1, 6) = "Body"'Export eMail Data from PST Folder to Excel with date and timeoRow = 1For iRow = 1 To Folder.Items.Count'If condition to import mails received in last 60 days'To import all emails, comment or remove this IF conditionIf VBA.DateValue(VBA.Now) - VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60 ThenoRow = oRow + 1ThisWorkbook.Sheets(1).Cells(oRow, 1).SelectThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).SenderNameThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).SubjectThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).ReceivedTimeThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).SizeThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress'ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).BodyEnd IfNext iRowMsgBox "Outlook Mails Extracted to Excel"Set Folder = NothingSet sFolders = NothingEnd_Lbl1:End Sub
This is how we can create an alternate to traditional archive Outlook emails built in option and maintain our own readable Email repository.
To execute this code, press F5 and system will extract data from Outlook to Excel from the mentioned mailbox and folder.
Note: The limitations of this Export Outlook to Excel code are:
- Double & Single quotes in below code might change to improper symbols while copying. Check it if you get any execution or compilation error.
- If there are any meeting invites or items other than a Email, then the above code will throw error. Remove the mail items that this code cannot read and re-execute the code.
- This VBA does not extract complete MIME details of email, as how it is stored in Outlook archive folder. This method only stores the mail content in Excel just for future reference.
- Attachments from emails are not skipped. It will export only text content. For more information on downloading attachment to a system folder, you can get to know by reading this post “Outlook Attachments”.
3. How to Archive email in Outlook? – On Demand
To archive Outlook email to a PST folder, follow these steps.
- In File Menu, click “Archive” (shortcut: Alt + f + r).
- Choose Outlook mail folder to be archived.
- Choose date for ‘Archive items older than’.
- Enter path for archive PST file & click Ok.
Outlook will start to move all the emails matching the date criteria to your archive pst file. Archiving old emails will improve the performance of Outlook considerably. Once your Outlook takes more time to load or search emails, then try to run a archival process on that specific email folder.
There are 2 options available to archive Outlook emails – Outlook Auto Archive & On demand.we are going to see the automatic archival settings next.
Also Read: VBA Option Explicit Not Working - Solved
4. How to Schedule Outlook Auto Archive?
To schedule the process of archiving Outlook emails at regular time interval, follow these steps.
- In Tools Menu, click “Options” (shortcut: Alt + t + o).
- Choose ‘Others’ tab & click “AutoArchive”.
- Enter Outlook Emails Autoarchive settings
- Time Frequency.
- Eligibility Date criteria for cleaning up older emails.
- Output PST Folder path.
- Click Ok when all settings are entered.
By using this method, you can make the Outlook to run archival process after office hours.
Usually, your PC slows down if you have large number of older emails in your Inbox. So, try to schedule a archival after office hours.
Misc about Export Outlook Emails To Excel or Email Archiving
In this page, we learned multiple ways to read all the Emails from a specific Outlook Folder and write the details like Email Subject, Sender Name, Received Time, Body etc., to Excel.
People do lot of search for the code in this article, but still some might get confused about what is the actual need to export Outlook emails to Excel.
Transferring data from one MS Office application to another, but why?
With Excel, after exporting they can apply options like Filter, Vlookup, Pivot Chart, and Pivot Table.
Anyways Microsoft has provided option through Outlook VBA to read email content and export the mail body, contacts, addresses, Calendar and Tasks to Excel or Text file. Outlook also has option for VBA, but it is fun to integrate it with Excel VBA.
Also Read: VBA to Delete File in a Folder
Export Outlook Emails To Excel – VBA Code – 5,700+ Free Downloads
- Games VCF Countif Record Macro Timer in Excel Task Manager print screen HTML Table Import Data Thumbnails Text to Speech TTS Candy Crush Block Apps in FaceBook C++ Programing Machine learning AI bulk email Excel VBA vba color index vba color codes Android social media Twitter Google+ Google Adsense Tips bitcoin Calendar in Excel outlook vba mass email Data Mining Excel Macro Facebook WhatsApp python Wordpress