VBA: Attach individual PDFs from folder to Outlook message with dynamic naming conventions

[foxyginger]

I have many PDF and Excel invoice files which are saved to a folder via FTP. I would like to write a Macro which grabs these files individually and attaches them to their own Outlook e-mail.

I want the subject line to read "[Group Name] [prior month] Fee Invoice" - Ex.) Deli Service April Fee Invoice
-Group Name pulls all the string text from the file name before "_"

I want the e-mail address to be pulled from data in an Excel workbook by finding the Group Name and pulling back the associated e-mail addresses.

VBA code advice?

I've written a macro to do this for static data, but not dynamic.