If you want to rapidly export all image attachments of an Outlook email to an Excel worksheet, you can refer to this article. Here we will show you a more effective way than manually exporting.
When you receive an email which contains a host of picture attachments, if you want to use them for making a report in Excel, you must long for a way which can export these images into an Excel worksheet in batches. Now, we’ll introduce you such an approach in the followings.
Export All Image Attachments of an Email to an Excel Worksheet
- To begin with, access your Outlook application in normal fashion.
- Then, in Outlook window, press “Alt + F11” key shortcuts, which will bring up the “Microsoft Visual Basic for Applications” window.
- In this screen, you need to open a module which is not being used or insert a new one straightly.
- Next, you ought to copy the piece of VBA code below into this module.
Sub ExportAllImageAttachmentsToExcelWorksheet() Dim objSourceMail As Outlook.MailItem Dim objAttachment As Outlook.Attachment Dim strImage As String Dim objExcelApp As Excel.Application Dim objExcelWorkbook As Excel.Workbook Dim objExcelWorksheet As Excel.Worksheet Dim objFile As Object Dim objFiles As Object Dim nRow As Integer Select Case Outlook.Application.ActiveWindow.Class Case olInspector Set objSourceMail = ActiveInspector.currentItem Case olExplorer Set objSourceMail = ActiveExplorer.Selection.Item(1) End Select If Not (objSourceMail Is Nothing) Then 'Save the image attachments to a temporary folder strTempFolder = Environ("Temp") & "\" & Format(Now, "yyyymmddhhmmss") & "\" MkDir (strTempFolder) Set objFileSystem = CreateObject("Scripting.FileSystemObject") For Each objAttachment In objSourceMail.Attachments If IsEmbedded(objAttachment) = False Then Select Case LCase(objFileSystem.GetExtensionName(objAttachment.filename)) Case "jpg", "jpeg", "png", "bmp", "gif" objAttachment.SaveAsFile strTempFolder & objAttachment.filename End Select End If Next 'Create a new Excel workbook Set objExcelApp = CreateObject("Excel.Application") Set objExcelWorkbook = objExcelApp.Workbooks.Add Set objExcelWorksheet = objExcelWorkbook.Sheets(1) objExcelApp.Visible = True objExcelWorkbook.Activate 'Get the images in the temporary folder Set objFiles = objFileSystem.GetFolder(strTempFolder).Files 'Insert the images into this new Excel worksheet For Each objFile In objFiles strImage = strTempFolder & Trim(objFile.Name) nRow = nRow + 1 With objExcelWorksheet .Range("A" & nRow).value = objFile.Name 'Change the height and width as per your needs .Range("B" & nRow).ColumnWidth = 10 .Range("B" & nRow).RowHeight = 80 .Range("B" & nRow).Activate With .Pictures.insert(strImage) With .ShapeRange .LockAspectRatio = msoTrue .Width = 50 .Height = 70 End With End With .Columns("A").AutoFit .Activate End With Next End If End Sub Function IsEmbedded(objCurAttachment As Outlook.Attachment) As Boolean Dim objPropertyAccessor As Outlook.propertyAccessor Dim strProperty As String Set objPropertyAccessor = objCurAttachment.propertyAccessor strProperty = objPropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001E") If InStr(1, strProperty, "@") > 0 Then IsEmbedded = True Else IsEmbedded = False End If End Function
- After that, you can exit the macro.
- Then, head to “File” > “Options” > “Quick Access Toolbar” to add this macro to Quick Access Toolbar.
- Finally, you can try this macro right now.
- Firstly, select or open a source email.
- Then, click the macro button in Quick Access Toolbar.
- When macro completes, you will get an Excel worksheet, shown like the following screenshot:
Protect Outlook File from Getting Corrupt
It is known that Outlook is prone to corruption. Therefore, we should understand how to protect Outlook form corruption. First off, so as to block virus attacks, it’s necessary to install antivirus software and never download unknown attachment. Besides, we’re better off getting hold of a potent repair tool, such as DataNumen Outlook Repair. It can offer most effective remedy in case of Outlook corruption.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and outlook repair software products. For more information visit www.datanumen.com