Send Email: Once priority is changed

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

How can I send an email if the value of a certain field changes to something.

For eg: If Priority field changes to 5 I want to send an email to myself.

I know how to get the email working on click of a button on the userform,
But dont know how to get it working once a field is updated.

Thanks in advance.
 
Sam,
How does the 5 get into the priority field? Is it typed in by a user or
calculated by some formula in the cell(s). I also presume this could be in a
list in a particular column? What column?

I ask those questions because if the value is typed in by someone, then you
can use the Worksheet_Change() event to test for an entry of 5 in the
cell/column and send the email based on that. But if it's controlled by a
formula, then things get more difficult in an unattended workbook.
 
Sam,
I re-read your post and I'm wondering if you actually want to send the email
based on a change of a field in your UserForm. This should help with that.
There are 2 events that would be useful for it: either the _Change() event
or the _Exit(). I'd suggest the _Exit() as then you can test for an entry of
5, whereas the _Change() would trigger if someone started typing a number
like 555-1212 just as soon as they typed the 1st '5'.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Text = "5" Then
MsgBox "You entered a 5"
... your code to send the email here.
End If
End Sub

or (not as good a choice)
Private Sub TextBox1_Change()
If TextBox1.Text = "5" Then
MsgBox "You entered a 5"
... your code to send the email here.
End If
End Sub
 
Back
Top