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!!!

 

[Fluff]

What if you change it to

                    If .Offset(0, 1).Value <> SentMsg Then
                        Call Mail_with_outlook2
                    End If

 

[daddyfoxuk]

Bingo! something as simple as that Thank you!! How would i add the cell number into the email body i.e each time a mail is sent it would copy the data in row "B"

 

[Fluff]

Try

Call Mail_with_outlook2(.offset(,-7).value)

and

Sub Mail_with_outlook2(MyVal as string)

Then do whatever you want with the value

 

[daddyfoxuk]

Where would this go, ive tried it but doesn't seem to work...?!

 

[daddyfoxuk]

I was going for something like .Subject = Range("B:B").Value

 

[Fluff]

Where would this go, ive tried it but doesn't seem to work...?!

Just change those two lines as I have shown

 

[daddyfoxuk]

so ive changed the lines, how would i get it to show the value in colum B with each email sent..? Tried a few things but cant seem to make it work... Thanks.

 

[Fluff]

try

.Subject=MyVal

 

[daddyfoxuk]

You've only gone and solved it Your a star, thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback