Attaching multiple files to an Outlook mail using Excel VBA

[lawrence24]

Hello,

I've got a small macro that does the following:

1. Save active sheet as PDF in a specified folder
2. Create new mail item in Outlook
3. Attach created sheet into new mail

Then, I'll view it, and send.

Now, I just need to tweak it a bit so that it'll search the specified folder for multiple .pdf files. The catch is that there will be lots of them, but each filename has a date on it. I need to attach just the ones that have the date I specify. For instance I want to attach all the PDF's that have today's date:

1. OT_Sheet 17-08-15.pdf [x]
2. OT_Graph 17-08-15.pdf [x]
3. Media 01-02-15.pdf
4. Media_Weekly 06-08-15.pdf
5. Montly_report 17-08-15.pdf [x]

How do I do that?

Here's the code I've got so far (the file names and paths are all bogus, obviously, as I'll be changing them to the real ones later on, these are just test locations/names):

    
Sub BOB_AM()

    ChDir "C:\Users\me\Desktop\AM\BOB"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\me\Desktop\AM\BOB\OT Sheets - " & Format(Now() + 1, "dd-mm-yy") & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, From:=1, To:=1, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False

    Dim OApp As Object, OMail As Object, signature, fileList(0) As String
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
        With OMail
            .Display
        End With
        signature = OMail.HTMLbody
        With OMail
            .To = "test@test.com"
            .cc = "example@me.com; chicken.pie@bacon.com"
            .Subject = "Test Test | BOB JOHN | " & Format(Now() + 1, "dd MMM yyyy")
            .Attachments.Add "C:\Users\am\Desktop\AM\BOB\OT Sheets - " & Format(Now() + 1, "dd-mm-yy") & ".pdf"
            .HTMLbody = "<p style='font-size:12.5pt'>" & "Dear Bacon," & "<br>" & "<br>" _
                        & "Greetings from The Lord!" & "<br>" & "<br>" _
                        & "Please find the attached files for your perusal." & signature _
                        & "</p>"
            .Display
        End With
    Set OMail = Nothing
    Set OApp = Nothing
    
End Sub