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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,207
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,102,646
Messages
5,488,079
Members
407,624
Latest member
NatashaGillWH

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top