Sending an email for values that exceed a limit

Question

When I try to execute the following code, the debugger is highlighting the line strbody which decides which cells get output to the email.

I'm not sure how to activate the FormulaCell so that when a value is exceeded in a column this row will be able to used to enter information to the email.

Option Explicit
Public FormulaCell As Range

    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"

    'Above the MyLimit value it will run the macro
    MyLimit = 1

    'Set the range with Formulas that you want to check
    Set FormulaRange = Me.Range("B3:B197")

    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

MACRO

Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook1()
'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 = "ron@something.abc"
strcc = ""
strbcc = ""
strsub = "Customers"
strbody = "Hi Ron" & vbNewLine & vbNewLine & _
          "The total Customers of all stores this week is : " & Cells(FormulaCell.row, "B").Value & _
          vbNewLine & vbNewLine & "Good job"

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

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 = "MAIL@EMAIL.CO.UK"
strcc = ""
strbcc = ""
strsub = "S888 OVERDUE"
strbody = "THIS S888 IS NOW OVERDUE" & Cells(FormulaCell.row, "A").Value & vbNewLine & vbNewLine & _
          "Your total of this week is : " & Cells(FormulaCell.row, "K:Q").Value & _
          vbNewLine & vbNewLine & "HURRY UP!!"

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

Answer 1

as Vityata already stated, the expression Cells(FormulaCell.row, "K:Q").Value isn't valid

if you need a reference to cells in column K to Q in the same row as FormulaCell Range then use

Worksheetfunction.Sum(Intersect(FormulaCell.EntireRow, Range("K:Q")))

So if you wanted to return the sum of those cells then use

strbody = "THIS S888 IS NOW OVERDUE" & Cells(FormulaCell.row, "A").Value & vbNewLine & vbNewLine & _
      "Your total of this week is : " & Worksheetfunction.Sum(Intersect(FormulaCell.EntireRow, Range("K:Q"))) & _
      vbNewLine & vbNewLine & "HURRY UP!!"

But there I see a hidden bug in your code where you use Public FormulaCell As Range both in Worksheet_Calculate() event handler and in your "MACRO" Module

By doing so, in you're instantiating two different Range variables, one attached to the worksheet event handler and one to the MACRO module, and without explicitly qualifying them up to their Parent object you would reference the Range variable in the currently "active" VBA module.

So, in your code before executing Call Mail_with_outlook2 statement, any FormulaCell would reference the current iterator Range variable of your For Each FormulaCell In FormulaRange.Cells loop. But as soon as the program execution gets into Mail_with_outlook2() the "resident" FormulaCell variable is still to be set and so it's Nothing

Long story short you have two choices:

• get rid of Public FormulaCell As Range in MACRO module and explicitly qualify all FormulaCell references up to the Sheet object where the Worksheet_Calculate() resides

  for example, assuming "Sheet1" is the name of this latter, then you'd write:

  strbody = "Hi Ron" & vbNewLine & vbNewLine & _
            "The total Customers of all stores this week is : " & Cells(Worksheets("Sheet1").FormulaCell.Row, "B").value & _
    vbNewLine & vbNewLine & "Good job"
  

• get rid of all Public FormulaCell As Range in both Worksheet and MACRO module and have Mail_with_outlook1() sub accept a "FormulaCell" Range parameter

  so in Worksheet_Calculate(), instead of:

  Call Mail_with_outlook2
  

  you'd have:

  Mail_with_outlook2 FormulaCell
  

  and in Mail_with_outlook1(), instead of:

  Sub Mail_with_outlook1()
  

  you'd have

  Sub Mail_with_outlook1(FormulaCell As Range)
  

Answer 2

The error is in this here:

Cells(FormulaCell.row, "K:Q").Value Depending on what you need, try like this:

Cells(FormulaCell.Row, "K").Value
Range("K" & FormulaCell.Row & ":Q" & FormulaCell.Row).Values

Or something else.

In general, whenever you think that the error is somewhere you know, try to make the code as smaller as possible and to see whether it runs. In your case something small is this one:

Public Sub TestMe()

    Dim strBody As String

    For Each FormulaCell In Range("A1:A2")
        strBody = "Hi Ron" & vbNewLine & vbNewLine & _
        "The total Customers of all stores this week is : " _
        & Cells(FormulaCell.Row, "B").Value & _
        vbNewLine & vbNewLine & "Good job"
        Debug.Print strBody
    Next FormulaCell

End Sub