Save and Delete Attachments from Outlook messages

38 Responses to Save and Delete Attachments from Outlook messages

1. Josh on June 24, 2011 at 5:41 am

   Brilliant code!!! Exactly what I was looking for. And thanks for the super easy instructions to follow. The only difference I found was that in Outlook 2010 Alt+F11 didn’t work, so I just had to turn the developer toolbar on.
2. outlooktips on June 24, 2011 at 1:51 pm

   Alt+F11 works with Outlook 2010 – why it didn’t for you is a mystery. (Sometimes other apps interfere and hijack the shortcuts.)
3. JRM on July 8, 2011 at 7:04 am

   Any chance the code can run without prompts of any kind?
4. Diane on July 8, 2011 at 7:19 am

   Security prompts or the message boxes telling you want is happening?

   The lines of code beginning with MsgBox can be deleted – they are just there so you can see what is happening. They can be very useful for troubleshooting.

   Either delete or add a ‘ in front of each msgbox to disable it – like this:

   ‘ MsgBox strDeletedFiles
5. JonasL on August 5, 2011 at 2:57 am

   Great script, however when I mark several messages the text in each e-mail that tells you were the files were saved contains ALL files that were saved and removed, i.e. not only the attachements that were removed from that specific e-mail. Anyone else that experience this problem? Suggestions for fixing it?

   Thanks!
6. Diane Poremsky on August 5, 2011 at 4:44 am

   It’s been a long time since I wrote the code, but I think its meant for single message use. For multiple messages you need to do a loop and clear the values before moving to the next message.

   With just looking at it here quickly, so I could be wrong, it might work to reset the strAttachments value or objAttachments before looping.
7. JonasL on August 5, 2011 at 5:51 am

   Perfect, I just resetted the strDeletedFiles after each loop and now it works like a charm.

   Thanks!
8. Diane Poremsky on August 5, 2011 at 5:53 am

   Cool. Thanks for sharing.
9. Mohan on September 14, 2011 at 7:03 am

   I would like to keep attachment file names in the email message whenever I remove/delete attachments from the email. This feature was there in Lotus Notes. How to get this feature in Outlook?

   Thanks
   Mohan
10. Diane Poremsky on September 15, 2011 at 9:40 am

    You need to use an addin or VBA – the code on this page does it with this line: strDeletedFiles = strDeletedFiles & “” & “” & strFile & “”
    if you want just the file name and not the save path, you can edit the code to remove the parts that remember the path.
    See http://www.slipstick.com/addins/attachment-management-tools-for-outlook/ for addins that will do this for you.
11. Darryl Gittins on September 21, 2011 at 7:27 am

    Lookks nice but the script fails for me with a run-time error’9′, subscript out of range:

    strFolderpath = CreateObject(“WScript.Shell”).SpecialFolders(16)
12. Diane Poremsky on September 21, 2011 at 8:03 am

    I can’t repro that error – I thought maybe it was because you didn’t create the olattachments folder under My Documents – but the code runs error free, it just doesn’t actually save the attachments. (I am getting a block-end if error, not sure why since no one else complained.)

    What version of Windows? Is Windows Scripting installed/enabled?
13. kavsoo on October 5, 2011 at 8:28 am

    This code works only when the outlook is opened by the user, is there a way to make it work automatically (assuming the inbox never gets opened, and all the attachments coming to this inbox should get stored in a folder). Appreciate inputs.
14. Diane Poremsky on October 5, 2011 at 9:30 am

    The only way to run code on a mailbox that is never opened, is to do it on the server. Outlook (or any mail client) needs to check for new mail and download it before it can process it.

    Is this an exchange mailbox? If so, a server-side event sink or, if you open the mailbox as a secondary mailbox, tweaking the macro should work.
