<

Go Premium for a chance to win a PS4. Enter to Win

x

Export or Strip Email Attachments in Outlook

Published on
51,053 Points
37,853 Views
32 Endorsements
Last Modified:
Awarded
Intro

I've seen a few requests floating around for scripts that will export or strip attachments out of emails, and so far have never encountered any high quality implementations of this feature, so I created this method with a little flavor. Whether you just want an easy way to save all attachments somewhere on your computer, or you're doing a bulk reduction of your inbox size, this script should satisfy your needs.

All you need to do is select the emails you want to export or remove attachments from, and click your new "Export Attachments" button:
Export Attachments
Specify your file path:
Specify File Path
Choose whether or not you want to remove attachments from the original message(s):
Option to remove attachments or just export
(Don't worry about overwriting same-named files, I have you covered):
Prevents Unwanted Overwriting
And presto! Your attachments have been saved to the desired location:
Attachments Exported
And if you chose to strip attachments, your emails will now be conveniently prepended with the files it used to contain, their size, and a link to the path where they were saved:
Attachments Stripped


Here's how you make yourself an Export Attachments feature in Outlook

1. Insert a New Module

You're going to be making a VBA macro for going through messages and attachments. To do this, you need to open up the VBA project for your Outlook (by pressing Alt+F11). When it opens, your window will look similar to the one in the screen shot below. Then, right click your project on the left, and click Insert, Module, as shown:

2. Paste in the Macro Code

The hard work has all been done for you :)
The code below takes care of all the features describe above (and more). Just copy paste it into the window, and you're almost ready to go! The code to copy in is below:
Option Explicit

