Send Email Based on Cell Value

Hi everyone,

The below VBA code is set to send an email to a recipient (in column J) whenever a "Yes" is entered in column "I:I".
The issue is that if I put "Yes" in I3 and there is already a "Yes" in I2, it will send two emails for the two lines. If I now put "Yes" in I4, it will send an email to I2, I3 AND I4.

I'd like my code to only send an email to the line I am putting a "Yes" in.

Thanks!

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. I have also added indentation because the undindented code was nearly unreadable. --6StringJazzer

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Your problem is that wile a worksheet change event is triggering the call to mail_small_text, that sub is looping through all of column I to decide who to email. You need to pass a range "Intersect(Target, Range("I:I"))" to the sub, and then only loop through that.

Welcome to the Forum M5B!

I added code tags for you, plus indentation. However, the code does not compile. There is an error on the line in red due to unbalanced quotes.

I correct that error and made the change you are asking for, but I cannot test it without your file.

The strategy is to make xCell an argument to the Sub since Worksheet_Change already knows which cell changed. Then you no longer need the loop to check every cell for "Yes".

Please Login or Register  to view this content.