How to use VBA for Outlook to Excel data transfer?
To export Outlook emails to Excel & archive mails, choose any of the below methods listed.
- Extract data from Outlook to Excel using Menu option .
- Excel Macro Export emails from Outlook to Excel VBA with date and time.
- Archive Outlook Emails:
- Outlook Archive on demand.
- Outlook Autoarchive emails.
Lets start to learn all possible methods to export emails from Outlook to Excel, starting with the built-in option available right from Outlook menu.
1. How to Export Outlook emails to Excel?
To export Outlook email data to Excel, follow these instructions.
- Click File menu or Office Ribbon in Outlook application.
- Select “Import and Export” (shortcut: Alt + f + t).
- Choose “Export to a file” & press Enter or Next.
- Select “Microsoft Excel 97-2003” & press Enter.
- Choose ‘Outlook folder to Export’ & next.
- Enter File path to export Email data.
- Map Outlook field headers with output Excel column headers [optional].
- Click Finish.
This completes the export emails from Outlook to Excel process.
Open the output file to see the email data in Excel format. All the email items from the PST folder that you have chosen will be downloaded to Excel. People are more comfortable with Excel for data processing rather than doing it in Outlook itself. That why We export email from Outlook to Excel to do lot of data processing with email content in Excel.
With this method, we can export only email data to Excel & not the attachments. In case if you want to process attachments then have a look at this page: Download Outlook Mail Attachments to local Folder
2. How to Export Emails from Outlook to Excel Through VBA?
Here is the Outlook VBA to copy email body to Excel along with sender, receiver, date & time details, excluding attachments.
It is assumed that user know how to write basic commands in VB Editor and execute it. Create a new workbook and save it as “Excel Macro Enabled Workbook” (.xlsm). Press Alt + F11 to get to the VB Editor for Excel and Copy Paste the below code.
Before executing the code, make these changes.
- 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 in execution status.
Export Outlook emails to Excel with date and time
Export Outlook Emails to Excel with Date and TimeVisual 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. This Outlook vba copy email body to excel also if we remove the commented codes from the last line inside the loop.
Note: The limitations of this 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 Excel VBA Export Outlook Emails or Simply Email Archiving
With the vba code in this page, we are not going to store mails in a PST file, but instead, Export emails from Outlook to Excel. This way, we keep a copy of mail for our reference. In this code, we learned 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? Outlook would be the preferred application, If it is just about reading and replying to mails. Users prefer Excel for Outlook because; we are more familiar in processing data with Excel rather than other office applications. With Excel, after exporting they can apply options like Filter, Vlookup, Pivot Chart, and Pivot Table.
Anyways Microsoft has provided option through VBA to read Outlook content and export its Mail, Contacts, Calendar and Tasks to Excel or Text file & automate responding to user through VBA. Outlook also has option for VBA, but it is fun to integrate it with Excel VBA.
Export Outlook Emails To Excel – With Date and Time – Using VBA