How to retrieve the latest file saved

[dburks1]

I have created a script that saves the curent workbook to my temp. drive. After this action is perfromed I then start Outlook and scripted the email and distribtution list for the appropriate reciepiants of the report.

My question is since the file will be the latest file saved in the temp. drive, is there a way to have outlook auto attach the "latest file saved to/in my temp drive" to the email message.

 

[TonyJollans]

No - and, as your temp folder is shared, you can't even be sure it is always the latest file that you want. It wouldn't, however, be hugely difficult, to write some code in Outlook, along these lines:

    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    Dim TempFile As Scripting.File
    Dim LatestFile As Scripting.File
    For Each TempFile In FSO.GetSpecialFolder(TemporaryFolder).Files
        If LatestFile Is Nothing Then Set LatestFile = TempFile
        If TempFile.DateCreated > LatestFile.DateCreated Then Set LatestFile = TempFile
    Next
    Set TempFile = Nothing
    Set FSO = Nothing
    ' Now, LatestFile is the one you want

[[[[[

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[dburks1]

How would incorporate that into my existing code.


Dim objOutlk 'Outlook
Dim objMail 'Email item
Dim strMsg
Const olMailItem = 0
'Create a new message
Set objOutlk = CreateObject("Outlook.Application")
Set objMail = objOutlk.createitem(olMailItem)
objMail.To = "email@email.com"
objMail.cc = "email2@email.com"
objMail.Subject = "Accuracy Report " & CStr(Month(Now) - 1) & "/" & CStr(Year(Now))

strMsg = "Please see the metrics attached." & vbCrLf & vbCrLf & "Thanks," & vbCrLf & vbCrLf
strMsg = strMsg & "My Name"

objMail.attachments.Add ("H:\TestFile.txt")
objMail.body = strMsg
objMail.display
Set objMail = Nothing
Set objOutlk = Nothing
'end sub

End With
------------------------
I know that I need to remove: objMail.attachments.Add ("H:\TestFile.txt") because this is automatically attaching the file name to the email message. Even doing so I am still getting an error message.

 

[dburks1]

I should clarify....Instead of using

objMail.attachments.Add ("H:\TestFile.txt")

I would like to use the code your provided to replace it but I am just not sure how to merge it with the existing code that I provided.

 

[fumei]

Even doing so I am still getting an error message. "

Always tell us what an error actually is. We cannot see what it is.

That being said....

1. Make it a Function, like this:

Function GetLastFile() As String
    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    Dim TempFile As Scripting.File
    Dim LatestFile As Scripting.File
    For Each TempFile In FSO.GetSpecialFolder(TemporaryFolder).Files
        If LatestFile Is Nothing Then Set LatestFile = TempFile
        If TempFile.DateCreated > LatestFile.DateCreated Then Set LatestFile = TempFile
    Next
    [b][COLOR=red]GetLastFile = LatestFile.Path[/color red][/b]
    Set TempFile = Nothing
    Set FSO = Nothing
End Function

Sub TryIt()
MsgBox GetLastFile
End Sub

The Sub will display the path to last file. Note that Tony's code does not. LatestFile is a file system object...ummmm...object. It is NOT the path string pointing to the file.

2. the returned string for .Path is NOT - repeat NOT - the full character pathname we have become used to. It uses the old old old path/filename format, and thus it will likely come out as something like:

C:\DOCUME~1\GERRY~1.KNI\LOCALS~1\Temp\~DF9C71.tmp

This could definitely be an issue!

3. note that the function will get the information from the default System temp folder. This may - or may not be - the "temp" folder you want to use.

4.

Dim objOutlk    'Outlook
Dim objMail 'Email item
Dim strMsg As String
Const olMailItem = 0
'Create a new message
  Set objOutlk = CreateObject("Outlook.Application")
  Set objMail = objOutlk.createitem(olMailItem)
  objMail.To = "email@email.com"
  objMail.cc = "email2@email.com"
  objMail.Subject = "Accuracy Report " & _
        CStr(Month(Now) - 1) & "/" & CStr(Year(Now))
        
  strMsg = "Please see the metrics attached." & _
      vbCrLf & vbCrLf & "Thanks," & vbCrLf & vbCrLf
  strMsg = strMsg & "My Name"
[COLOR=red]' USE the Function[/color red]        
  objMail.attachments.Add GetLastFile
  objMail.body = strMsg
  objMail.display
  Set objMail = Nothing
  Set objOutlk = Nothing

5. Are you using early-binding? In which case, it is better if you use:

Dim objOutlk As Outlook.Application

rather than a Variant, as you have it now.



Gerry

 

[TonyJollans]

Thank you, Gerry, for saving me a lot of explanation!

dburks1 - As Gerry asks, what error do you get?



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website