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!!!

 

[Domenic]

For the current date, try...

Path = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\Collateral Cashflow Transaction Balance_" & Format(Date, "yyyymmdd") & "_1219026.csv"

Hope this helps!

 

[Trying2learnVBA]

For the current date, try...

Path = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\Collateral Cashflow Transaction Balance_" & Format(Date, "yyyymmdd") & "_1219026.csv"

Hope this helps

This will not work because the last string of numbers changes and not in a sequence. Is there code to pull the file based on "Date Modified" and set this to today's date?

 

[Domenic]

For the lastest file, have a look at the following link...

Open the Latest File in a Folder

Open the latest file in a folder using VBA in Microsoft Excel.

xl-central.com

Note that once it finds the latest file, it opens the file. You can just omit the last line, which opens the file.

Hope this helps!

 

[Trying2learnVBA]

Hey guys, I cant get it to work. The FileName Dir( keeps telling me FileName = "" empty.

Also, is there a way to edit my original post?

 

[Domenic]

Hey guys, I cant get it to work. The FileName Dir( keeps telling me FileName = "" empty.

Oh sorry, since you're looking for the latest .csv file, you'll need to replace...

MyFile = Dir(MyPath & "*.xls", vbNormal)

with

MyFile = Dir(MyPath & "*.csv", vbNormal)

Also, is there a way to edit my original post?

If the Edit button doesn't appear at the bottom of your post, then you won't be able to edit it.

 

[Trying2learnVBA]

VBA Code:
MyFile = Dir(MyPath & "*.csv", vbNormal

I tried this - it gives me an error - object needed or Myfile =""

 

[Domenic]

Can you post your revised code?

 

[Trying2learnVBA]

Can you post your revised code?

Hello,

I have not been able to figure this out yet. Here is my code that works but does not attach the file. I attach the file manually.

Sub CollRecon_Email()

Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Set EItem = EApp.CreateItem(0)
Dim CL As Worksheet
Set CL = ThisWorkbook.Sheets("CONTACT LIST")


        With EItem
        .Display
        .To = "CounterpartyRisk@TIAABank.com"
        .Subject = "Collateral Recon " & CL.Range("C1")
        .cc = "Collateral@TIAABank.com"
        '.Attachments.Add ("K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\" file name changes daily... Everything works here except I am unable to attach the desired file.
        .HTMLBody = "Hello," & "<br><br>" & "Collateral Recon is good to go" & .HTMLBody
End With


Set EApp = Nothing
Set EItem = Nothing

End Sub

This is the code I am trying to get to work based on the info provided here.

Sub LastModifiedAttachment_Email()

Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Set EItem = EApp.CreateItem(0)
Dim CL As Worksheet
Set CL = ThisWorkbook.Sheets("CONTACT LIST")

Dim MyPath As String, MyFile As String, LatestFile As String, LatestDate As Date, LMD As Date
    MyPath = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\"

'This block is commented out or I get the msgbox "No files were found..."
'        If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        
         MyFile = Dir(MyPath & "*.cvs", vbNormal)
         
'          If Len(MyFile) = 0 Then
'        MsgBox "No files were found...", vbExclamation
'        Exit Sub
'    End If
'    
       'Loop through each Excel file in the folder - DO I NEED THIS?
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
    'Workbooks.Open MyPath & LatestFile
    
        With EItem
        .Display
        .To = "CounterpartyRisk@xxx.com"
        .Subject = "Collateral Recon " & CL.Range("C1")
        .cc = "Collateral@xxx.com"
        .Attachments.Add MyPath & LatestFile '[B]This code runs until this part.  What should I put here? [/B]
        .HTMLBody = "Hello," & "<br><br>" & "Collateral Recon is good to go" & .HTMLBody
End With


Set EApp = Nothing
Set EItem = Nothing

End Sub

 

[Domenic]

Okay, I have converted LastModifiedAttachment_Email into a function that accepts the path to your folder and returns the latest CSV file, and I have re-named it GetLatestCSVFile. Therefore, try the following instead...

Sub CollRecon_Email()

    Dim MyPath As String
    MyPath = "K:\CapMkt\Collateral Recon\Findur Reports\Collateral Balances CSV\"
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    Dim LatestFile As String
    LatestFile = GetLatestCSVFile(MyPath)
    If Len(LatestFile) = 0 Then
        MsgBox "No CSV file found!", vbExclamation
        Exit Sub
    End If
   
    Dim EApp As Object
    Set EApp = CreateObject("Outlook.Application")
   
    Dim EItem As Object
    Set EItem = EApp.CreateItem(0)
   
    Dim CL As Worksheet
    Set CL = ThisWorkbook.Sheets("CONTACT LIST")
   
    With EItem
        .Display
        .To = "CounterpartyRisk@TIAABank.com"
        .Subject = "Collateral Recon " & CL.Range("C1").Value
        .cc = "Collateral@TIAABank.com"
        .Attachments.Add MyPath & LatestFile
        .HTMLBody = "Hello," & "<br><br>" & "Collateral Recon is good to go" & .HTMLBody
    End With

    Set EApp = Nothing
    Set EItem = Nothing

End Sub

Function GetLatestCSVFile(MyPath As String) As String

    'Declare the variables
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
   
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
   
    'Get the first CSV file from the folder
    MyFile = Dir(MyPath & "*.csv", vbNormal)
   
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        GetLatestCSVFile = ""
        Exit Function
    End If
   
    'Loop through each CSV file in the folder
    Do While Len(MyFile) > 0
   
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
       
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
       
        'Get the next CSV file from the folder
        MyFile = Dir
       
    Loop
   
    GetLatestCSVFile = LatestFile
       
End Function

Hope this helps!