Save and Open an Attachment using VBA

Last reviewed on December 30, 2013

A visitor wanted to know how to save attachments and then open them without opening Windows Explorer to find the attachment. This VBA code is based on the code sample at our Outlook-Tips site: Save and Delete Attachments. We removed the lines that delete the attachment from the message and added the file path to the message body, using the file path to open the message using Window's ShellExecute command.

The macro at Save Attachments to the hard drive is the original macro this code was built from. It saves attachments on the selected messages but does not open them.

Save and Open Attachments VBA Sample

To use this code sample, open the VBA editor by pressing Alt+F11 keys. Paste the code into the ThisOutlookSession module. If it does not exist, create the directory path on your hard drive then update the code with the path.

Select one or more attachments and run the macro.

For more information, see How to use VBA Editor

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
  "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Sub SaveandOpenAttachments()
Dim objOL As Outlook.Application
Dim objMsg As Outlook.MailItem 
Dim objAttachments As Outlook.Attachments
Dim objSelection As Outlook.Selection
Dim i As Long
Dim lngCount As Long
Dim strFile As String
Dim strFolderpath As String
Dim strExePath As String

    ' Get the path to your My Documents folder
    strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
    
    On Error Resume Next
    Set objOL = CreateObject("Outlook.Application")
    Set objSelection = objOL.ActiveExplorer.Selection

    ' Set the Attachment folder. (Folder must exist.) 
    strFolderpath = strFolderpath & "\OLAttachments\"

    For Each objMsg In objSelection
    Set objAttachments = objMsg.Attachments
    
    lngCount = objAttachments.Count
      If lngCount > 0 Then
        For i = lngCount To 1 Step -1
         strFile = objAttachments.Item(i).FileName
         strFile = strFolderpath & strFile
         objAttachments.Item(i).SaveAsFile strFile

    'use ShellExecute to open the file
    'this may not work with zip extension if you use Compressed folders
    ShellExecute 0, "open", strFile, vbNullString, vbNullString, 0
    
    Next
    End If
    Next
    
ExitSub:

Set objAttachments = Nothing
Set objMsg = Nothing
Set objSelection = Nothing
Set objOL = Nothing
End Sub

Use with 64-bit Outlook

To use this with 64-bit Outlook, replace the Declare line with this:

Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias _
  "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Open hidden attachments on Outlook.com items

To use this to open hidden attachments on Outlook.com items, change the objMsg line to this:

Dim objMsg as Object

Use the Temp folder instead of My Documents, delete
strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
and replace
strFolderpath = strFolderpath & "\OLAttachments\" with this:

strFolderpath = VBA.Environ$("Tmp") & "\"

More Information

To save and open specific attachment types (for example, only open doc and xls files) as the messages arrive, see Attachment: Print received attachments immediately. Change "print" to "open" in the ShellExecute line of that code:
ShellExecute 0, "print", sFile, vbNullString, vbNullString, 0
to
ShellExecute 0, "open", sFile, vbNullString, vbNullString, 0

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

5 responses to “Save and Open an Attachment using VBA”

  1. Jimi

    I'm the one asking about such a feature, and it's really great of you to help out like this. Although I'm not sure how often I will use this macro, because of two things:

    1. I really would want a "Save as" dialog, where I can choose the directory and file name, and get a warning if the file already exists.
    2. I would like to have this option available in the right click context menu, as "Save as and open..." or something like that.

    But I guess I can't have everything. Just to bad that the Microsoft team didn't think about this feature. The web client (ie Outlook Web Access) has this feature (after the save as dialog, I get the option to open the file), so I find it strange that the standalone client doesn't.

  2. RB

    doesnt' work. runs but files don't show up in folder. nice idea anyway.

  3. noi

    Hello,

    it is not possible to save the attachments from selected RSS Feed objects.
    Would it be possible?

    greetings
    noi

Leave a Reply

If the Post Coment button disappears, press your Tab key.