Sending an automatic e-mail everytime a range is updated

[optionsactuelles]

Hi!

I'm new to VBA, and I've been trying to find how to send an automatic e-mail from Excel whenever someone updates the file in Column Q to "YES". In the column, there are 3 options: YES, NO, or N/A. There are hundreds of rows so hundreds of previous Yes, No, and N/A.

I only want the e-mail to be sent whenever there is a new YES that is updated in that specific column. If the user updates it to anything else or leaves it blank, no e-mail is sent.

Thank you so much!

 

[ClimoC]

Hello

So ultimately what you're going to need - since you won't be keeping an endless record of what was and wasn't just done, would be something like the following:

1) Assume your 'YES/NO/NA' column was Col "Z"
2) Somewhere in the table/same row, I would keep a spare field - we'll call it "Recently set to Yes"
3) A Worksheet_Change event would set this new field to '1' when you mark col Z as 'Yes'
4) A controlled user-driven event would then send an email for each item it finds with '1' in the new column.
5) When the email has been sent, the '1' is deleted (preventing future repeats of the same email)

First part, which sits in the Worksheet Module of the Sheet that gets edited, would be something like:

Private Sub Worksheet_Change(Byval R as Range)
Dim rRow as long
Dim is_newCol as long
Dim yesNoNaCol as long

yesNoNaCol = 26  '26, if it's in Column "Z" (1 = A, 13 = M, etc)
is_newCol = 27 'or wherever your new column in point 2 in the post sits
rRow = R.Row 'the row you've changed the value on

If R.Column = yesNoNaCol then  'did you update the YesNoNaCol?
    If R.value = "YES" then 
        Activesheet.Cells(rRow, is_newCol).value = 1
    end if
End if

Next bit (which might require some more reading - btw there will be hundreds of posts on MrExcel.com specifying how to create an Outlook email object, fill the subject/to/body text and send it), you would simply have to add a line at the end (after sending the email) which sets all 1's to 0's (or blank, doesn't matter) in the "Recently Set to Yes?" column.

Doing it in controlled batches, even if you click the button each time, will save you in case you or someone/thing else changes that value by mistake.