Event Triggers

Excel Community,

I have a worksheet that lists 40+ inventory items with their running total amounts. I used Ron de Bruin's code to send emails from the sheet (it is working great!). Could anyone provide the code for an event trigger so that, when the inventory dips below 15,000 lbs, a reminder email is sent out to buy more?

Thanks!

I moved she code from module 1 to sheet 2 "total". I also changed the range and changed the sheet name.

In the debugger I get the pop up, "Run-time error '13': Type Mismatch".

Any ideas why?

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range
Dim OrderMore As Double

OrderMore = Sheets("total").Range("J4:J47")

If OrderMore < "15000" Then GoTo EmailOrder
Exit Sub

EmailOrder:

On Error GoTo StopMacro

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Range that will be sent in email
Set Sendrng = Worksheets("total").Range("B2:J47")

'Remember the activesheet
Set AWorksheet = ActiveSheet

With Sendrng

' Select the worksheet with the range you want to send
.Parent.Select

'Remember the ActiveCell on that worksheet
Set rng = ActiveCell

'Select the range you want to mail
.Select

' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope

' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = "The following powders are below their safety stock inventory levels."

With .Item
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Powder Inventory Stock Levels"
.Send
End With

End With

'select the original ActiveCell
rng.Select
End With

'Activate the sheet that was active before you run the macro
AWorksheet.Select

StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False

End Sub