VBA program to mail a row to multiple people

Hi,

I am trying to create a macro that would loop through a set of rows and send emails via outlook. Please see attached Excel workbook for the data I am trying to send.

Contents of the email

"To" field: columns I and J of each row in the worksheet "Feb. 2012"
"CC" field: All of the emails in the range of "CC List" worksheet
Subject: regular string of text

Body: Text found in cells A1:A26 of "Template" worksheet AND the header row for the table in "Feb. 2012" AND the row corresponding to the email recipients.

I would like for the macro to loop through each row and send the email with contents only relevant to the recipient.

To see an example of the email I want to send please go to "Example Screenshot" Worksheet

Thank you so much!

try something like this, it may require a bit of tweaking to fit your sheet

Please Login or Register  to view this content.

with comments

Please Login or Register  to view this content.

by mistake

the method in my code is not using the outlook application method, but instead the envlope method. you can add a cc by adding the following line:

Please Login or Register  to view this content.

the address could be a variable as well.

i understand alot of people prefer to use the outlook object method, i have just found this method to be simple and quick. i often have to use it to send custom templates out to 300+ people, so i use it in a loop where part of the loop changes a lookup value, and it has worked flawlessly for me in that scenario.

what i do is make the template sheet dynamic, leave your default text, then a vlookup for the second portion of the e-mail. you can set the macro to change a cell outside of the e-mail range to the look up value, then your e-mail body will update. you can then send that. i hope that was clear, I'm not able to write the code right now for you, but if you still need help i can try to do that soon. basicaly you just need to add into the loop something like

Please Login or Register  to view this content.

and change the selected range to A1:A3, where A3 is a vlookup formula based on C1.

Good Luck,

-Dan

my code will only send a contigous range of selected cells, so you need to make your template have a set range to send, for example A1:A3, and in A3 have a formula to pull the line that should be sent.

glad it worked out for you, let me know if you need more help, ill do what i can.