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

 

[daddyfoxuk]

Hi, one final question... i have the value of B now in the subject of the email using Call Mail_with_outlook2(.Offset(, -7).Value) and .Subject = MyVal which is great, how would i add A into that also including "Suspect Part Details" in the subject! Thanks again for your previous help!

 

[Fluff]

Depends on how you want it to look.

 

[daddyfoxuk]

I would like "A" along with "B" this already works with Call Mail_with_outlook2(.Offset(, -7).Value) in the subject along with the wording "Suspect Notification"

 

[Fluff]

How about

Call Mail_with_outlook2(.Offset(, -8).Resize(, 2))

Sub Mail_with_outlook2(Rng As Range)
   .Subject Rng(1).Value & " Suspect Notification " & Rng(2).Value

 

[daddyfoxuk]

Bingo!!! I've swapped the order around Suspect at the beginning and added a space in between, works perfectly tho! Thanks Again!!!

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[daddyfoxuk]

Hi, Sheets still working however i would like to include further cells to be copied into the email.... As per below that is working how would i add cells further along the sheet...? Cells E and F or 5 and 6 not sure how it works, any help woould be great!

Sub Mail_with_outlook2(Rng As Range)
.Subject Rng(1).Value & " Suspect Notification " & Rng(2).Value

 

[daddyfoxuk]

Glad it's sorted & thanks for the feedback

Hi, Sheets still working however i would like to include further cells to be copied into the email.... As per below that is working how would i add cells further along the sheet...? Cells E and F or 5 and 6 not sure how it works, any help woould be great!

Sub Mail_with_outlook2(Rng As Range)
.Subject Rng(1).Value & " Suspect Notification " & Rng(2).Value