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

RexMex

New Member
Joined
Feb 13, 2012
Messages
3
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
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...
Code:
If .Value > Reorder Then

To this...
Code:
If .Value > .Offset(, 1).Value Then
 

RexMex

New Member
Joined
Feb 13, 2012
Messages
3
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

New Member
Joined
Feb 13, 2012
Messages
3
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

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
This is an example to reference a cell on specific sheet...

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

Forum statistics

Threads
1,136,275
Messages
5,674,786
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top