Send an automatic email alert when cell value is "Yes"

Hi,

I hope somebody here can help. I have been looking on many forums and websites for a solution to this problem but have yet to crack it!

Firstly let me just say that I am a complete novice when it comes to coding so please bear with me.

Basically I have a spreadsheet to monitor behaviour in a high school. There is a column in the sheet with the heading 'PT Action Required?' which has a drop down menu where users can either select "Yes" or "No".

I would like the spreadsheet to automatically send me an email to inform me if a user selects "Yes".

So far I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("D2:D200"), Target) Is Nothing Then
    If Target.Value <> "Yes" Then Exit Sub
End If

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


strbody = "PT action required." & vbNewLine & vbNewLine & _
"Please refer to S1/2 behaviour spreadsheet." & vbNewLine & _
"Thank you."


On Error Resume Next
With OutMail
.To = "Euan.Williamson-ik@fife.gov.uk"
.CC = ""
.BCC = ""
.Subject = "PT Action Required"
.Body = strbody
.send
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub

However, nothing happens.

Does anyone have any ideas?

Thanks in advance

Hi Eaun,

I am looking for exactly the same thing for exactly the same reason! If you manage to get it working then please let me know!