VBA to identify a specific email and copy the attachment from it to the network

Status
Not open for further replies.

Snook

Member
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
Hi,

I've created a scheduler in our Cognos reporting system that emails me an updated data extract each morning. What I would like is a macro that identifies this daily email (perhaps when I open outlook?) and copies the attachment to a defined area on the network and overwrites the previous attachment. If it helps the email address, subject name and attachment name are always the same. For the purposes of this let's say they are as follows:

Email - Snook@hotmail.com
Subject - Cognos Data Extract
Attachment - Data Extract.xlsx
Network Address - \\network\performance management

What would also be useful is if the email could be moved to another folder within my inbox once the attachment has been extracted, e.g. named 'Cognos Data'.

If you require any further info just give me a shout.

Thanks in advance,

Snook
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange

Snook

Member
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
Hi Diane,

Thanks for this, much appreciated!

I'll let you know how I get on....

Regards,

Snook
 

Lawrny

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
I am receiving a error with the line in the saveAttachtoDiskRule

Set fso = CreateObject("Scripting.FileSystemObject") 'On Error Resume Next
Notice I had to comment out the On Error Resume Next

I have added the Microsoft Scripting Runtime Reference

Public Sub saveAttachtoDiskRule(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim fso As Scripting.FileSystemObject
Dim oldName
Dim file As String
Dim DateFormat As String
Dim newName As String
Dim enviro As String
enviro = CStr(Environ("USERPROFILE"))
saveFolder = enviro & "\Documents\Daily Sales Reports\2014\"
Set fso = CreateObject("Scripting.FileSystemObject") 'On Error Resume Next
For Each objAtt In itm.Attachments
file = saveFolder & objAtt.DisplayName
objAtt.SaveAsFile file
Set oldName = fso.GetFile(file)
DateFormat = Format(oldName.DateLastModified, "yyyy-mm-dd ")
newName = DateFormat & objAtt.DisplayName
oldName.Name = newName
Set objAtt = Nothing
Next

Set fso = Nothing
End Sub

The error is Compile error Expected" end of statement

Also I expected to be able to select this as a rule... How is that done?

Thanks in advance for your help
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You create a rule and choose the Run a Script option then select this macro.

On Error Resume next should be on a separate line.

Your code works fine here - i had to change the folder path but as long as the path exists, it should work for you.
 
Status
Not open for further replies.
Top