Archive Outlook Emails
This article explains how to export email content from Outlook to Excel using VBA Macro code. People do lot of search for this code, 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? If it is just about reading and replying to mails, Outlook would be the preferred application.
Excel is Easy for Data Processing
If you need to do lot of processing with Mail data and automate responding to user through VBA, Users prefer Excel. Outlook also has option for VBA, but it is fun to integrate it with Excel VBA.
Users prefer Excel for Outlook because; they 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.
How to Export Emails from Outlook to Excel Through VBA?
Since it is a complete extraction of all the needed mail content, the topic is named as “Outlook Mail Download”. Let’s see the actual VBA code. 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”. Press Alt + F11 to get to the VB Editor for Excel and Copy Paste the below code. Before executing the code, make these changes.
Visual Basic1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071Option Explicit'This Code is Downloaded from OfficeTricks.com'Visit this site for more such Free CodeSub Download_Outlook_Mail_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 FolderoRow = 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
- Go to Tools -> References and enable check box at “Microsoft Outlook nn.n Object Library (nn.n is version of Object Library that varies depending on your Office Installation)
- Change the code to include the Mail box Name and Folder Name from where you need to Read and Export the mails.
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. All the mails from the mentioned Mailbox and folder will be downloaded to Excel. To export the mail message/body, remove comment from the last line inside the loop.
Note: 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 code does not download Attachments from Mails. 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”.
This process does not store mails as such, in the usual outlook archive folder, as a proof for the Emails. This method only stores the mail content in excel just for future reference or processing the content.
Do we call it Export or Import? or Simply Email Archiving
With this code, 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.
Recommended For You
You may also like these Posts
Export Outlook Emails to Excel