The current spreadsheet looks for the Total to be greater than 3 and send an email to that specific individual and then marks the individual as notified in order not to notify them again if someone else hits 4 or more. I would like to adjust the code to send out the current macro email to anyone that has 4 or 5 in the total column and send a separate email when the individual hits 6 or above. I think I can make a separate email macro with ease by just copying the current macro and changing the name and the body of the email. But I am stumped with the code.
Email based on conditions
- sreising
- Thread is marked as Resolved.
-
-
I wonder if a SELECT CASE would work ?
-
Something like this
Code
Display MorePrivate Sub Worksheet_Calculate() Dim FormulaRange As Range Dim NotSentMsg As String Dim MyMsg As String Dim SentMsg As String Const MyLimit As Double = 3 NotSentMsg = "" SentMsg = "Notified" ' ' 'Above the MyLimit value it will run the macro ' MyLimit = 3 'Set the range with Formulas that you want to check Set FormulaRange = Me.Range("P4:P100") ''/// don't use umtil testing has finished ' On Error GoTo EndMacro: For Each FormulaCell In FormulaRange.Cells With FormulaCell If IsNumeric(.Value) = False Then MyMsg = "Not numeric" Else Select Case FormulaCell.Value Case Is > MyLimit MyMsg = SentMsg If .Offset(0, 1).Value = NotSentMsg Then Call Mail_with_outlook2 End If Case Else MyMsg = NotSentMsg End Select 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 -
ok this is helpful
-
Pleased to help.
Post back if you need further help.
Visit my web site, www.excel-it.com, for more examples and some helpful articles.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!