help with multiple worksheet_calculate on same sheet_mail with outlook

[patrickleeroy]

Hello, This is my first time using macros; and I gotta say, it is very interesting and fun I have found out, but I am stumped. I searched all over this forum for hours looking for a way to run multiple worksheet_calculates on the same sheet. The code below shows a specific cell triggering an outlook email when below a specific value, and giving a message of "SENT" to the cell next to it when the email gets sent.

Option Explicit

Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

NotSentMsg = "Not Sent"
SentMsg = "Sent"


'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("F7")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value < 5 Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook1
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).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


The problem I am having is because I wanna repeat this with ("F7:F73"), but each cell gets triggered by a different value. This one shows F7 triggered at <5. Well F8 will be triggered at <20 and F9 at <12 and so on. Below is my Module code I am using for outlook.

Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook1()

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 = "myname@mycompany.com"
strcc = ""
strbcc = ""
strsub = "ROP for " & Cells(FormulaCell.Row, "B")
strbody = "Hi Patrick," & vbNewLine & vbNewLine & _
"The re-order point for the following has been reached : " & Cells(FormulaCell.Row, "B").Value & _
vbNewLine & vbNewLine & "Please verify and re-order if necessary, thank you."

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I have figured out how to trigger each cell at different values by separating it with "End If". But I can only do that and make it work by taking out the .offset codes for my email message of "SENT", and also by removing what is in red below

strsub = "ROP for " & Cells(FormulaCell.Row, "B")
strbody = "Hi Patrick," & vbNewLine & vbNewLine & _
"The re-order point for the following has been reached : " & Cells(FormulaCell.Row, "B").Value & _
vbNewLine & vbNewLine & "Please verify and re-order if necessary, thank you."


Which the codes in red are critical to have specific emails for each cell. Please help me write this properly. Thank you.


Patrick