Hello,

I am trying to get excel to email a sales rep when their client account is nearing expiration. I have been through all of the forums and help sites trying to figure this out and I keep getting the same error, "error 91: object variable not set yet," I definitely did set it. Additioanlly, the mnacro deosnt recognize when the value of the specified cell goes above the limit i set it at. my code is reproduced below. I would appreciate any and all help. This is my second day ever dealing with VBA so thank you in advacne for your patience in dealing with me. I have attached the excel file as well.

regards,
Jacob

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 = "No"
    SentMsg = "Yes"

   
    MyLimit = 1

   
    Set FormulaRange = Me.Range("m4:m10")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook2
                    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
And the email code is 

Option Explicit

Public FormulaCell As Range


Sub Mail_with_outlook2()

    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, "P").Value
    strcc = ""
    strbcc = ""
    strsub = "Your subject"
    strbody = ""

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

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Smart ledger 1.0.xlsm