VBA Not running automatically

[daddyfoxuk]

So i have the below codes... When i hit F5 the code will run perfectly although i would like it to include cell A,B,C and D in the email body. But i want this to run every time the value of any cells in I going above 1.... Can anyone help as to whys it isnt running, kinda doing my head in now haha!

On Sheet4 (Bad Parts)

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"
    
    MyLimit = 1
    
    Set FormulaRange = Me.Range("I2:I100")


    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

Module 1

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 = "****"
    strcc = ""
    strbcc = ""
    strsub = "Suspect Notification"
    strbody = ""


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


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


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 = "****"
    strcc = ""
    strbcc = ""
    strsub = "Suspect Notification"
    strbody = ""


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


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Any Help would be great!!!

 

[AlexB123]

Let me start with saying I'm not certain, but I would look at two things:

1) "Me.Range" --- I understand that "Me." should be used when the code is located on a specific sheet or module (I rarely use it, myself). I would use a more explicit reference, i.e.:

Set myWB = ThisWorkbook
Set myWS = myWB.Sheets("Sheet4")

2) A similar issue:

[LEFT][COLOR=#333333][FONT=Verdana]If IsNumeric(.Value) = False Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                MyMsg = "Not numeric"[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]            Else[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                If .Value > MyLimit Then[/FONT][/COLOR][/LEFT]

In my (limited) experience, you need the references. I assume that what happens here is the focus on each cell in the loop gets messed up ... you either need to use more specific references (each time .value is present ---- myWB.myWS.FormulaCell.Value), or change how your "With statement" is set up ... I have never used If/else blocks within a "With Statement". That ".value" might be looking at past cells, or getting confused by the branching.

To test, I would set breakpoints on every line and step through the code. Likely, while you are not getting any compile errors, the reason it runs some of the time is that your code is generating run-time errors. You need to find where the code is getting mixed up.

 

[daddyfoxuk]

To be honest i'm very confused lol! There's no run-time errors appearing and hitting F5 it does everything i want, bar the cells being copied in the body of the email... I:I has the value of 1 until "on suspect list" appears in C:C and then it will change to the value of 2. J:J will then change to "sent" instead of "Not Sent" and i'm hoping somewhere in all of this it would create and send the email automatically. Im stuck lol!

 

[AlexB123]

Try providing more complete references for ".Value" and ".Offset".

I would also look carefully at the intersection of FormulaRange.Cells and other calls to "FormulaRange". I'm wondering if referencing .Cells and a .Range at the same time could be an issue? i.e.,

[LEFT][COLOR=#333333][FONT=monospace]For Each FormulaCell In FormulaRange
[/FONT][/COLOR][/LEFT]

Instead of

[LEFT][COLOR=#333333][FONT=monospace]For Each FormulaCell In FormulaRange.Cells
[/FONT][/COLOR][/LEFT]

Also, what is "Formula Cell"?

 

[Fluff]

Can anyone help as to whys it isnt running

A few questions
Do you have any formulae on that sheet?
Is calculation set to automatic?
Where is the code located?

 

[daddyfoxuk]

Everything works as it should when i run the code maunally... In I:I the formula is =IF(C2=0,"Scan Part",IF(C2="on suspect list","2")) so this triggers I to change to 2 and should send the email from the code. J changes to sent as it should so I know the mail has been sent but it just doesn't send automatically...

Private Sub Worksheet_Calculate() is on bad parts sheet (sheet4)

And

The subMail code has been inserted as a module

Not sure if i've done it correctly...

Please help its the final bit I need to work!!

 

[Fluff]

Is calculation set to Automatic?
If yes run this

Sub reset()
Application.EnableEvents = True
End Sub

and then change a value in C to see if that triggers the code

 

[daddyfoxuk]

Is calculation set to Automatic? how do I check...? ​

<strike></strike>

 

[Fluff]

On the Formulas Tab > Calculation Options

 

[daddyfoxuk]

Yes set to automatic.... any ideas...?