VBA Code to send email based on cell not working

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.

Code:
[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:
Code:
Private Sub Worksheet_Calculate()
    If IsNumeric(Range("M12").Value) And Range("M12").Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top