Run a macro automatic when a specific formula cell reaches a User Defined value

[RexMex]

I'm having trouble sending an email after having the user define a "Reorder" level on an inventory project. I have tried troubleshooting or tweaking Ron's http://www.rondebruin.nl/mail/change.htm but i'm currently getting a mismatch error.
If I can define Reorder Level to be Column "P" and my Remaining Inventory to be column "O". I'm trying to use the logic that if my Remaining inventory is lower than my "Reorder" Level (or threshold), an email is generated to send out to me. My Remainging Inventory is a formula and Reorder Level has conditional formatting. Any help would be very beneficial.

Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
'Dim Reorder As Double
NotSentMsg = "Not Sent"
SentMsg = "Sent"
'Above the Reorder value it will run the macro
' Reorder = 200
' Remaining Inventory < Reorder Level should trigger the Macro
Dim Reorder As Range
Set Reorder = Me.Range("P9:P80")

'Set the range with Formulas that you want to check
Set FormulaRange = Me.Range("O9:O80")
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value > Reorder Then
MyMsg = SentMsg
If .Offset(0, 2).Value = NotSentMsg Then
Call Mail_with_outlook2
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 2).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell
ExitMacro:
Exit Sub
EndMacro:
Application.EnableEvents = True
MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description
End Sub

 

[AlphaFrog]

Hi and welcome to the board.

If you disable the On Error GoTo EndMacro line, the line of code that is giving the error will be highlighted when you click the Debug button on the error message.

I think you want to change this...

If .Value > Reorder Then

To this...

If .Value > .Offset(, 1).Value Then

 

[RexMex]

Hi, Thank you for your brilliance! It worked, although I tweaked it to If .Value < .Offset(, 1).Value Then
Excel sends out an email based of the user defined Reorder value.
Thank you very much.

 

[RexMex]

The excel file sends an email alright but uses the Columns A and B from the current worksheet instead of using a different worksheet. Is there a way I can direct it to a specific worksheet? Thanks

Sub Mail_with_outlook2()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strto = Cells(FormulaCell.Row, "K").Value
strcc = ""
strbcc = ""
strsub = "Low Inventory Item"
strbody = "Hi " & Cells(FormulaCell.Row, "A").Value & vbNewLine & vbNewLine & _
"Please remember to reorder : " & Cells(FormulaCell.Row, "B").Value & _
vbNewLine & vbNewLine & "Thank you,"
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
'You can add a file to the mail like this
'.Attachments.Add ("C:\test.txt")
.Display ' or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[AlphaFrog]

This is an example to reference a cell on specific sheet...

[COLOR="Red"]Sheets("My Fav Sheet")[/COLOR].Cells(FormulaCell.Row, "A").Value