Send an email based on a cells value

Hi All,

I have a worksheet that when a cell in column F is changed I want to email out a message to a particular person.
I.E. if the cell in F is changed to Production I want to email Chris Smith with a message letting him know that he has a new Non Conformance that needs actioning.
If the cell in F is changed to Survey I want to email Lee Ashton with the same message etc etc.

I am reasonably new to VBA and not sure on the coding that is required to do this

shina,

Paste this code in the worksheet. You can get to the worksheet code by right-clicking the tab you want to run this for, and then clicking "View Code". I'm not sure how many people you want to send e-mails to. If it's a lot of people, I would suggest creating a list of triggers, e-mail addresses, subject lines, and body text in a worksheet and then referencing that using the code. The way I have the code now, it requires you to add e-mail addresses and such anytime a new person is added or removed. However, I tested this and it does what you need. You will need to look at the code and modify the e-mail addresses, subjects, and bodies. Let me know if you have any questions.

Please Login or Register  to view this content.

Originally Posted by brant.wheeler

shina,

Paste this code in the worksheet. You can get to the worksheet code by right-clicking the tab you want to run this for, and then clicking "View Code". I'm not sure how many people you want to send e-mails to. If it's a lot of people, I would suggest creating a list of triggers, e-mail addresses, subject lines, and body text in a worksheet and then referencing that using the code. The way I have the code now, it requires you to add e-mail addresses and such anytime a new person is added or removed. However, I tested this and it does what you need. You will need to look at the code and modify the e-mail addresses, subjects, and bodies. Let me know if you have any questions.

Please Login or Register  to view this content.

Thanks Brant. I will try this in the morning when I get to work. Sorry for my stupid question but body would be the message I want to send.

Yes. There are 3 things really:
1) Email Address
2) Subject - the title of the e-mail
3) Body - the more descriptive message you want to send

Originally Posted by brant.wheeler

Yes. There are 3 things really:
1) Email Address
2) Subject - the title of the e-mail
3) Body - the more descriptive message you want to send

What if I wanted to cc someone on the email. Sorry to be a pain

Thank you for this helpful link and it works a treat are you able to specify a range i.e. cells b100:b150 for example?