Creating Multiple Emails from same cells in a sheet

[Mancolt]

I am trying to combine 2 different VBA macros that I've found on this forum (I think) into 1. The first part of the macro changes the values in 4 cells on Sheet "Email Message", based on 1 vlookup cell. The 2nd part of the macro creates an email based off of the values of those cells, and then saves it as a draft. Unfortunately, I can't get the macro to cycle through all of the values and generate more than 1 email. It seems to me like once it gets to Outlook, the code doesn't make it back to Excel to continue cycling through the code.

I know I'm doing a terrible job describing this, so hopefully the attachment and code can be of some help.

Please Login or Register  to view this content.

Outlook Draft Email Macro - Forum Help.xlsm

Thanks!

[Mancolt]

Hopefully I can clarify my objective:

I created a "template" of information to be used as the source for each email. These cells (let's call them EmailValues) are linked to a lookup cell. As the lookup cell is changed, the EmailValues adjust. I am trying to get my macro to automatically cycle through each option in the lookup cell, create an email using the (then) current EmailValues, save and close that email draft, and then move on to the next lookup cell value, and repeat the email creation process.

I'm having trouble determining where my code is breaking down. When I run the code above, I only get 1 email draft (the last option in my range A2:A5, I get the cell.value of A5) when I'm expecting 4 emails (A2:A5).

[Mancolt]

I found my issue. The order of the Set code is important. The code below works, with the highlighted section being what I adjusted.

Please Login or Register  to view this content.

I added the On Error Resume Next in case there was no attachment for an item.