How to send an automatic email when a cell reach a certain value (smaller than 3)when that cell is calculated with a dated if formula?

[BenGrobler]

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Sheet1(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("M6"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 3 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" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Forklift Training"
.Body = "Good day HR & Training Department" & vbNewLine & _
"" & vbNewLine & _
"Please book Forklift training as soon as possible as his current license will expire very soon." & vbNewLine & _
"" & vbNewLine & _
"Best Regards" & vbNewLine & _
"" & vbNewLine & _
"ELF Management"

.display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

HOw do i modify the above code to trigger the vba code?

 

[rollis13]

Have a try with what I came up with. From the macro you posted it seems that you need to detect changes occuring in cell M6 that has a formula, so go ahead.
This macro must be pasted in the module of the sheet where M6 is to be checked. Test it with the MsgBox; if it doesn't create issues when working in your project all you have to do is have it launch your email macro.

Option Explicit
Private Sub Worksheet_Calculate()
    Dim rng    As Range
    Set rng = Range("M6")
    If Not Intersect(rng, rng) Is Nothing Then
        If rng.Value < 3 Then
            '
            MsgBox "M6 changed and is smaller than 3"                      '<- only for debug purpose
            'here you Call your macro that sends the email instead of MsgBox
            'or insert the code of the email macro
            '
        End If
    End If
End Sub