How to Quickly Merge & Export Multiple Excel Workbook Attachments into One Workbook in Outlook

When you receive an email attached with multiple Excel workbooks, if you want to check the contents of all the workbooks in a single workbook, you can use the way in this article. It’ll export multiple Excel workbook attachments to one workbook.

To view all the Excel workbook attachments of an email in one Excel workbook, you should firstly combine and export these attachments into a single workbook. Instead of manually doing that, you can use the following method to achieve that with effortless ease. It’ll teach you to use VBA code to accomplish that. As for how to use VBA, you can refer to the post “How to Run VBA Code in Your Outlook”.

Merge & Export Multiple Excel Workbook Attachments into One Workbook

1. For a start, launch your Outlook application.
2. Then, access Outlook VBA editor.
3. Next, in the new “Microsoft Visual Basic for Applications” window, you need to enable “MS Excel Object Library”, about which you can refer to the article – “How to Add an Object Library Reference in VBA”.
4. Subsequently, copy the following VBA code into a project or module.

Sub CombineMultipleExcelWorkbookAttachmensIntoOne()
    Dim objSelectedAttachments As Outlook.AttachmentSelection
    Dim objAttachment As Outlook.Attachment
    Dim strTempFolder As String
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim strFile As String
    Dim objCurrentSheet As Excel.Worksheet
    Dim objCurrentBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim objFileSystem As Object
 
    On Error Resume Next
    'Create a new temp folder
    strTempFolder = "E:\Temp" & Format(Now, "yyymmddhhmmss") & "\"
    MkDir (strTempFolder)
 
    'Save selected attachments
    Set objSelectedAttachments = Outlook.Application.ActiveExplorer.AttachmentSelection
    For Each objAttachment In objSelectedAttachments
        objAttachment.SaveAsFile (strTempFolder & objAttachment.FileName)
    Next
 
    'Copy all the workbooks into a new file
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Visible = True
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
    objExcelWorkbook.Activate
 
    strFile = Dir(strTempFolder)
    Do While strFile <> ""
       Set objCurrentBook = objExcelApp.Workbooks.Open(strTempFolder & strFile)
       For Each objCurrentSheet In objCurrentBook.Sheets
           objCurrentSheet.Copy Before:=objExcelWorkbook.Sheets(1)
       Next
 
       objExcelApp.Workbooks(strFile).Close
       strFile = Dir()
    Loop
 
    'Remove empty sheets
    objExcelApp.DisplayAlerts = False
    For Each objSheet In objExcelWorkbook.Worksheets
        If objExcelApp.WorksheetFunction.CountA(objSheet.Cells) = 0 Then
           objSheet.Delete
        End If
    Next
    objExcelApp.DisplayAlerts = True
 
    'Delete the temp folder
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    objFileSystem.DeleteFolder (strTempFolder)
End Sub

5. After that, you can close the window and add the new macro to Quick Access Toolbar.
6. Later, pitch on the source email in the mail list.
7. Then, hold down the “Ctrl” to select the Excel workbook attachments which you want to export.
8. Next, click the macro button in Quick Access Toolbar.
9. When the macro completes, a new Excel workbook will display. It’ll contain the contents of the previously selected Excel workbooks.

Fall back on a Mightier External Repair Tool

When meeting Outlook corruption, most users will tend to make use of the inbox repair tool to have a try in the first place. However, it won’t be a lot of help in case of serious damage. At that point, you have no choice but to take aid of an external reliable and experienced tool, such as DataNumen Outlook Repair.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf recovery and outlook repair software products. For more information visit www.datanumen.com