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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
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
 

Malcolm torishi

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

Watch MrExcel Video

Forum statistics

Threads
1,101,943
Messages
5,483,823
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top