VBA Code to send email based on cell not working

[Malcolm torishi]

Can any please help I have been on this all day and can not get it to do what I want it to do.
I have managed to obtain this VBA Code to send an automated email when the cell valve of M12 is greater than 200. Now this code works fine when I manually type a value in M12 greater than 200 and hit the enter key but for some reason because I have an If & And function formula in M12 when the formula populates M12 with a figure greater than 200 it just does not work
Please can any one help it is so frustrating

thank you


Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("M12"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 200 Then
Call Mail_small_Text_Outlook
End If
End Sub

Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = Range("M18").Value & ";" & Range("S7").Value & ";" & Range("S11").Value
.CC = ""
.BCC = ""
.Subject = "Pre Start Warning Alert re " & Range("P3").Value
.Body = "This is an Pre Start Warning Alert to note you that we have started on site with No Pre-Start logged."
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[Domenic]

Can you post the formula you have in M12?

 

[DanteAmor]

This event is activated when a cell is modified. In the case of the formulas, the cell is not modified, the formula remains the same, what changed was the result of the formula.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]

One option is to use the calculate event, this event is activated every time a formula is updated.
Try this:

Remove the change event and put the following:

Private Sub Worksheet_Calculate()
    If IsNumeric(Range("M12").Value) And Range("M12").Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub

 

[Domenic]

Hi Dante, good catch.

 

[Malcolm torishi]

This is the if and function in m12. So when m12 = 201 from the function below the email should be displayed from the above VBA Code. But it does not work. It only works when I manually type over the formula with a number >200
[FONT=&quot][FONT=&quot]=If(And(M11<M9,M10=“”),201,0))[/FONT][/FONT]
[FONT=&quot][FONT=&quot]M9= today’s date[/FONT][/FONT]
[FONT=&quot][FONT=&quot]M10=pre start meeting date[/FONT][/FONT]
[FONT=&quot][FONT=&quot]M11= start on site date[/FONT][/FONT]

 

[Malcolm torishi]

Dante
thank you for this I will try it when I get to work on Monday and let you know how I got on

 

[DanteAmor]

Hi Dante, good catch.

Thanks Domenic, I hope it works for the OP.

 

[Malcolm torishi]

Dante
i have copied your code into my worksheet and it works perfectly, thank you so much

 

[DanteAmor]

Dante
i have copied your code into my worksheet and it works perfectly, thank you so much

I'm glad to help you. Thanks for the feedback.