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 8,058 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
Option Explicit 'This Code is Downloaded from OfficeTricks.com 'Visit this site for more such Free Code Sub VBA_Export_Outlook_Emails_To_Excel() 'Add Tools->References->"Microsoft Outlook nn.n Object Library" 'nn.n varies as per our Outlook Installation Dim Folder As Outlook.MAPIFolder Dim sFolders As Outlook.MAPIFolder Dim iRow As Integer, oRow As Integer Dim 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).Folders If VBA.UCase(Folder.Name) = VBA.UCase(Pst_Folder_Name) Then GoTo Label_Folder_Found For Each sFolders In Folder.Folders If VBA.UCase(sFolders.Name) = VBA.UCase(Pst_Folder_Name) Then Set Folder = sFolders GoTo Label_Folder_Found End If Next sFolders Next Folder Label_Folder_Found: If Folder.Name = "" Then MsgBox "Invalid Data in Input" GoTo End_Lbl1: End If 'Read Through each Mail and export the details to Excel for Email Archival ThisWorkbook.Sheets(1).Activate Folder.Items.Sort "Received" 'Insert Column Headers ThisWorkbook.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 time oRow = 1 For 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 condition If VBA.DateValue(VBA.Now) - VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60 Then oRow = oRow + 1 ThisWorkbook.Sheets(1).Cells(oRow, 1).Select ThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).SenderName ThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).Subject ThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).ReceivedTime ThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).Size ThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress 'ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).Body End If Next iRow MsgBox "Outlook Mails Extracted to Excel" Set Folder = Nothing Set sFolders = Nothing End_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.
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.