Hello Everyone!
I hope you are safe and healthy!
I am trying to set up the excel to send out emails to a number of people if a cell's value (in a range of cells) is higher than a predefined number. Although my write up results in receiving the email as required, it sends out emails even when the cells' values do not exceed the predefined number! (The initial idea of the excel created was to check dates however, this has changed so please disregard the fact that the predefined number relates to a date in numeric form!)
Refer to the below write up (for a test spreadsheet and with minor changes for GDPR purposes ?):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C7:C12"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 44423 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there," & vbNewLine & vbNewLine & _
"The selected cell in Excel document named Book2 has changed, please check the document and proceed with your actions accordingly" & vbNewLine & _
"" & vbNewLine & _
"Thank you in advance" & vbNewLine & _
"" & vbNewLine & _
"Kind regards," & vbNewLine & _
"John John"
On Error Resume Next
With OutMail
.To = "johnjohn@testmail.net; john.john@testmail2.net"
.CC = ""
.BCC = ""
.Subject = "AUTOMATED EMAIL SENT FROM EXCEL (BOOK2)"
.Body = strbody
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Also below is the test Excel created for the above write up:
Any assistance on the matter will be greatly appreciated!
Thank you in advance!
Eraclis
I hope you are safe and healthy!
I am trying to set up the excel to send out emails to a number of people if a cell's value (in a range of cells) is higher than a predefined number. Although my write up results in receiving the email as required, it sends out emails even when the cells' values do not exceed the predefined number! (The initial idea of the excel created was to check dates however, this has changed so please disregard the fact that the predefined number relates to a date in numeric form!)
Refer to the below write up (for a test spreadsheet and with minor changes for GDPR purposes ?):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C7:C12"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 44423 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there," & vbNewLine & vbNewLine & _
"The selected cell in Excel document named Book2 has changed, please check the document and proceed with your actions accordingly" & vbNewLine & _
"" & vbNewLine & _
"Thank you in advance" & vbNewLine & _
"" & vbNewLine & _
"Kind regards," & vbNewLine & _
"John John"
On Error Resume Next
With OutMail
.To = "johnjohn@testmail.net; john.john@testmail2.net"
.CC = ""
.BCC = ""
.Subject = "AUTOMATED EMAIL SENT FROM EXCEL (BOOK2)"
.Body = strbody
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Also below is the test Excel created for the above write up:
Any assistance on the matter will be greatly appreciated!
Thank you in advance!
Eraclis