Find pdf file within subdirectory to attach to email

I have completed a macro in Excel that will go to a subdirectory, locate a specific pdf file from data in column A, and attach the pdf to an email. The files are kept in folders depending on the content, i.e., some of the files are in the INVOICES folder, some are in the SERVICES folder, some are in the INTERCOMPANY folder. The file will never be located in more than 1 folder.

What I would like the macro to do is find the file and attach it to an email. The macro works if I provide the complete path but I would like to find the file REGARDLESS to the subfolder name within the 2021 INVOICES folder.

Following is the path for the Services directory. There are also monthly subdirectories for INVOICES and INTERCOMPANY.

C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices\2021 Invoices\04-2021 Service Invoices

I would like the macro to look in the subdirectories in the 2021 Invoices folder.

Couldn't look at your file due to restrictions my end, but maybe something like this?

PHP Code: 

Sub FindAndAttachFile()
Dim OutApp As Object
Dim OutMail As Object
Dim fso As Object
Dim FoundFile As String
Dim searchFile As String
Const SearchPath As String = "C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices\2021 Invoices\04-2021 Service Invoices"

searchFile = Sheet1.Range("A1").Value
Set fso = CreateObject("Scripting.FileSystemObject")
SearchFolders fso.GetFolder(SearchPath), searchFile, FoundFile

If FoundFile = "" Then
    MsgBox "File not found."
Else
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.createitem(0)
    With OutMail
    .to = "abc@xyz.com"
    .Subject = "File"
    .Body = "Test!"
    .attachments.Add FoundFile
    .display
    End With
End If
Set OutMail = Nothing
Set OutApp = Nothing
Set fso = nothing
End Sub

Function SearchFolders(Folder As Variant, searchFile As String, FoundFile As String)
    Dim oFile As Object
    Dim SubFolder As Object
    
    For Each oFile In Folder.Files
        If oFile.Name Like searchFile Then
                FoundFile = Folder & "\" & oFile.Name
                Exit Function
        End If
    Next

    For Each SubFolder In Folder.SubFolders
        If FoundFile <> "" Then Exit Function
        SearchFolders SubFolder, searchFile, FoundFile
    Next
End Function 


Thank you ByteMarks can you please help me debug.
I'm getting an error at ln 9, col 38 -- searchFile = Sheet1.Range("A1").Value
I tried changing it to my sheet name -- searchFile = select email recipients.range("A2").value
same debug error

My apologies but I am really a novice vb user. The error message was "path not found". I double checked and corrected the path. Thank you
The macro goes through now but returns "file not found". I double checked that each file is in one of the subfolders; however, the invoice # on line 1 is NOT in the SERVICE folder, it is in the INTERCOMPANY folder.

Tried the following edit --
Const SearchPath As String = "C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices\2021 Invoices\ & "*" -- that didn't work either.

I'm trying to find the file regardless of it's location within the 2021 Invoices folder (a higher level in the directory tree).

yes, and all of the files are pdf's, i.e., 123456.pdf, 234568.pdf, 384013.pdf, ...

I just saw your message that you couldn't access the excel file I attached. If you would like me to email it to you, let me know.

Due to possible sensitive data in your file, I will delete this solution that used your workbook and data. I tried posting the code but got the Access Denied message twice. This is the only forum that I have that issue with.

Please let me know when you have downloaded so that I can remove the file for your security.

https://www.dropbox.com/s/jsv9tzpq6y...mail.xlsm?dl=1

In mMain module, run CreateEmail2().