Hi Guys,

This is my first time posting and I'm am really stuck with how to figure this out. I'm not sure if I've included everything that's helpful so just let me know and I'll post more information.



Okay so basically, I'm creating a macro that will check a range of cells. If they are over 51 I need to automatically send an email out, then when they exceed 201 another email needs to be sent, this happening again for 301 and 501.

This is what I've got so far:

Option Explicit

Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
Dim MyLimit1 As Double
Dim MyLimit2 As Double
Dim MyLimit3 As Double

NotSentMsg = "N/A"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = 51
MyLimit1 = 201
MyLimit2 = 301
MyLimit3 = 501

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

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "N/A"
Else

If .Value > MyLimit3 Then
MyMsg = SentMsg
If .Offset(9, 0).Value = NotSentMsg Then
Call Mail_with_outlook4
End If
Application.EnableEvents = False
.Offset(9, 0).Value = MyMsg
Application.EnableEvents = True
Else

If .Value > MyLimit2 Then
MyMsg = SentMsg
If .Offset(7, 0).Value = NotSentMsg Then
Call Mail_with_outlook3
End If
Application.EnableEvents = False
.Offset(7, 0).Value = MyMsg
Application.EnableEvents = True
Else

If .Value > MyLimit1 Then
MyMsg = SentMsg
If .Offset(5, 0).Value = NotSentMsg Then
Call Mail_with_outlook2
End If
Application.EnableEvents = False
.Offset(5, 0).Value = MyMsg
Application.EnableEvents = True
Else

If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(3, 0).Value = NotSentMsg Then
Call Mail_with_outlook1
End If
Application.EnableEvents = False
.Offset(3, 0).Value = MyMsg
Application.EnableEvents = True
Else
MyMsg = NotSentMsg
End If

End If

End If

End If

End If

End With
Next FormulaCell

ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description

End Sub

The issue is that it isn't sending the email and also if the number falls back under one of the 'targets' it doesn't update itself and allow for a email to be sent again if exceeded.


I hope this makes sense and like I said if you need anythign more just let me know.



Thanks in advance,


James