Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-27-2016, 03:34 AM
emmanpelayo emmanpelayo is offline Saving Attachment Automatically Windows 7 64bit Saving Attachment Automatically Office 2010 64bit
Novice
Saving Attachment Automatically
 
Join Date: Jul 2015
Posts: 22
emmanpelayo is on a distinguished road
Default Saving Attachment Automatically

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.
Reply With Quote
  #2  
Old 09-27-2016, 04:35 AM
gmayor's Avatar
gmayor gmayor is offline Saving Attachment Automatically Windows 10 Saving Attachment Automatically Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 09-27-2016, 05:07 AM
emmanpelayo emmanpelayo is offline Saving Attachment Automatically Windows 7 64bit Saving Attachment Automatically Office 2010 64bit
Novice
Saving Attachment Automatically
 
Join Date: Jul 2015
Posts: 22
emmanpelayo is on a distinguished road
Default Thanks

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
Reply With Quote
  #4  
Old 09-27-2016, 05:34 AM
emmanpelayo emmanpelayo is offline Saving Attachment Automatically Windows 7 64bit Saving Attachment Automatically Office 2010 64bit
Novice
Saving Attachment Automatically
 
Join Date: Jul 2015
Posts: 22
emmanpelayo is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 09-27-2016, 09:24 PM
gmayor's Avatar
gmayor gmayor is offline Saving Attachment Automatically Windows 10 Saving Attachment Automatically Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 09-28-2016, 04:17 AM
emmanpelayo emmanpelayo is offline Saving Attachment Automatically Windows 7 64bit Saving Attachment Automatically Office 2010 64bit
Novice
Saving Attachment Automatically
 
Join Date: Jul 2015
Posts: 22
emmanpelayo is on a distinguished road
Default

Awesome!!! You are a legend! Thanks
Reply With Quote
Reply

Thread Tools
Display Modes


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
Saving Attachment Automatically 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
Saving Attachment Automatically 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:24 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft