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
- For a start, launch your Outlook application.
- Then, access Outlook VBA editor.
- 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”.
- 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
- After that, you can close the window and add the new macro to Quick Access Toolbar.
- Later, pitch on the source email in the mail list.
- Then, hold down the “Ctrl” to select the Excel workbook attachments which you want to export.
- Next, click the macro button in Quick Access Toolbar.
- 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.
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