Getting VBA to send email based on different cell values in Excel

So I have attached a table were I have a column of values. I want excel to be triggered to send an email to users when the values get below a certain number. I have found a code that will do this if the number to get below (15, for instance) is the same, but I want some of the values to be different. For instance if all the numbers are in column "C" I want rows 1,2, and 3 to trigger an email when the number gets below 12, but I want rows 4 and 5 to trigger an email when the number gets below 27. The code that I found is this: how do I modify it to add different values?

Dim xRg As Range
'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("C1:C3"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value < 12 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 = "Email Address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothin

Change the line

Please Login or Register  to view this content.

to refer to the full range C1-C5

Change the line that checks the values so it also checks the row (This will determine the low value)

Please Login or Register  to view this content.

You don't have to worry about changes to other cells as these are filtered out earlier.

You might also need to pass a parameter to the mail proc otherwise you won't know which value has dipped below the min quantity.

cytop thank you, it worked perfectly!! I thought I would be able to send it to multiple users by just separating email addresses with a comma, but that didn't work. Do you know how I would be able to do that?

Multiple addresses are usually separated by semi-colons...

Please Login or Register  to view this content.

Thanks for replying…