Solved How to send an email from excel if certain conditions are me

February 7, 2017 at 08:29:55
Specs: Windows 7
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


See More: How to send an email from excel if certain conditions are me

Report •

✔ Best Answer
February 8, 2017 at 06:39:23
Thank you for reposting the code with the pre tags. That really helps.

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:

If .Value > MyLimit Then

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:

If .Value > MyLimit Then

with

   If .Value > 42 And FormulaCell.Offset(0, -4) = "Y" Or _
      .Value > 14 And FormulaCell.Offset(0, -4) = "N" Then

You 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.



#1
February 7, 2017 at 10:27:06
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

Second, I noticed that the code you posted is a Worksheet_Calculate macro. That means that it is going to run every single time the worksheet calculates. If you have Calculation set to Automatic, then the code is going to run every single time you make any change, regardless of whether or not your Target cells are involved. Is that what you want? I see the risk of sending the same emails over and over again, even when nothing has changed in Columns G or K.

As for your question, this IF seems to meet your requirements:


   If .Value > 42 And FormulaCell.Offset(0, -4) = "Y" Or _
      .Value > 14 And FormulaCell.Offset(0, -4) = "N" Then
 

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 8, 2017 at 01:05:07
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


Report •

#3
February 8, 2017 at 01:32:58
Hi,

Thank you for your response, I have reposted the code using the link you provided, I hope it's much clearer to see. The code automatically populates "Sent or Not Sent" in Column L. So it does not resend to lines marked "Sent". I have tried the code you provided me, but there seems to be an error somewhere.

I cut and replaced "My Limit = 200" in my code & pasted your If function there but it's coming up with an error. My Excel columns are as below:

G	H	I	J	K	L	M
Y	12/01/2017	31/03/2018		443	Sent	tom@something.abc
Y	12/01/2017	31/03/2018		443	Sent	dave@something.abc
Y	12/01/2017	31/03/2018		443	Sent	ron@something.abc
Y	12/01/2017	31/03/2018		443	Sent	debra@something.abc
Y	12/01/2017	30/01/2017		18	Not Sent	gord@something.abc
Y	12/01/2017	31/01/2017		19	Not Sent	tom@something.abc
Y	31/03/2018	01/02/2017		-423	Not Sent	dave@something.abc


Report •

Related Solutions

#4
February 8, 2017 at 06:39:23
✔ Best Answer
Thank you for reposting the code with the pre tags. That really helps.

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:

If .Value > MyLimit Then

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:

If .Value > MyLimit Then

with

   If .Value > 42 And FormulaCell.Offset(0, -4) = "Y" Or _
      .Value > 14 And FormulaCell.Offset(0, -4) = "N" Then

You 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.


Report •

#5
February 8, 2017 at 07:55:23
Thank you very much for your response, your code has worked a treat! I'll go through the material you referred, it will surely help for the future.

Thanks again!


Report •

Ask Question