Public Sub ExportAttachments()
    Dim objOL As Outlook.Application
    Dim objMsg As Object
    Dim objAttachments As Outlook.Attachments
    Dim objSelection As Outlook.Selection
    Dim i As Long, lngCount As Long
    Dim filesRemoved As String, fName As String, strFolder As String, saveFolder As String, savePath As String
    Dim alterEmails As Boolean, overwrite As Boolean
    Dim result
    
    saveFolder = BrowseForFolder("Select the folder to save attachments to.")
    If saveFolder = vbNullString Then Exit Sub
    
    result = MsgBox("Do you want to remove attachments from selected file(s)? " & vbNewLine & _
    "(Clicking no will export attachments but leave the emails alone)", vbYesNo + vbQuestion)
    alterEmails = (result = vbYes)
    
    Set objOL = CreateObject("Outlook.Application")
    Set objSelection = objOL.ActiveExplorer.Selection
    
    For Each objMsg In objSelection
        If objMsg.Class = olMail Then
            Set objAttachments = objMsg.Attachments
            lngCount = objAttachments.Count
            If lngCount > 0 Then
                filesRemoved = ""
                For i = lngCount To 1 Step -1
                    fName = objAttachments.Item(i).FileName
                    savePath = saveFolder & "\" & fName
                    overwrite = False
                    While Dir(savePath) <> vbNullString And Not overwrite
                        Dim newFName As String
                        newFName = InputBox("The file '" & fName & _
                            "' already exists. Please enter a new file name, or just hit OK overwrite.", _
                            "Confirm File Name", fName)
                        If newFName = vbNullString Then GoTo skipfile
                        If newFName = fName Then overwrite = True Else fName = newFName
                        savePath = saveFolder & "\" & fName
                    Wend
                    
                    objAttachments.Item(i).SaveAsFile savePath
                    
                    If alterEmails Then
                        filesRemoved = filesRemoved & "<br>""" & objAttachments.Item(i).FileName & """ (" & _
                                                                formatSize(objAttachments.Item(i).size) & ") " & _
                            "<a href=""" & savePath & """>[Location Saved]</a>"
                        objAttachments.Item(i).Delete
                    End If
skipfile:
                Next i
                
                If alterEmails Then
                    filesRemoved = "<b>Attachments removed</b>: " & filesRemoved & "<br><br>"
                    
                    Dim objDoc As Object
                    Dim objInsp As Outlook.Inspector
                    Set objInsp = objMsg.GetInspector
                    Set objDoc = objInsp.WordEditor

                    objMsg.HTMLBody = filesRemoved + objMsg.HTMLBody
                    objMsg.Save
                End If
            End If
        End If
    Next
    
ExitSub:
    Set objAttachments = Nothing
    Set objMsg = Nothing
    Set objSelection = Nothing
    Set objOL = Nothing
End Sub

Function formatSize(size As Long) As String
    Dim val As Double, newVal As Double
    Dim unit As String
    
    val = size
    unit = "bytes"
    
    newVal = Round(val / 1024, 1)
    If newVal > 0 Then
        val = newVal
        unit = "KB"
    End If
    newVal = Round(val / 1024, 1)
    If newVal > 0 Then
        val = newVal
        unit = "MB"
    End If
    newVal = Round(val / 1024, 1)
    If newVal > 0 Then
        val = newVal
        unit = "GB"
    End If
    
    formatSize = val & " " & unit
End Function

'Function purpose:  To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE:  If invalid, it will open at the Desktop level
Function BrowseForFolder(Optional Prompt As String, Optional OpenAt As Variant) As String
    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, Prompt, 0, OpenAt)

    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    Set ShellApp = Nothing
     
    'Check for invalid or non-entries and send to the Invalid error handler if found
    'Valid selections can begin L: (where L is a letter) or \\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":": If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\": If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else: GoTo Invalid
    End Select
     
    Exit Function
Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = vbNullString
End Function

Function BrowseForFile(Optional Prompt As String, Optional OpenAt As Variant) As String
    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, Prompt, 16 + 16384, OpenAt)
    
    On Error Resume Next
    BrowseForFile = ShellApp.self.Path
    On Error GoTo 0
    Set ShellApp = Nothing
     
    'Check for invalid or non-entries and send to the Invalid error handler if found
    'Valid selections can begin L: (where L is a letter) or \\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":": If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\": If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else: GoTo Invalid
    End Select
     
    Exit Function
Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFile = vbNullString
End Function

Open in new window

When you've pasted it in, everything should look like this. The code is pretty straightforward, so those of you who like to dig in should be able to understand and customize it at will:
Code Pasted into VBA Project

3. Add a Button for Your Macro

Now you can close down the Microsoft Visual Basic window. You've done the hard part. Next we want to add the macro to the toolbar so that you can use it conveniently. Right click on a blank area of the toolbar as shown, and click "Customize...". This will bring up the Outlook Customize Toolbars dialogue.

4. Drag your Macro Onto the Toolbar

You have to find your macro and drag it onto the Toolbar now. Switch to the "Commands" tab in the dialogue, and select "Macros" from the list on the left. You should see "ExportAttachments" macro there. Select it and drag it to wherever you want it on your toolbar:
Add the Macro to the Toolbar

5. Rename the Button

You probably don't like the ugly name Outlook has given your button, so go ahead and rename it. To do this, click the "Rearrange Commands" button at the bottom of the dialogue we have open (I know - pretty unintuitive). A new dialogue will open. Click the "Toolbar" option button (instead of "Menu Bar") and find the new button you just created. When you find it, select it and click the "Modify Selection" button. Here, you can rename it to whatever you want. Here's an illustration:
Rename the Button
If you want, you can give your button a shortcut by inserting an ampersand (&) in front of the letter you want to be the shortcut. Then when you press Alt+'That Letter', it will trigger the button. Careful, if you chose a letter that is already a shortcut (like E for the 'Edit' menu item) then you'll have to press Alt+E+E(again) to cycle through to your button.

6. Test it out!

Now test it out! Select one or more emails, click the button, and save out your attachments. Isn't that sharp?
Export AttachmentsAttachments Stripped

A few extra notes

If you're about to overwrite a file, the dialogue pops up to confirm the overwrite or let you change the file name. If you keep changing the name to that of a file that already exists, it will keep prompting you  :)
The code puts a nicely formatted message at the top of your emails, including all the names of removed files, and their sizes, and even the path where they were saved (as a url). You can customize this message if you edit the code.
If you move the file after you've saved it, the link in the emails doesn't get updated (unless you do it manually), so be aware if you plan to make use of this feature to track down attachments in the future - save it where you want to store it!
If you click cancel in the initial folder chooser dialogue, the whole routine will end. If you click cancel when picking a new name for a file that already exists, it will just skip that file and leave it attached to the email.
If you want a button that will always strip attachments, or one that will always just save all attachments, but leave the email as is, you can easily modify this macro to not prompt you and always perform the desired action.
For instance, replacing the linesresult = MsgBox("Do you want to remove attachments from selected file(s)? " & vbNewLine & _ "(Clicking no will export attachments but leave the emails alone)", vbYesNo + vbQuestion) alterEmails = (result = vbYes) with justalterEmails = false will cause the macro to just save attachments but not strip them out.

One other thing you might want to consider is changing the icon of your button. I'm sure you've noticed that that nice icon on the first page doesn't appear on its own. I created that in the same place as I renamed the button. After clicking "Modify Selection", click "Modify button icon..." and you will get a window where you can specify your own icon. This is how I designed mine:
Design your own Button Icon

Enjoy, and if you found this article handy, let me know and click "Yes" at the top of the page!

--
Alain Bryden

PS: If you're curious about that 'Archive Message(s)' button on my toolbar, you can check out my article about it here: How to make an 'Archive' button in Outlook
Other articles by me.
32
Comment
Author:alainbryden
35 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
Alain, brilliant!
Look how much cooler my icon is, though.
My Cooler IconSeriously, as a testament to your article, having the icon image and at a decent size I was easily able to replicate even that detail in implementing this in my system.

I love this Macro, very nicely done ...

You have my Yes vote.
0
 
LVL 71

Expert Comment

by:Qlemo
Good stuff! This article shows how you can do that replacing/removing thing with own code.

I for myself use Outlook Attachment Remove Add-In (http://www.kopf.com.br/outlook/) for exact the same action. It never had any issues replacing the attachments with file system links, or removing them completely. It has some more options and is free. If you do not like to use own code, it is really worth a try.
0
 

Expert Comment

by:dcmathis
This is just the tool that I'm looking for.  However, I'm getting a runtime error when trying to run the macro.  If it makes a difference, I'm using outlook 2010.  Specifically, the flagged line is line 62, "                    objMsg.HTMLBody = filesRemoved + objMsg.HTMLBody"  Any chance that this could be related to the version of Outlook?

Sorry, I'm not a visual basic programmer, so I'm a little in the dark here.

Thanks.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Expert Comment

by:dcmathis
Oops.  I guess I should have been more specific.  When I run the macro, it strips the attachment and saves it where instructed, but then generates a Runtime error "5": Invalid procedure call or argument.  I wonder if the problem is the .HTMLBody part of the command, as this isn't defined anywhere in the macro.

At any rate, when I close the email message I'm asked if I want to save the changes.  If I say no, then the attachment is left in the message.  If I say yes, then the attachment is NOT saved in the message, but neither are the comments that should be there, i. e. attachment name and where saved.

Thoughts?
0
 
LVL 21

Author Comment

by:alainbryden
Sorry, this is only guaranteed to work for Outlook 2003 and 2007. I would have expected it to work for 2010 as well, but Microsoft could have very well changed their object model to no longer support writing to the objMsg.HTMLBody property. This might also be disabled due to a security setting on your system so that viruses can't alter the content of emails before you send them out. Maybe if you post a question referring to this article an expert with Outlook 2010 can figure out whether or not this can be made to work.

Cheers,
Alain
0
 

Expert Comment

by:dcmathis
Alain,  Sorry, my mistake.  It does work fine, as long as the email message is displayed as html.  If the message is displayed as plain text, then the file is exported just fine, but the note isn't inserted in the email message.
 Two more questions:  
1.  How would I modify it to prompt for a filename in all situations, rather than just if the file already exists, or possibly utilize the default "Save As" dialog box?  
2.  How would I modify it to actually display the path to to the saved attachment instead of the [Location Saved] text, perhaps while still having it be a clickable link?
Thanks again for a great tool.

Dow
0
 
LVL 21

Author Comment

by:alainbryden
1. Unfortunately, Outlook does not have provide access to the nice Save As dialogue in VBA the way Excel does, which is why I used the folder selection solution I did. With work, one might be able to come up with a better solution.

To always ask for a file name, Replace
While Dir(savePath) <> vbNullString And Not overwrite

Open in new window

with just
Do

Open in new window

and then at the end of the loop, replace
Wend

Open in new window

with
Loop While Dir(savePath) <> vbNullString And Not overwrite

Open in new window


2. Replace the following line with the one below it.
"<a href=""" & savePath & """>[Location Saved]</a>"

Open in new window

"<a href=""" & savePath & """>" & savePath & "</a>"

Open in new window


Cheers,
Alain
0
 

Expert Comment

by:dcmathis
FANTABULOUS!!!!

Thanks a million!
0
 

Expert Comment

by:BrianEsser
Excellent! Thanks for your efforts - This was perfect for what I was looking for.
I didn't have to register with another website and I learned some things in the process thanks to your HIGH quality work. You made it a cake walk and simplified the complexities completely as far as I'm concerned.

What would be a good way to get you some points or recognition for your efforts?

Cheers,
Brian
0
 
LVL 21

Author Comment

by:alainbryden
Just voting this article helpful gets me points, so I thank you for that and for the kind words of encouragement :)

Alain
0
 
LVL 3

Expert Comment

by:Jofnn
Fantastic Alain!

Only one tweek I'd recommend, would be to have a variable read the "received date" so that it could be pumped into the filename... i.e. for easy filtering:  Turning the file name into something like

(strRCVDATE)_(strEXPDATE)_(filename)

Where strRCVDATE is when it was received... strEXPDATE is where it was exported

Any ideas how to achieve this?
0
 
LVL 21

Author Comment

by:alainbryden
You could get those strings by running the objMsg.ReceivedTime and Now() values through Format( value, "yyyy/mm/dd") - and then concatenating them with the file name.

Hope that helps ./
0
 
LVL 21

Author Comment

by:alainbryden
Some troubleshooting notes:

After putting the macro into outlook, next time you close outlook, you should get a message:

"Do you want to save the VBA project 'VbaProject.OTM'?"

You should answer "Yes" so that the macro is available next time.


It's possible that the next time you open Outlook - Outlook will detect the macro and disable it by default.

If you go to Tools > Trust Center, and then click the tab called "Macro Security", you probably have "Warnings for signed macros; all unsigned macros are disabled" checked. Because this macro is unsigned (you made it yourself), outlook will disable it by default, so you will need to select "No security check for macros." This will allow the macro to work by default each time Outlook is restarted.

Alain
0
 

Expert Comment

by:JGarza81
Hi Alain!

I think this is great! I would like to make a small tweek to show that the file did have an attachment. I would like to be able to keep the attachment sign with the email so I know that e-mail had an attachment. Is there a way to creat a shortcut to the file and save that to the e-mail?

Thanks!!
Jerry
0
 
LVL 21

Author Comment

by:alainbryden
Neat Idea.

Hopefully it won't be too much trouble. Just use the short bit of code on this page to create your shortcut:
http://www.vbforums.com/showthread.php?t=234891

Next use
objMsg.Attachments.Add filePath

Open in new window

after stripping attachments, but before you save the email back.

Finally, delete the shortcut you created in a temporary location.
0
 

Expert Comment

by:JGarza81
Thanks Alain! I am going to play around with your idea and try and get it to work. I really like that. After some reasearch, another way to show the attachment sign is to make a blank text file to attach after the attachment as been stripped. Using your code bit, I set this up and it worked well. I would rather have what you have proposed, so I will play with that.

temp = saveFolder & "\Attachments Removed.txt"
                        objAttachments.Add temp, olByValue, , "Attachments Removed"

Now I am going to try and work on a way to get the attachments to save into SharePoint! Fun!
0
 

Expert Comment

by:LearnHow
How can one included the email with the extracted attachment?
0
 
LVL 21

Author Comment

by:alainbryden
Hey, you'll want to use objMsg.SaveAs to save the email itself out to file. You'll have to decide whether you want to do that before or after you've stripped attachments and added text to the original body of the email.
0
 

Expert Comment

by:cjury808
Aloha everyone,

I'm using this tool but want to utilize some of the suggestions in the comments.  I am not familiar with VBA script writing.  Could someone post the script so that it automatically saves to specific folder (i can add that is you show me where) and also adds the received date to the end of the attachment.

Mahalo!
0
 

Expert Comment

by:TheMurman
Thank you Alain, is it possible to get this working on personal folders (PST's) that are added into the Outlook profile as it is not working on them at present.  Many thanks
0
 

Expert Comment

by:cmraguilar
Hi Alain,

I've used your macro to send some files to a specific folder, although i'm running into a problem when a user sends a message as an attachment.  When I open the attached message and execute the macro on the opened message, it defaults to saving the original email, not the message that was opened.  Any help would be appreciated.  I'm still green to VBA...

Raul
0
 
LVL 21

Author Comment

by:alainbryden
Attached messages are more like attachments than messages. Unless a message is in your Mailbox list and selected, this macro won't affect it.
0
 
LVL 71

Expert Comment

by:Qlemo
You should be able to drag the attachment mail into your inbox (or whereever), and then apply the macro on that copy.
0
 

Expert Comment

by:cmraguilar
Thanks Qlemo, that works because the document will need to be viewed before running the macro.  So my next question is how would I pull the name in the "To:" field as use that in the new file name string.  

We are having people send scanned documents that need to be archived.  The emails are send from the print server to the employee and then will probably be attached when forwarded on.

I'm trying to make this semi-automagical because it will happen 500+ times.
0
 
LVL 21

Author Comment

by:alainbryden
For that, you might want to open a new question here on EE and let someone work it out for you in exchange for points.
0
 

Expert Comment

by:fytosit
It's so good is not working:

Run-time error '429'

ActiveX component can't create object.

When you go to debug it highlights:

Set objOL = CreateObject("Outlook.Application")

Nice innit?
0
 
LVL 71

Expert Comment

by:Qlemo
That error is not really related to the code. Creating an Outlook object inside of Outlook shouldn't be difficult for VBA, should it? I assume you have an issue with memory or such. Try with just this lines:
Public Sub ExportAttachments()
    Dim objOL As Outlook.Application
    Set objOL = CreateObject("Outlook.Application")
End Sub

Open in new window

and if you still get the same error (which I'm sure will happen) then ask an own question in the Outlook topic area.
0
 

Expert Comment

by:fytosit
Sorry mate but it was not the first I tried and wasn't working.

The computer is fine and I found one that works.

I would love to check it and get back to you but you said is not a code problem and I found one that works, so I will leave it.

Keep up the good work.
0
 

Expert Comment

by:KMKeller
Alain,

I've used your script to great benefit, thank you for sharing!  I do have one item that I'd like to request.  I need to timestamp the files as they're saved off, but I need the timestamp to be the original creation date of the file, rather than the current date or the email date.  For example, I need ;

Name                                            Date Modified
Test.doc                                        6/11/2013 7:25 AM

to become

Test_20130611.doc

I know you can specify this attribute using something similar to the below, but I'm at a loss as to how to enable it in your script.  Can you help?

Kirk

FileName = "C:\Email Attachments\" & _
   Format(Item.CreationTime, "_yyyymmdd") & Atmt.FileName
0
 
LVL 71

Expert Comment

by:Qlemo
KMKeller,

The only way to do that is by reading special properties contained in special attachment types. As soon as you attach a file to an email, the filesystem data is lost, e.g. for a .txt file.  Word documents keep their dates inside the .doc* file format, as other Office files do.

To show you how to retrieve such special info is certainly not part of this article, and I do not expect alainbryden to respond, so please feel free to ask a regular question referring to this article.

Qlemo
Page Editor
0
 

Expert Comment

by:KMKeller
I was thinking more along the lines of a second pass through the directory specified in the above script to save the files to.  Once they're exported, they'll have the timestamp, at that point, a second pass would allow the script to rename the files post export.
0
 
LVL 71

Expert Comment

by:Qlemo
The file's creation date will be the one the script has been executed at, so it isn't worth much to post-process. It is much better to just build a file name with the current date (or, even better, the email's sent date).
0
 

Expert Comment

by:basharz2383
This is amazing.

I do like to see if i can add the following

-Create folder shortcuts to save attachment (currently i have to go complete 5 steps to save in the desired folder)

-Option to rename file at all times or insert date/time stamp

Please advise.
0
 

Expert Comment

by:Nick Baugh
I have been using this for sometime ,  but was wondering if there is a way of adding code to only extract only certain file types for example only PDF's, any ideas on how to do this?
0
 
LVL 1

Expert Comment

by:bretli walker
Thanks for sharing helpful article. Next time, i will follow this method, but last time I exported my all outlook attachments with Softaken Outlook Extractor tool.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Join & Write a Comment

Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month