strto = Cells(FormulaCell.Row, "K").Value Run Time Error

[JVRamoso02]

Hi,

I'm trying to use the code provided in the attached macro shared at https://www.rondebruin.nl/win/s1/outlook/bmail9.htm for the entry regarding "Run a macro automatic when a specific formula cell reaches a certain value" since I need to create a similar macro. Unfortunately, when I try to run the macro for sheet "MoreThenOneFormulaValueChange", it is returning to a run time error saying "Object Variable or With block variable not set". I tried to debug and the error is coming from the MailCode Module created for Outlook2 specifically for the entry "strto = Cells(FormulaCell.Row, "K").Value". Could you please help advise how I can fix and make this work? I tried to change this entry to "strto = my email address and deleted all entries containing Cells (FormulaCell.Row, "#").Value and it works, I received an email from outlook but I need to make the code above work since there will be several emails in Column K.


I really hope someone can help me fix this. Looking forward to your feedback.

 

[gallen]

You'll struggle to get people to help writing in blocks of text.

Try to space it out and use code tags where possible.

My guess is that 'FormulaCell' is nothing and hasn't been set. but as you don't post all the code it's difficult to tell.

 

[JVRamoso02]

You'll struggle to get people to help writing in blocks of text.

Try to space it out and use code tags where possible.

My guess is that 'FormulaCell' is nothing and hasn't been set. but as you don't post all the code it's difficult to tell.

Hi Gallen,

Sorry for the confusing message before. Here is the code:

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 = 200

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

    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

MAILCODE

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 = "Audit Follow Up"
strbody = "Hello " & Cells(FormulaCell.Row, "D").Value & "," & vbNewLine & vbNewLine & _
"It has been " & Cells(FormulaCell.Row, "B").Value & " days since your last audit review. This is a warning that you still have " & Cells(FormulaCell.Row, "I").Value & " days until your audit explanation is past due." & _
vbNewLine & "Your corrective action plan was as follows: " & vbNewLine & vbNewLine & Cells(FormulaCell.Row, "G").Value & vbNewLine & vbNewLine & "Please provide an explanation as soon as possible." & vbNewLine & vbNewLine & "Thank you," & vbNewLine & "Marquita Mark"
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")
.Send ' or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

The run time error is coming from the mail code when i pressed the debug button specifically this one:

strto = Cells(FormulaCell.Row, "K").Value

Column K contains the emails of different people. When i replace it to a specific email and remove all formulacell.row formula, i receive an automatic email. But I need it to work for the entries in Column K. I think the formulacell is also set but correct me if I'm wrong. I'm a beginner at coding and would really like to know how I can solve this one.

 

[gallen]

Nowhere do I see 'FormulaCell' being declared and more importantly set.

This will be your problem. What cell is 'FormulaCell' meant to represent?

 

[Fluff]

Try making these 2 changes.
In your calulate event

                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook2 [COLOR=#0000ff](FormulaCell)[/COLOR]
                    End If

And in the Mail

Mail_with_outlook2([COLOR=#0000ff]FormulaCell as Range[/COLOR])