I am looking to write code that will send an out an email automatically if 2 conditions are met in excel. The first condition being, is this a repeat design “Y or N” and the second is the number of days shown in another column. The criteria is, if “Y & over 42 days then send email” or if “N and over 14 days then send email” otherwise do nothing.
I have my repeat design in Col G & Number of days in Col K. I have been trying to adapt the code below that I found online earlier on. Unfortunately, it uses a limit instead of the IF function I would like. It is currently set to send out an email as soon as any number in Col K goes over a 200 day limit, that’s the bit I would like to change.
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 Formulas that you want to check
Set FormulaRange = Me.Range(“K8:K100”)
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
As far as your example data, your column letters don’t appear to line up correctly, but based on your earlier posts, I’ll assume that Column K contains the 443, 18, etc.
Another posting tip:
Since we can’t see your workbook from where we’re sitting, telling us that the VBA code is “coming up with an error” doesn’t give us a lot to work with. VBA can present all sorts of errors, including syntax errors, compile errors, run time errors, application errors and even the dreaded Fatal Error. (Ouch!)
It would help us help you if you told us what the error says and, if possible, which instruction caused the error.
Allow me to offer you this before I address your question:
If you are going to be using VBA, either writing your own code or just trying to figure out how code that you find on the web works, it helps to have some debugging techniques in your toolbox. I suggest that you practice the techniques found in the following tutorial. Not only can these techniques help you find errors in your own code, but they can be used to reverse engineer code that you find elsewhere. I am essentially self taught in VBA and much of what I have learned came from my application of these debugging techniques on working code, which helps me understand how and why the code does what it does.
https://www.computing.net/howtos/sh…
OK, as for your current problem, let’s take a look at what you said:
“I cut and replaced “My Limit = 200″ in my code & pasted your If function there”
In the original code that you posted, the author has a created a variable and named it MyLimit. (S)he then sets that variable equal to 200. Once that is done, VBA will use the value 200 whenever it encounters the MyLimit variable in an instruction.
Further down in the code we see the instruction:
What that instruction is doing is simply checking the cell currently referenced in the For-Next loop to see if it is greater than 200 (or whatever value the author chooses to assign to the MyLimit variable).
In your case, you don’t want the email to be triggered based a single value because you have multiple conditions that must be checked. Therefore, the “If” instruction that decides on whether the email is sent or not has to check for all of those conditions.
What this boils down to is that you put my suggested “If” instruction in the wrong place. What you should have tried is to replace this instruction:
with
If .Value > 42 And FormulaCell.Offset(0, -4) = "Y" Or _ .Value > 14 And FormulaCell.Offset(0, -4) = "N" ThenYou should also clean up the code by deleting any instructions and comments that include the MyValue variable since it is no longer being used.
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.