asked on

Auto email from excel based on cell value

In the following code mail will be generated if the cell value is grater than 200.
But i need to send mail if the cell value is less than 200.

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

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = 200

'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("B8")

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

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Why are you looping through a range that is only one cell?

Also, you can set some variables as Constants

200 is an Integer value, not a Double

Option Explicit

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim MyMsg As String
    Const NotSentMsg As String = "Not Sent"
    Const SentMsg As String = "Sent"
    'Below the MyLimit value it will run the macro
    Const MyLimit As Integer = 200

    'Set the range with the Formula that you want to check
    '    Set FormulaRange = Me.Range("B8")

    On Error GoTo EndMacro:
    With Me.Range("B8")
        If Not IsNumeric(.Value) Then
            MyMsg = "Not numeric"
        Else
            If .Value < MyLimit Then
                MyMsg = SentMsg
                If .Offset(0, 1).Value = NotSentMsg Then
                    Call Mail_with_outlook1
                End If
            Else
                MyMsg = NotSentMsg
            End If
        End If
        Application.EnableEvents = False
        .Offset(0, 1).Value = MyMsg
    End With

ExitMacro:
    Application.EnableEvents = True
    Exit Sub

EndMacro:

    MsgBox "Some Error occurred." _
         & vbLf & err.Number _
         & vbLf & err.Description
    Resume ExitMacro
End Sub

Open in new window

Roy Cox

Kishore, can provide a response please.

Roy Cox

There's two answers, both have picked up the error, my answer also suggests improvements in the code

Wayne Taylor (webtubbs)

Question answered.