send auto email in excel

[khoover12]

I am looking to send a email when the value in column H is < column I, I have this code below but when I change the value in column H it does not send a email. Please help!


Dim R As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set R = Intersect(Range("H:H"), Target)
If R Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value < ("I:I") Then
Call send_mail_outlook
End If
End Sub
Sub send_mail_outlook()
Dim x As Object
Dim y As Object
Dim z As String
Set x = CreateObject("Outlook.Application")
Set y = x.CreateItem(0)
z = "Hello!" & vbNewLine & vbNewLine & _
"Current quantities are lower than min. quantities" & vbNewLine & _
"Please check inventory"
On Error Resume Next
With y
.To = ""
.cc = ""
.BCC = ""
.Subject = "Check Spare Parts Inventory"
.Body = z
.Send
End With
On Error GoTo 0
Set y = Nothing
Set x = Nothing
End Sub

 

[Joe4]

I think your issue is with this line here:

If IsNumeric(Target.Value) And Target.Value < ("I:I") Then

What exactly are you trying to do there?
It does not make sense to check if a single valud is less than a whole column.

If you are simply trying to check if the new value in column H is less than the value in column I of that same row, try this instead:

If IsNumeric(Target.Value) And (Target.Value < Target.Offset(0, 1).Value) Then