How to Attach Files with Dates in VBA

Question

I have a macro that generates emails but I want to make this macro attach specific files with a date.

The date I want the macro to find when searching for the files is this:

lastSunday = DateAdd("d", 1 - Weekday(Now), Now)
Format(lastSunday, "dd-MM-yyyy")

Here is my full Macro:

    Sub macro()
 Dim OutApp As Object, OutMail As Object
    Dim emailTo As String, emailCC As String
    Dim lastSunday As Date
    Dim c As Range
    
    lastSunday = DateAdd("d", 1 - Weekday(Now), Now)
    
    emailTo = WorksheetFunction.TextJoin(";", True, ActiveSheet.Range("Table22[To]"))
    emailCC = WorksheetFunction.TextJoin(";", True, ActiveSheet.Range("Table22[CC]"))
     
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = emailTo
            .CC = emailCC
            .Subject = "Weekly Reports - " & Format(lastSunday, "dd-MM-yyyy")
            .Body = "Dear all," & vbCrLf & vbCrLf & _
            "Please find attached the Weekly report" & vbCrLf & vbCrLf & "Hope this helps, please let me know if you require any additional detail." & vbCrLf & vbCrLf & "Kind regards,"
            '.Attachments.Add "S:documents\[filename - DD-mm-YYYY]"
            OutMail.Display
        End With

End Sub

Answer 1

The Attachments.Add method creates a new attachment in the Attachments collection. The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment. So, you need to be sure the file doesn't contain forbidden symbols (it is a valid filename) and the file is located locally, for example:

Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = emailTo
            .CC = emailCC
            .Subject = "Weekly Reports - " & Format(lastSunday, "dd-MM-yyyy")
            .Body = "Dear all," & vbCrLf & vbCrLf & _
            "Please find attached the Weekly report" & vbCrLf & vbCrLf & "Hope this helps, please let me know if you require any additional detail." & vbCrLf & vbCrLf & "Kind regards,"
            .Attachments.Add "S:\documents\filename - " & Format(lastSunday, "dd-MM-yyyy") & ".ext"
            OutMail.Display
        End With