Extracting Attachments from Selected Emails

This is a continuation of the article “Automating the Process of Extracting *.sql Attachments from Emails and Executing them on a set of SQL Server Databases”. If you have not read that article, I recommend you do that before proceeding further.

The Problem

Find a way to extract all attachments from selected emails into a selected folder on hard-drive.

The Solution

1. Open Microsoft Outlook.
2. Click Tools > Macro > Visual Basic Editor (or press Alt+F11) to open the VBA Editor.
3. In the VBA Editor, click Insert > Module. This will add a new Module (Module1) to the project.
4. Paste the following code in Module1:
   

   Option Explicit
   
   Sub CopyAttachmentsToScriptsFolder()
       '' Change this path to suit your needs
       CopyAttachmentsToFolder "C:\Temp\Scripts\"
   End Sub
   
   Sub CopyAttachmentsToOtherFolder()
       Dim oApp, oFolder, folder As String
       Set oApp = CreateObject("Shell.Application")
       Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
       If oFolder Is Nothing Then Exit Sub
       folder = Replace(oFolder.Self.Path & "\", "\\", "\")
       CopyAttachmentsToFolder folder
   End Sub
   
   Private Sub CopyAttachmentsToFolder(ByVal folder As String)
       Dim email As MailItem, mCount As Long
       Dim aFile As Attachment, aCount As Long
       For Each email In ThisOutlookSession.ActiveExplorer.Selection
           mCount = mCount + 1
           For Each aFile In email.Attachments
               aFile.SaveAsFile folder & aFile.FileName
               aCount = aCount + 1
           Next
       Next
       MsgBox "Successfully copied " & aCount & " attachments found in " & mCount & " emails to folder: " & folder, vbInformation
   End Sub
   
   

5. Save and close the VBA Editor.
6. In the Outlook main window, right click anywhere on the toolbar area and choose “Customize…” from the popup menu. Alternatively you can click View > Toolbars > Customize…
7. Add a new toolbar, and name it whatever you want to name it to.
8. Then switch over to Command tab. From the Categories dropdown select “Macros” and then drag the two macros from Command dropdown into your newly created toolbar.
9. You can right-click the buttons and rename them, or assign an icon if you wish to.
10. Close the Customize dialog.
11. Click the buttons and test whether they are working properly.

Enjoy! 🙂

Previous Part:

• Introduction

Next Part:

• Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases

…