15. Frank Bello on October 5, 2011 at 12:29 pm

    Thanks so much for compiling this code. Quite of some help!
    I modified the structure to break the code into a single call for each mail item. THis way, the code can be used for all items selected, but at the same time a RULE can invoke the code also when incoming mail hits the box. here’s the modification.

    [admin note: click the link to view Frank's code or open the text file linked near the top of this page.]

    Public Sub SaveAttachments()
        Dim objOL As Outlook.Application
        Dim pobjMsg As Outlook.MailItem 'Object
        Dim objSelection As Outlook.Selection
    
        ' Get the path to your My Documents folder
        strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
        On Error Resume Next
    
        ' Instantiate an Outlook Application object.
        Set objOL = CreateObject("Outlook.Application")
    
        ' Get the collection of selected objects.
        Set objSelection = objOL.ActiveExplorer.Selection
    
        For Each pobjMsg In objSelection
            SaveAttachments_Parameter pobjMsg
        Next
    
    ExitSub:
    
        Set pobjMsg = Nothing
        Set objSelection = Nothing
        Set objOL = Nothing
    End Sub
    
    Public Sub SaveAttachments_Parameter(objMsg As MailItem)
        Dim objAttachments As Outlook.Attachments
        Dim i As Long
        Dim lngCount As Long
        Dim strFile As String
        Dim strFolderpath As String
        Dim strDeletedFiles As String
    
        ' Get the path to your My Documents folder
        strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
        On Error Resume Next
    
        ' Set the Attachment folder.
        strFolderpath = strFolderpath & "\OLAttachments\"
    
            ' Get the Attachments collection of the item.
            Set objAttachments = objMsg.Attachments
            lngCount = objAttachments.Count
    
            If lngCount > 0 Then
    
                ' We need to use a count down loop for removing items
                ' from a collection. Otherwise, the loop counter gets
                ' confused and only every other item is removed.
                For i = lngCount To 1 Step -1
    
                    ' Save attachment before deleting from item.
                    ' Get the file name.
                    strFile = objAttachments.Item(i).FileName
    
                    ' Combine with the path to the Temp folder.
                    strFile = strFolderpath & strFile
    
                    ' Save the attachment as a file.
                    objAttachments.Item(i).SaveAsFile strFile
    
                    ' Delete the attachment.
                    'objAttachments.Item(i).Delete
    
                    'write the save as path to a string to add to the message
                    'check for html and use html tags in link
                    If objMsg.BodyFormat  olFormatHTML Then
                        strDeletedFiles = strDeletedFiles & vbCrLf & ""
                        Else
                        strDeletedFiles = strDeletedFiles & "" & "<a href='//" & _
                        strFile & "'>" & strFile & "</a>"
                    End If
                Next i
            End If
    
            ' Adds the filename string to the message body and save it [COMMENTED AS THIS FUNCTION WAS NOT DESIRED]
            ' Check for HTML body
            '        If objMsg.BodyFormat  olFormatHTML Then
            '            objMsg.Body = objMsg.Body & vbCrLf & _
            '            "The file(s) were saved to " & strDeletedFiles
            '        Else
            '            objMsg.HTMLBody = objMsg.HTMLBody & "" & _
            '            "The file(s) were saved to " & strDeletedFiles & ""
            '        End If
            objMsg.Save
    ExitSub:
    
        Set objAttachments = Nothing
        Set objMsg = Nothing
        Set objOL = Nothing
    End Sub
    
16. kavsoo on October 21, 2011 at 8:27 am

    Thank you Diane for your response;

    Here’s my situation, this is an exchange mailbox. I’m afraid that the IT admin will allow me to set up a server-side event for a small task, so this is out of the question.

    So can you explain more about “opening the mailbox as secondary and tweaking the macro”?

    thanks again
17. Nick on October 26, 2011 at 4:06 pm

    Hi, thanks for publishing this. It would really help if the macro were able to create a subfolder for each email’s attachments, labelled with the Subject of the email (truncated and cleaned of unusable characters if needed) with a _YYYY MM DD_hh mm ss suffix for date and time received. Does anyone know how to add this easily?

    Thanks again.
18. Christopher W. Brown on October 31, 2011 at 9:48 am

    Thanks for the code. I tried using it a few months ago but got syntax error messages. Not a VBS guy and this is the first time I’ve attempted programming outlook so I put it off. I revisited it today when I had some time. Anyway, I am using Outlook 2007 on Windows 7 Pro and I had to replace all of the ‘&’ with just the ‘&’ and the ‘>’ and ‘<’ with actual > and < signs. Now it works as advertised.

    The msg box didn't really look correct either. It was displaying the html text in the box. But I was able to know what it was telling me and now I've debugged and deleted the MsgBox stuff so I'm good. Thanks again for the code. Just wanted to inform those who may get the syntax error using the '&' type commands.

    (Also had to Ctrl-V to paste code not Ctrl-P.)
19. Diane Poremsky on October 31, 2011 at 10:33 am

    Thanks for bringing the problems to my attention. I’ll fix the code… again. (Code samples are the one big failing with WordPress.)
20. Rob on November 3, 2011 at 12:46 pm

    Wow this is great, thanks! Clear, well commented and well thought out. You’re very good
21. John Fitzgerald on November 4, 2011 at 10:52 am

    Awesome! Thanks. Anyone know how to keep the paperclip icon?

    Also, I had to comment out the last End If for some reason.
22. Diane Poremsky on November 5, 2011 at 8:44 pm

    You can’t keep the paperclip unless there is an attachment – you can remove the line from the code that deletes the attachment or rework it to add a small attachment to the message.
23. James on November 23, 2011 at 2:46 am

    I have the same situation. Have you found a solution yet?
24. rakesh seebaruth on November 26, 2011 at 1:37 am

    Hi ,

    i have a master copy file named(master copy.xls).whatever name is given in cell “e5″ is saved as by that name.(e.g if cell e5 is tom) then the file will be saved as tom.xls. My master copy file will remained unchanged. My problem is that when i attached the tom.xls file thru outlook .the latter does not send the file tom.xls but it send the master copy.xls file .Plz help My VBA Codes are below

    Sub save()
    mydrive = “C:”
    mydir = “excel”
    myname = Sheets(“sheet1″).Range(“e5″)
    Application.DisplayAlerts = False
    ThisWorkbook.saveas Filename:=mydrive & “\” & mydir & “\” & myname & “.xls”
    Application.DisplayAlerts = True
    End Sub

    Sub Mail_Workbook_1()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .To = ” aa@yahoo.com”
    .CC = “”
    .BCC = “”
    .Subject = Range(“e5″)
    .Attachments.Add ActiveWorkbook.FullName
    .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    rakesh seebaruth is offline Reply With Quote
25. Chris on January 19, 2012 at 9:13 am

    I have two questions:

    Question 1 -
    This code worked great, How would I modify this code so that it would save the email message to disk in an outlook message file format. The email messages that I am wanting to process do not have attachments and would like to know how to modify this code to save it to disk. Now I have to manually do a “File Save AS” and know I can get VB to do it for me automatically.

    This code worked so well for attachments that I know it would work good for just saving the msg to file.

    Question 2 –
    I am trying to make a “rule” in my outlook to process incoming emails with a certain subject line and then call the SaveAttachments VB code to run. I cant find a way to call this vb code macro. I can run the macro manually on an email message and it works just fine but can’t see how to have a rule call this macro and run it automatically.
26. Chris on January 19, 2012 at 9:17 am

    I wanted to add to my above question # 2 that I used Frank’s modified code that is supposed to break it into a single call for each mail item so you can use it with a rule to filter incoming mail but still did not understand how to get the rules to work with that code modification.
27. Augusto Papagno on January 26, 2012 at 11:09 am

    Hello:
    I did some modifications on Frank’s code, in order to avoid file overwritting when the attachment name is the same as an existing one (i.e. details.txt on a return receipt email). Now, the attachemnts are saved in the format “SenderName.ReceivedDate.filename.ext” and then the attachment is deleted from the orinal email.

    Here is the code:

    Public Sub SaveAttachments()
    Dim objOL As Outlook.Application
    Dim pobjMsg As Outlook.MailItem ‘Object
    Dim objSelection As Outlook.Selection

    ‘ Get the path to your My Documents folder
    strFolderpath = CreateObject(“WScript.Shell”).SpecialFolders(16)
    On Error Resume Next

    ‘ Instantiate an Outlook Application object.
    Set objOL = CreateObject(“Outlook.Application”)

    ‘ Get the collection of selected objects.
    Set objSelection = objOL.ActiveExplorer.Selection

    For Each pobjMsg In objSelection
    SaveAttachments_Parameter pobjMsg
    Next

    ExitSub:

    Set pobjMsg = Nothing
    Set objSelection = Nothing
    Set objOL = Nothing
    End Sub

    Public Sub SaveAttachments_Parameter(objMsg As MailItem)
    Dim objAttachments As Outlook.Attachments
    Dim i As Long
    Dim lngCount As Long
    Dim strFile As String
    Dim strFolderpath As String
    Dim strDeletedFiles As String

    ‘ Get the path to your My Documents folder
    strFolderpath = CreateObject(“WScript.Shell”).SpecialFolders(16)
    On Error Resume Next

    ‘ Set the Attachment folder.
    strFolderpath = strFolderpath & “OLAttachments”

    ‘ Get the Attachments collection of the item.
    Set objAttachments = objMsg.Attachments
    lngCount = objAttachments.Count

    If lngCount > 0 Then

    ‘ We need to use a count down loop for removing items
    ‘ from a collection. Otherwise, the loop counter gets
    ‘ confused and only every other item is removed.
    For i = lngCount To 1 Step -1

    ‘ Save attachment before deleting from item.
    ‘ Get the file name.
    strFile = objAttachments.Item(i).FileName

    ‘ Combine with the path to the Temp folder.
    strFile = strFolderpath & objMsg.SenderName & “.” & Format(objMsg.ReceivedTime, “yyyy-MM-dd h-mm-ss”) & “.” & strFile

    ‘ Save the attachment as a file.
    objAttachments.Item(i).SaveAsFile strFile

    ‘ Delete the attachment.
    objAttachments.Item(i).Delete

    ‘write the save as path to a string to add to the message
    ‘check for html and use html tags in link
    If objMsg.BodyFormat olFormatHTML Then
    strDeletedFiles = strDeletedFiles & vbCrLf & “”
    Else
    strDeletedFiles = strDeletedFiles & “” & “” & strFile & “”
    End If
    Next i
    End If

    ‘ Adds the filename string to the message body and save it
    ‘ Check for HTML body
    If objMsg.BodyFormat olFormatHTML Then
    objMsg.Body = objMsg.Body & vbCrLf & _
    “The file(s) were saved to ” & strDeletedFiles
    Else
    objMsg.HTMLBody = objMsg.HTMLBody & “” & _
    “The file(s) were saved to ” & strDeletedFiles & “”
    End If
    objMsg.Save
    ExitSub:

    Set objAttachments = Nothing
    Set objMsg = Nothing
    Set objOL = Nothing
    End Sub
28. Tony on February 13, 2012 at 7:29 am

    Thanks for the interesting information. However, I was wondering if anyone knows how to alter the Attachment Drag & Drop functionality with resepct to copying an attached file from Outlook to a Windows Explorer folder.

    When I drag an attachment to an Explorer folder, the date modified date/timestamp becomes “now” (current time), not the original attachement’s date modified. Is there a way to preserve the original timestamp when dragging attachements out of Outlook?

    Thanks in advance.
29. Diane Poremsky on February 13, 2012 at 8:15 am

    To the best of my knowledge, no you can’t change the behavior. It’s always been a bit goofy, with the Window Explorer date taking Outlook’s time stamps if the message was received within a certain time period (approx 2 weeks prior). Augusto’s code sample will add the received to the filename field – not quite the same, but it can help. I’ll see if i can put together some VBA that will set the Windows time stamp using the received time.
30. Nick on February 16, 2012 at 8:18 am

    Hi, this looks great – thanks. Unfortunately, I get a Compile error: Syntax error when I try to run it. The code breaks at “If objMsg.BodyFormat olFormatHTML Then” – it seems to want a Then or GoTo immedaiately after “If objMsg.BodyFormat”. Any thoughts?

    As a possible enhancement, would it be straightforward to remove the attachments and instead of adding text to the email, replace the attachments with a text file listing the attachments and their new locations or even a file with active links to the removed files?
31. Nick on February 16, 2012 at 1:25 pm

    Hello again – figured that out by reviewing other code online – seems it just needed a not equals sign, to read “If objMsg.BodyFormat olFormatHTML Then”. I wonder why it works without for some people.
32. Nick on February 16, 2012 at 3:59 pm

    Hi – thanks for all the work on this, it’s just great. However, I would prefer to replace the attachments with a text file indicating the location of the saved attachments, rather than adding text to the end of the message body. Does anyone know how to do this?
33. Ray on April 8, 2012 at 12:24 pm

    Hello,
    I receive emails that contain .pdf, .txt., .doc and other types of attachments. I would be very grateful if someone could show me how to modify the above code so that I can remove and save all attachments but leave any pdf files in the emails if they exist.
34. Ray on April 8, 2012 at 12:52 pm

    Forgot to include that i use Outlook 2003 on Win XP.
35. Nick on May 9, 2012 at 9:09 am

    This macro is great! It is a feature that should be built-in! Especially useful for those of us who have limits on mailbag size.

    I’d like to add a link to the OLAttachments folder in the message.
    I can make it work with Rich Text but not with HTML messages.

    I just replace the “strDeletedFiles” with “strFolderpath” but it doesn’t work.
36. Diane Poremsky on May 9, 2012 at 11:44 am

    @nick what happens when you try? Because the code adds file names to strFolderpath, it might work better to make a new string variable that is used only for the folder path:

    dim strFolderOnlypath as string
    strFolderOnlypath = strFolderpath & “OLAttachments”

    If objMsg.BodyFormat <> olFormatHTML Then
    objMsg.Body = objMsg.Body & vbCrLf & _
    “The file(s) were saved to ” & strFolderOnlypath
    Else
    objMsg.HTMLBody = objMsg.HTMLBody & “

    ” & _
    “The file(s) were saved to ” & strFolderOnlypath & “

    ”
    End If
37. Pedro M. Lledó on May 11, 2012 at 8:21 am

    Hi all,
    I’m trying to modify Augusto’s script in order to be able to select a folder to save the attachments, instead of saving it to the My Documents\OLAttachments.
    I’m trying the FolderBrowserDialog object, but I got an error about user-defined type not defined, when compiling the visualbasic code. I’m trying to link the right Library in Tools–>References, but no success.

    I’m using Outlook 2007 on Win7
    What I’m missing?

    Please advice and thanks a lot for your help in advance.
    Kindest,
    Pedro
38. Diane Poremsky on May 11, 2012 at 1:13 pm

    Which library are you referencing?