Add attachment based on partial file name match

Dear All,

I hope that someone can help me with a task I need to complete very quickly. I've spent a couple of hours searching forums but cannot find exactly what I need.

I have a folder containing several hundred pdf files which use the individual's email address at the beginning of the file name. The pdf names are as follows:

emailaddress_COI_Report.pdf
Folder location: N:\December\Reports

I would like to use a macro-enabled workbook to search the folder, ignoring the last 15 characters of the file name. This should enable a match with the email addresses in sheet 1, column A, cells A2,A3, A4 etc.

When a match is found, the specific pdf should be attached to the recipient's email message.

I would be grateful for any advice on how to accomplish this.

Many thanks
halltribe

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

Thank you Mumps1. I have added attachments as you suggest.

The Excel file has a list of email addresses in column A.

I would like to use VBA to compare the email addresses in the workbook against the pdf names contained in the folder. The last 15 characters of each pdf name is the same - if these characters can be ignored, the email address in the pdf name should then match the email addresses contained in the workbook and be used to create the email message and attach the appropriate report only.

Do you want the macro to generate the email for each address in column A? If so, I would need to know what you want as the subject of the email, the contents of the body of the email and the contents of the signature part of the email.

Thank you Mumps1. Yes, a message should be generated for each email address in column A where a PDF exists that contains a match for the email address.

The subject could be "Declaration of Interest - Approved"

and the body "Dear Colleague, please find attached a report containing your declaration of interest, which has been agreed by the Corporate Services team. If you have any queries, please do not hesitate to contact the undersigned"

"Corporate Services Team
MyCompany"

Try:

Please Login or Register  to view this content.

Hi,

you only needed tot change

Please Login or Register  to view this content.

You are very welcome.