Archive Outlook Emails
How to Export Emails from Outlook to Excel Through VBA?
This article explains about downloading & archiving email content from Outlook through Excel Macro code. People do lot of search for this code, but still some might get confused about what is the actual need to import the Outlook mail details to Excel. Transferring data from one MS Office Application to Another, but why? !!!
Excel is Easy for Data Processing
If it is just about reading and replying to mails, Outlook would be the preferred application.
But 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.
Anyways Microsoft has provided option through VBA to read Outlook content and export its Mail, Contacts, Calendar and Tasks to Excel or Text file.
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.
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 mail from Outlook and Import it to Excel. This way, we keep a copy of mail for our reference. In this code, we are going 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.
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.
Visual Basic12345678910111213141516171819202122232425262728293031323334353637383940414243444546Sub 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 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"Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)If Folder = "" 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"'Insert Mail DataFor iRow = 1 To Folder.Items.CountoRow = iRow + 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).SenderEmailAddressThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).BodyNext iRowMsgBox "Outlook Mails Extracted to Excel"end_lbl1:End Sub
- Create a new workbook and save it as “Excel Macro Enabled Workbook”.
- Press Alt + F11 to get to the VB Editor for Excel.
- Copy Paste the below code.
- Before executing the code, make these changes.
- 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.
- 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 un-comment the last line inside the loop.
- 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 is how we can create an alternate to traditional archive Outlook emails built in option and maintain our own readable Email repository.
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 way 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.
[Total: 1 Average: 5/5]Outlook Email Download to Excel written by Moderator average rating 5/5 - 1 user ratings
You may also like these Posts
<<<----VCF to Excel – Android Contact Manager Extract Web page with Tables – Best Data Mining Tool---->>>
Outlook Email Download to Excel
Join Our Free Subscription
You are just one-step away to receive our Latest Blog Updates in Your Inbox !!!
Top5 Trending Posts
- Record Macro Folder Size Run Macro Command Button Add Button Folder List Personalized Email Calendar1.value Simple Excel Macro Email Customized Email Excel Timer Scheduler Worksheetfunction Appointment Formula in Macro Formula through Macro Excel Formula in Macro Outlook Calendar Macro to refer worksheet Refer Excel Sheet Refer Worksheet Refer Cell Refer worksheet from VBA Calendar Control working with Calendar Formula Excel Duplicate Countif Calendar in Excel Date Control Calendar Twitter WhatsApp Google+ Excel Macro Candy Crush Timer in Excel Hello World Email blast QR Code Generation outlook archive email mass email auto archive outlook VCARD Google Adsense Tips Facebook VCF Wordpress