Send Email with VBA - Attachment question

[Trying2learnVBA]

Hello,

I have this code that works really well for automating repetitive emails from excel. However, I need to attach a file daily. So, the file's name changes daily.
How can I modify my code to always select the latest file?

Sub CollRecon_Email()

Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Set EItem = EApp.CreateItem(0)
Dim path As String
path = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\Collateral Cashflow Transaction Balance_20220824_1219026.csv" 'The name of this file changes daily - notice the "20220824" number is today's date. The last numbers are semi random.  I'd like excel to grab the file modified Today's date.
Dim CL As Worksheet
Set CL = ThisWorkbook.Sheets("CONTACT LIST")

        With EItem
        .display
        .To = "counterpartyrisk@tiaabank.com"
        .Subject = "Collateral Recon " & CL.Range("C1")
        .cc = "May.love@tiaabank.com; Wendy.burnes@tiaabank.com"
        .Attachments.Add (path) 'Here I would like the file with today's date to be picked.
        .HTMLBody = "Hello," & "<br><br>" & "Collateral Recon is good to go" & .HTMLBody
End With


Set EApp = Nothing
Set EItem = Nothing

End Sub

Thank you for your help!!!

 

[Fadafacka]

Hello,

I have this code that works really well for automating repetitive emails from excel. However, I need to attach a file daily. So, the file's name changes daily.
How can I modify my code to always select the latest file?

Hello hello!

You can modify a tiny little bit your code and it's done. With the help from the wild character *, the Dir function and some of the date related functions:

Dim sPath as String
Dim sFileName as String
Dim sFilePath as String

sPath = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\Collateral Cashflow Transaction Balance_" & Year(Date) & Month(Date) & Day(Date) & "_*.csv"
sFileName = Dir(sPath)
sFilePath = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\Collateral Cashflow Transaction Balance_" & sFileName

[...] 'After creating the email:

.Attachments.Add sFilePath

Dir function lets you use the asterisk to refer to any text string, and it will return the actual name of the file (without the previous path). You can then add this name to the preceding path altogether, and use this full file path in the .Attachments.Add method of your mail object (where the asterisk is not acting as a wild character).

I'm sorry I didn't put the whole code, I'm in the nightshift and I have some trouble thinking right now. I hope it's clear and works. I used it in my database, so if it doesn't work it's a typo problem from transcribing my code into yours.


Good luck!

 

[Fadafacka]

Hello,

I have this code that works really well for automating repetitive emails from excel. However, I need to attach a file daily. So, the file's name changes daily.
How can I modify my code to always select the latest file?

Hello hello!

You can modify a tiny little bit your code and it's done. With the help from the wild character *, the Dir function and some of the date related functions:

Dim sPath as String
Dim sFileName as String
Dim sFilePath as String

sPath = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\Collateral Cashflow Transaction Balance_" & Year(Date) & Month(Date) & Day(Date) & "_*.csv"
sFileName = Dir(sPath)
[B]sFilePath = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\" & sFileName[/B]

[...] 'After creating the email:

.Attachments.Add sFilePath

Dir function lets you use the asterisk to refer to any text string, and it will return the actual name of the file (without the previous path). You can then add this name to the preceding path altogether, and use this full file path in the .Attachments.Add method of your mail object (where the asterisk is not acting as a wild character).

I'm sorry I didn't put the whole code, I'm in the nightshift and I have some trouble thinking right now. I hope it's clear and works. I used it in my database, so if it doesn't work it's a typo problem from transcribing my code into yours.

Please ignore previous reply, sFilePath was duplicating information already included in sFileName.

Good luck!