How to add an email attachement to VBA Mass Email by referencing file name in column

Hi,

I'm trying to create a macro in excel that will send an email via Outlook with 2 attachments. 1 of the attachments will be unique to the individual and I want to be able to pick up the file name by referencing the file name column in excel e.g. .Attachments.Add ("D:\File\Document2_FullName.pdf") - Record 1 is John Smith so the file link will be "D:\File\Document2_JohnSmith.pdf". A loop will also be required so that it can move to the next record.

This is what I have so far -

Please Login or Register  to view this content.

Any assistance will be greatly appreciated.

Thanks!

Hi XLC88, and welcome to the forums.

Firstly, take a look at the forum rules, this will assist you to understand what is considered "good form" when requesting assistance (and NO, you havent transgressed with your post so don't panic)

Secondly, it is always worthwhile to post a sample workbook clearly showing the inputs and the expected results. Sadly the volunteers on this forum generally all have "day jobs" and will unfortunately often bypass a request if it means they need to construct a workbook that may or may not bear any resemblance to yours.

An added bonus, you often get working code back in your workbook to subsequent development.

In terms of email processing from excel, the wonderful resource available from Ron de Bruin (easily googled) has saved my bacon a few times already.

Post a workbook and I will take a look for you (unless another solution is posted before I get there )

Again, welcome to the forum.

Jmac

Hi Jmac,

Thanks for replying to my query.

I have attached a sample for your reference.

I need to send an email to each record with 2 attachments. For 1 of the attachments, the file path will be the same for all (D:\Test\), however the document name will be "First_Lastname.PDF" - please see Col F for example.

Any assistance will be greatly appreciated.

Attachment 310329

Apologies, please refer to Col E for example.

Sorry, I get an error when trying to access the attachment in your post.

Did you use the "Go Advanced" button at the bottom of the posting screen, manage attachments option??

Jmac

one more quick question, do you have control over the workbook, that is could you add a new column if needed??

I've attached the worksheet =Test.xlsm. I've played around with the macro again and I was able to get the 2 attachments to reference the file name in Col E and F but I'm having troubles looping the macro to create a new email for the next record -

Please Login or Register  to view this content.

Yes, I have control over the workbook if new columns are required

Sorry for the spam but I got the following code to work but when the 2nd email is created, the attachments from the previous record is attached with the email in additional to their corrected files-

Example -
John Smith - 2 Attachments: John_Smith.pdf and Doc1
Jane Doe - 4 attachments: John_Smith.pdf and Doc1 plus Jane_Doe.pdf and Doc 2
Jack Lim - 6 attachments: John_Smith.pdf and Doc1 plus Jane_Doe.pdf and Doc 2 plus Jack_Lim.pdf and Doc 1

The correct attachments should be -

John Smith - 2 Attachments: John_Smith.pdf and Doc1
Jane Doe - 2 attachments: Jane_Doe.pdf and Doc 2
Jack Lim - 2 attachments: Jack_Lim.pdf and Doc 1

Please Login or Register  to view this content.

works like a charm! thanks so much for your help!