How to Batch Export & Insert Multiple Outlook Email Objects into an Excel Worksheet

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.

Batch Export & Insert Multiple Outlook Email Objects into an Excel Worksheet

Export & Insert Multiple Outlook Email Objects into an Excel Worksheet

  1. For a start, launch Outlook VBA editor via “Alt + F11”.
  2. Then, add “Microsoft Excel Object Library” with reference to “How to Add an Object Library Reference in VBA“.
  3. 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

VBA Code - Export & Insert Multiple Outlook Email Objects into an Excel Worksheet

  1. Afterwards, add this macro to Quick Access Toolbar.
  2. Eventually, follow the steps below to take a shot.
  • At the outset, select multiple emails.
  • Then, run the macro through Quick Access Toolbar.Trigger Macro
  • Immediately, after macro finishes, you will see an Excel worksheet, like the following screenshot.Inserted Email Objects in Excel Worksheet

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.

Author Introduction:

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

Comments are closed.