Automated extraction of email attachment on Exchange Server

Question

An Exchange email account was created through which I will receive daily emails with three different Excel attachments.

Each Excel attachment has to be saved in one of three different folders.

Can an Exchange Server be configured to automatically do this, or must I program a script that monitors the account for new emails?

I believe it is an Exchange 2007 Server.

Answer 1

I'm currently using a python script for this purpose, it logs into the mailbow trought POP3 and then download the attachements and backup the emails, with little mods it may suit your needs very well, you can compile it with py2exe if you wish it. Here is the code.

PS: This code was orignaly written by Suresh Kesmar (all credits to him) ;)

Answer 2

I'm afraid that you'll have to accomplish this with a script since Outlook likes to protect you from yourself. The below instructions are from here. I've modified somewhat to apply to your excel spreadsheets. Keep in mind that if they are from Excel 2007, then you'll have to use "xlsx" for your file extension in the script.

1. Open up Outlook. I am using Outlook 2007, but this should also work in Outlook 2003. Go to Tools>Macro>Visual Basic Editor.
2. With your project folder highlighted (in the top left, in the PROJECT pane; I just used the default project), right click and choose Insert>Module. Copy and paste the following code into the main window of the editor:

[vb]Sub SaveAttachmentsToDisk(Item As Outlook.MailItem)

Dim olkFolder As Outlook.MAPIFolder, _

olkAttachment As Outlook.Attachment, _

objFSO As Object, _

strRootFolderPath As String, _

strFilename As String, _

intCount As Integer

‘Change the following path to match your environment

strRootFolderPath = "z:\www\departments\webreports\"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set olkFolder = Application.ActiveExplorer.CurrentFolder

If Item.Attachments.Count > 0 Then

For Each olkAttachment In Item.Attachments

If objFSO.GetExtensionName(LCase(olkAttachment.FileName))

= "xls" Then

strFilename = olkAttachment.FileName

intCount = 0

Do While True

If objFSO.FileExists(strRootFolderPath &

strFilename) Then

intCount = intCount + 1

objFSO.deletefile (strRootFolderPath & strFilename)

Else

Exit Do

End If

Loop

olkAttachment.SaveAsFile strRootFolderPath & strFilename

End If

Next

End If

Set objFSO = Nothing

Set olkAttachment = Nothing

Set olkFolder = Nothing

End Sub[/vb]

3. You will need to ensure that you have the proper security level set in order to properly process the script. In Outlook, go to Tools>Macro> Security. I chose No security check for macros. This might be too loose of a restriction for your environment; if so, try the next highest setting.

4. Create a new Outlook rule (Tools>Rules and Alerts) to reflect your changes. My rule looks for new messages from a specific email address and has an attachment (the web file that I want to move), moves the message to a specific folder (so I can have a backup of the message/attachment), then runs the module/script above to move the web file to the appropriate samba share. Here is what my Rule Description looks like:

Apply this rule after the message arrives
from joeuser@email.com  
and which has an attachment  
and on this machine only  
move it to the WEBBACKUP folder  
and run Project1.SaveAttachmentsToDisk

5. Hit Apply and OK to save your rule. A couple of caveats: this is a client-side rule, so you must keep Outlook running in order for the rule to process. Also, the code will overwrite any file (in my case, in the target samba share) that has the same name as the attachment. If you only wish to make a copy, you can append a number to the attachment name. To do so, replace this line of code:

objFSO.deletefile (strRootFolderPath & strFilename)

with

strFilename = "Copy (" & intCount & ") of " & olkAttachment.FileName