I am currently attempting to use VBA to automatically send out an email once the sum of an entire column is greater than 420 and less than 430 (two criteria). Right now, I have it so that an email is sent if the value of a cell within that column is greater than 420 and less than 430 (ex: 425), but I can't seem to redirect the code to look at the sum of the entire column rather than only a cell meeting this criteria within the column.

If I were to use multiple cells with values that add up to meet this criteria (ex: one cell at 415 and another cell at 7), the VBA code doesn't recognize that the sum of these two cells meet the criteria.

Any input is appreciated! Thank you

Code:

Dim xRg As Range
'Update by Extendoffice 2018/3/7

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Columns("C"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value >= 420 And Target.Value <= 430 Then
        Call Mail_small_Text_Outlook
    End If
End Sub

Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "The Employee named in the Subject of this email has exceeded 420 FMLA Hours. Please act accordingly. Thank you"
    On Error Resume Next
    With xOutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = [B2].Value
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

CodePudding user response：

I was able to trigger a msgbox, if the sum in column A exceeds 100. May be, this logic will work for you.

Private Sub Worksheet_Change(ByVal Target As Range)

     If Target.Cells.Count > 1 Then Exit Sub
     Set xRg = Intersect(Columns("A"), Target)
     If xRg Is Nothing Then Exit Sub
     Dim GetSum As Double
     GetSum = Application.WorksheetFunction.Sum(Target.Parent.Range("A:A"))
     If GetSum > 100 Then
         MsgBox "Triggered.", vbInformation
     End If

End Sub