Saving Attachment Automatically

emmanpelayo · #1 09-27-2016, 03:34 AM

Hi, in my place of work there is a report that we receive twice a day, sent to a group in outlook. I was hoping that i could automatically save the attachment file into a certain location when it arrives. Maybe I can make a rule to run a script when a message is sent to the group (there will always be a file attached). The problem is I do not know how to write a script (non-IT profession). It would be great if each file can be renamed automatically as the date and time it was received. I hope that it may be a possibility. Thanks.

gmayor · #2 09-27-2016, 04:35 AM

You can run a script from a rule that identifies the incoming messages. The following script will extract the chosen attachment (here any Excel worksheet, but you can change the extension as required). The messages are saved at the location defined in strPath (don't forget the back slash at the end of the path)

The date and time in the format
yyyymmdd-(HHMM) - Filename.ext
are added to the filename. If you just want the time and date edit the string. Watch out for illegal filename characters if changing the date format.
e.g.
20160927-(1428) - Datasheet.xlsx

Code:

Sub CustomSaveAttachments(Item As Outlook.MailItem)
Dim olAtt As Attachment
Dim strFileName As String
Const strPath As String = "C:\Path\"    ' The location where the attachment is to be saved
Const strExt As String = "xlsx"    ' the filename extension
    If Item.Attachments.Count > 0 Then
        For Each olAtt In Item.Attachments
            'Check the attachment file type by looking at the extension
            If olAtt.FileName Like "*" & strExt Then
                strFileName = Format(Date, "yyyymmdd-") & "(" & Format(Time, "HHMM) - ") & olAtt.FileName
                olAtt.SaveAsFile strPath & strFileName
            End If
        Next olAtt
    End If
lbl_Exit:
    Set olAtt = Nothing
    Exit Sub
End Sub

You can test the code before implementing it in the rule using the following with a selected message that has the attachment.

Code:

Sub GetMsg()
Dim olMsg As MailItem
    On Error Resume Next
    Set olMsg = ActiveExplorer.Selection.Item(1)
    CustomSaveAttachments olMsg
lbl_Exit:
    Exit Sub
End Sub

emmanpelayo · #3 09-27-2016, 05:07 AM

Thank you! That was perfect. But with this, it said it will only run when outlook is running. If I was not logged into the computer, then it will not save the files?? If that is so, would it save it as the current date and time that I have opened outlook and not the time that they have been received? Sorry, just trying to get my head around it. Thanks

emmanpelayo · #4 09-27-2016, 05:34 AM

Is there a command/script for the pdf's date and time that it was last modified as a substitute for the date and time it was received?

gmayor · #5 09-27-2016, 09:24 PM

The date in the filename is the date and time the message is received in the inbox. As the message is not received if Outlook is not running then that date and time, as it stands, may not be the date that you want. You could instead get the date the mesage was sent e.g. Change

Code:

strFileName = Format(Date, "yyyymmdd-") & "(" & Format(Time, "HHMM) - ") & olAtt.FileName

to

Code:

strFileName = Format(Item.SentOn, "yyyymmdd-") & _
                              "(" & Format(Item.SentOn, "HHMM) - ") _
                              & olAtt.FileName

The last modified date is far more of an issue, because you cannot read that date from the attachment until it has been saved and then the act of saving it will, I guess, update that modified date.

emmanpelayo · #6 09-28-2016, 04:17 AM

Awesome!!! You are a legend! Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Error Saving Outlook attachment Via Excel code	charlesdh	Excel Programming	1	01-04-2016 11:55 AM
Automatically print password protected pdf attachment	teegs42	Outlook	1	12-19-2014 07:51 PM
Word 2010 not saving automatically	darrylmarshall	Word	1	06-07-2012 02:36 PM
email as pdf attachment - subject line and attachment named after mail merge	Nexus	Mail Merge	12	04-13-2011 11:34 PM
Objective: Automatically export email text,attachment text to DB friendly format	SilentLee	Outlook	0	11-14-2010 02:45 PM