EXCEL VBA send dynamic emails messages based on trigger column

I am attempting to send emails to a distribution list based on a trigger column (column E). Each row that contains the word "Captured" should send an email to the address in column A. The email body for these outgoing emails should contain content in column D. Ideally I would like the Name (B) and Client (C) to dynamically appear in the Subject of the email.


The code below, fires to the correct recipients but sends the same message body (last one within the list to all email addresses).

I am in need of this for a large project so any help would be amazing!

Please Login or Register  to view this content.

post withdrawn

reason why your receipts are dynamic but nothing else is because of this line

Please Login or Register  to view this content.

you dont have something similar for your mess or your subject
to get subject in there you should have a variable to capture subject as well

how exactly do you want to structure your subject and your "mess"

ps why are you using

Please Login or Register  to view this content.

instead of just

Please Login or Register  to view this content.

?

i've reconstructed what i think your spreadsheet looks like and added some sample code of what you could possibly do

Please Login or Register  to view this content.

see if you does what you want
ps i changed .send to .display so it doesnt automatically send out emails while you are debugging

Originally Posted by mcjonesy

this is very close. I tried out your attachment, the only thing is that each email should be sent separately not in one joint email. So in your example there should be 4 separate emails. Any suggestions?

yes, essentially
you put the mailitems (.to,.subject,.body,etc) within the icounter For/Next Loop
so it generates seperate email per loop

Originally Posted by mcjonesy

I don't want to make things to complicated but can separate emails can be sent only for unique email addresses? In your example if row three and nine were both [email protected] with two different messages. Is it possible to group those messages in one email (with a blank line in between) while keeping other rows with different email addresses separate?

this is slightly more complicated
i will need to have a little think on how to do this the easiest way

how many rows of data are you expecting to run this for?
50, 1000, 1000000?

you can do capture > sent code at the same time of the email
no need to run it seperately

On unique emails
can i put a helper column in the data to count number of emails to each email address?
it would save me putting it in the code itself

also re:subject
i changed it to Subject = "Monsanto " & Cells(Icounter, 3).Value & " Application Development " & Cells(Icounter, 2).Value & " Opportunity"
however in the case there are more than one emails, how do you want subject handled?

it was a bit more complicated then i thought but no need for helper column since you change "Captured" to "sent"

Please Login or Register  to view this content.

few more variables involved mainly with capturing multiple lines into one email

I took your example and modified it slightly. Bug one from above is resolved. Bug two still exists.

If the enhancement from my post above isn't possible I would like to format the email body with two columns Cap and Mess. A grid would be ideal.

Thoughts? Also I would be happy to attach my excel project to these posts but it appears I am not able too.

Please Login or Register  to view this content.

Sorry wasnt able to look at this today
got bogged down at work

try these methods for uploading file
http://www.excelforum.com/the-water-...his-forum.html

ok i found the issues in your code

you didnt update the column references properly on every line

ie

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

and your rangetosearch wasnt pointing to the right column

Please Login or Register  to view this content.

in turn it wasnt updating column 9 properly

so i had a chance to properly review your code
it seems you had two running variables "cap" and "mess" to change essentially one line of Body
this seems unnecessary and can lead to confusion so i combined into one

also got rid of mutliple CountA lines and replaced them with one variable that you calculate once

Please Login or Register  to view this content.

attached new file
hopefully this one works properly now

not a problem
glad to have helped


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.