Batch Removal of Attachments (.PST and Exchange Server stores)

Hello! Need some help from the masses:

I need to see if there is a way or utility that allows you to batch delete all (or some based on date) attachments on a Microsoft Exchange server mailbox or an external .PST file.

We have some users who have large mailboxes strictly due to attachments and I would like to have a way to remove these attachments in a more automated way but still keep the messages.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
Hi sav2880,

I can do this with a macro, if that's an option.  I'd need to know whether this is for your mailbox or other staff's mailboxes.

sav2880Author Commented:
This would be a tool that we would allow people to use on their own mailboxes (had to get that clarified).

So some kind of macro that could be deployed where we could tell people that this would strip a folder / PST / mailbox of attachments would be ideal.
David LeeCommented:
The code for my solution is below.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Modules and click on Module1 (if you expand the tree in the top left pane and don’t see Module; right click on “Project 1” and choose “Insert Module”, then hightlight “Module 1” so you can paste into it in the right hand pane)
4.  Copy the code below and paste it into the right-hand pane of the VB Editor
5.  Edit the code as needed.  I inserted a comment line immediately before each line that needs to be changed.
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Change the Security Level setting to Medium
10.  If the file system folder where attachments will be saved doesn't already exist, then create it.
11.  Select an Outlook folder by clicking on it.
12.  Run the macro.  It will prompt for a cutoff date when it launches.  All messages dated before the cutoff date will be processed by the macro.  All attachments will be saved into the designated file system folder and replaced in the message with a link.  

I've tested the code on my system, Outlook 2003, and it worked as described.

Sub RemoveAttachmentsBasedOnDate()
    Dim olkFolder As Outlook.MAPIFolder, _
        olkItem As Outlook.MailItem, _
        olkAttachment As Outlook.Attachment, _
        strCutoff As String, _
        strTitle As String, _
        strAttachmentFolder As String, _
        strLinks As String
    'Change the folder path on the following line to the folder on your system where attachments will be saved
    strAttachmentFolder = "C:\eeTesting\MyAttachments\"
    strTitle = "Remove Attachments Based on Date"
    'Change the default date on teh following line as needed
    strCutoff = InputBox("Enter a cutoff date.  All attachments prior to that date will be saved, removed, and replaced with a link.", strTitle, Date - 30)
    If Not IsDate(strCutoff) Then
        MsgBox "You failed to enter a valid date.  The macro is aborting.", vbCritical + vbOKOnly, strTitle
        Exit Sub
    End If
    Set olkFolder = Application.ActiveExplorer.CurrentFolder
    For Each olkItem In olkFolder.Items
        If olkItem.ReceivedTime < CDate(strCutoff & " 00:00:01 AM") Then
            If olkItem.Attachments.Count > 0 Then
                strLinks = "<p>The following attachments were saved to disk at " & Time & " on " & Date & "<br>"
                For Each olkAttachment In olkItem.Attachments
                    olkAttachment.SaveAsFile strAttachmentFolder & olkAttachment.FileName
                    strLinks = strLinks & "<a href='" & strAttachmentFolder & olkAttachment.FileName & "'>" & olkAttachment.FileName & "</a><br>"
                strLinks = strLinks & "</p>"
                olkItem.HTMLBody = olkItem.HTMLBody & strLinks
            End If
        End If
    Set olkAttachment = Nothing
    Set olkItem = Nothing
    Set olkFolder = Nothing
    MsgBox "All done!", vbInformation + vbOKOnly, strTitle
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sav2880Author Commented:
Is there a way to update this macro so that the user can specify a folder to save the attachments into?

I ask because we are asking users to organize their mail and they may want to move a specific folder's attachments into a specific project file chosen at the time of the macro's creation.
David LeeCommented:
You can set the save to folder to whatever you want and different employees could use different folders.  I included a comment immedaitely before where the folder name is set.  Or am I not understanding the question?
sav2880Author Commented:
You're close. :)

I'm looking to see if that can be defined at the time of the macro's execution, through a dialog or drop down folder box of some sort (which I'm sure gets into calling API's and the such). Reason being, one of our users may be working on five projects (which will have their own folders, actually, PST files to be exact) and I would want each user to save the attachments from each project into a different folder.

I suppose I could set up macros for each folder, but that's a lot of legwork where a single macro allowing the user to select the folder at runtime would allow it to work with a single macro.
David LeeCommented:
Got it.  Sorry, I wasn't making the connection to what you were asking.  There are several ways of doing this.

1.  Show a dialog-box each time asking for the path to save to.  This is the easiest way to go, but also a dangerous one.  It's dangerous because the user could enter the wrong path and the attachments would all go to the wrong folder.  

2.  Build a list of project/folder associations into the script.  When the script ran it'd check the folder it was running against and use the matching folder.  If there was no matching folder it would abort.  This is a better approach than #1.  The down side is that to add a new project, folder, or to change the association of either one requires editing the script.

3.  Use an .ini file to store the project/folder associations.  This is essentially #2 with the list now external to the script.  This eliminates the need to edit the script to make changes to the project/folder associations.  

4.  Set the script to store all attachments in sub-folders under a root folder.  The sub-folder names would match the folder names in Outlook.  Assume the root folder is C:\My Attachments.  If I ran the script against an Outlook folder named Project A, then the attachments would all go to C:\My Attachments\Project A.  This is the best possible approach.  You don't have to do anything except run the script.  

Let me know how you'd like to proceed and we'll take it from there.
David LeeCommented:
Any update, sav2880?
sav2880Author Commented:
Sorry, fell a bit into the background here. I'm gonna jump back into this over the course of this week.
sav2880Author Commented:
Probably not going to get back to this quickly, and the answer above actually is going to serve the purposes needed now, so much thanks! :)
David LeeCommented:
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.