As we all know, it is very easy to export email list to an Excel worksheet. How about exporting and inserting Outlook email objects into an Excel worksheet? If you need to do that, you can use the intelligent method introduced in this article.
Outlook offers a native function that supports you to export email list to an Excel file. However, at times, maybe you want to directly export the entire email objects to Excel. In this way, you can open Outlook emails straightly from Excel file. Here we will teach you how to achieve it with Outlook VBA.
Export & Insert Multiple Outlook Email Objects into an Excel Worksheet
- For a start, launch Outlook VBA editor via “Alt + F11”.
- Then, add “Microsoft Excel Object Library” with reference to “How to Add an Object Library Reference in VBA“.
- Next, in the new screen, copy the following VBA code into an unused module.
Sub ExportEmailObjectsIntoExcelWorksheet() Dim objSelection As Outlook.Selection Dim strTempFolder As String Dim objMail As Outlook.MailItem Dim strFilePath As String Dim objExcelApp As Excel.Application Dim objWorkbook As Excel.Workbook Dim objWorksheet As Excel.Worksheet Dim objFileSystem As Object Dim objFiles As Object Dim objFile As Object Dim nRow As Integer Dim strMailObjectFile As String 'Get all selected emails Set objSelection = Outlook.Application.ActiveExplorer.Selection If Not objSelection Is Nothing Then strTempFolder = CStr(Environ("USERPROFILE")) & "\AppData\Local\Temp\EmailObjects" & Format(Now, "yyyymmddhhmmss") & "\" MkDir (strTempFolder) 'Save the emails in .msg format For Each objMail In objSelection strFilePath = strTempFolder & objMail.Subject & ".msg" objMail.SaveAs strFilePath, olMSG Next 'Create a new Excel file Set objExcelApp = CreateObject("Excel.Application") Set objWorkbook = objExcelApp.Workbooks.Add Set objWorksheet = objWorkbook.Sheets(1) objExcelApp.Visible = True Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objFiles = objFileSystem.GetFolder(strTempFolder).Files nRow = 0 For Each objFile In objFiles nRow = nRow + 1 'Insert the emails' subjects and objects strMailObjectFile = strTempFolder & Trim(objFile.Name) If strMailObjectFile <> "" Then objWorksheet.Activate objWorksheet.Range("A" & nRow) = Left(objFile.Name, (Len(objFile.Name) - 4)) objWorksheet.Columns("A").AutoFit objWorksheet.Columns("A").RowHeight = 42 objWorksheet.Range("B" & nRow).Select objWorksheet.OLEObjects.Add(FileName:=strMailObjectFile, Link:=False, DisplayAsIcon:=False).Select End If Next End If End Sub
- Afterwards, add this macro to Quick Access Toolbar.
- Eventually, follow the steps below to take a shot.
- At the outset, select multiple emails.
- Then, run the macro through Quick Access Toolbar.
- Immediately, after macro finishes, you will see an Excel worksheet, like the following screenshot.
Tackle Troublesome Outlook Issues
It’s inevitable that you will encounter a variety of issues in any applications. The same holds true for Outlook application as well. For instance, assuming that you frequently exit Outlook improperly, Outlook file tends to become corrupt. At that point, you have to attempt annoying Outlook recovery, such as using DataNumen Outlook Repair to repair PST file. In a nutshell, if you’re reluctant to suffering this, you had better handle your Outlook file with care and make regular backups.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql fix and outlook repair software products. For more information visit www.datanumen.com