Download Email Attachments Excel VBA Macro

We saw how to extract emails with Excel macros in this other post. We can also download email attachments to our local drive programmatically. For that, we need to target the Outlook application object, select one or more emails, and loop through the collection of attachments. The example below downloads attachments for just one email, but it can be put into a loop to download attachments for all the emails in a folder (see the previous post).

Macro code:

 
  Sub DownloadEmailAttachments()
      Dim OutApp As New Outlook.Application  'requires Microsoft Outlook object library
      Dim OutMail As MailItem, mailAttachment As Object
      Dim drivePath  As String, attachmentName As String
 
      Set OutMail = OutApp.ActiveExplorer.CurrentFolder.Items.GetLast  'last email in current folder in Outlook
 
      If OutMail.Attachments.Count > 0 Then
          drivePath = "C:\Users\<username>\Documents\Attachments\"
          For Each mailAttachment In OutMail.Attachments
              attachmentName = mailAttachment.Filename  'e.g. attachment.pdf
              mailAttachment.SaveAsFile drivePath & attachmentName
          Next mailAttachment
      End If
  End Sub
 

 

Macro explained:

• First, we declare object variables to represent the Outlook application and Outlook mail item objects respectively, as well as an email attachment object. We also have string variables to store the drive path and the attachment name.
• Then we target the last email in the current folder in Outlook (this is the selected folder, which may be the Inbox by default, or any other folder that has been selected manually). Alternatively, we can use the cede below to target the last email in a specific folder (no need to select the folder), or any other email.

  Set OutFolder = OutNamespace.GetDefaultFolder(olFolderInbox).Folders("Other")
  Set OutMail = OutFolder.Items.GetLast  'last email in that folder
  'or
  Set OutMail = OutFolder.Items(x)  'where x specifies the position of the email in the folder

 

IMPORTANT: We assume the last item in that folder is a mail item (it prompts an error if it’s another item type).

• In this example, we target just one email, but we could also loop through emails in a folder to download attachments for each of them.
• Now we check if the email has attachments, and if so, we loop through the collection of attachments for that email. Before the loop, we specify the drive path where attachments will be downloaded.
• Inside the attachments loop, we get the filename for each attachment and save it to the specified drive location. Additionally, we can get the file size if needed.

  attachmentSize = mailAttachment.Size  'file size in KB

 

IMPORTANT: If you do not have classic Outlook (or Outlook as part of O365) installed and setup in your machine, the macro will not work. It does not work with new Outlook or web Outlook.

This is how we download email attachments from Excel using VBA macros.

 

Other macros:

• How to Get Last 10 Rows From Google Sheets with Excel VBA macros
• How to Convert Range To Table with Excel VBA macros
• How to make Function Return Array with Excel VBA macros