Outlook Email Download to Excel
How to Export Emails from Outlook to Excel Through VBA?
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?
Export mail from Outlook and Import it to Excel. 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 Basic1234567891011121314151617181920212223242526272829303132333435Sub 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'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'Rad Through each Mail and export the details to ExcelSheets(1).ActivateFolder.Items.Sort "Received"For iRow = 1 To Folder.Items.CountSheets(1).Cells(iRow, 1).SelectSheets(1).Cells(iRow, 1) = Folder.Items.Item(iRow).SenderNameSheets(1).Cells(iRow, 2) = Folder.Items.Item(iRow).SubjectSheets(1).Cells(iRow, 3) = Folder.Items.Item(iRow).ReceivedTimeSheets(1).Cells(iRow, 4) = Folder.Items.Item(iRow).Size'Sheets(1).Cells(iRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress'Sheets(1).Cells(iRow, 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 uncomment 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 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”.
You may also like these Posts