Solved How To Automatically Send Email When Conditions Are Met

June 15, 2017 at 06:52:02
Specs: Windows 7
Hi
I am hoping someone will be able to help me with the following

I am trying to write code that will send an email automatically when 2 conditions are met in excel. The first condition being, is this, does the required until date = todays date and the second is, = Not sent. The criteria is, if "= today & Not Sent send email" and then change to Sent also add names to body of email that require access removing, otherwise do nothing.
I have my names in Col D , Not Sent/Sent in Col F & required until date in Col H. I have been trying to adapt the code below that I found online which by the look of it many others have found like the chap asking a similar questtion in this forum and like him am stuck. Unfortunately, it uses a numerical limit instead of the criteria I would like to use. It's currently set to send an email as soon as a number is = to or > is reached in a particular Col which before i started making changes was 200. the code is pretty near what i would like now i have made changes but i am a real novice at this and not sure how to address it. I have got it to a stage where it produces the email and can manually input the names to remove but that is all i dont know where to go with the rest i require.

Many thanks in anticipation of your help

My Data

Col D Col F Col H
Customer Name System Required Email Sent Raised On Required Until
Pete Hughes UAT Sent 09/05/2017 13/05/2017
Ron Copeland UAT Not Sent 16/05/2017 15/06/2017
David Tyler UAT Sent 22/05/2017 27/05/2017
Joe Hill UAT Sent 23/05/2017 27/05/2017
Vitor Freitas UAT Sent 24/05/2017 31/05/2017
Dan Jacklin UAT Sent 24/05/2017 27/05/2017
Paul Needham UAT Sent 24/05/2017 27/05/2017
Steve Hurt UAT Sent 31/05/2017 05/06/2017
Steve Hurt Sent 13/06/2017
Steve Hurt Not Sent 15/06/2017
Ron Copeland Sent 13/06/2017
Dan Jacklin Not Sent 15/06/2017
Test4 Not Sent 17/06/2017
Dan Jacklin Sent 13/06/2017
David Tyler Sent 13/06/2017
David Tyler Not Sent 15/06/2017
Joe Hill Sent 13/06/2017
Pete Hughes Not Sent 16/06/2017

My worksheet code:

Private Sub Worksheet_Calculate()
Dim FormulaRange As range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As String

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'If date equal MyLimit value it will run the macro
MyLimit = Date

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

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsDate(.Value) = Date Then
MyMsg = NotSentMsg
Else
If Date = MyLimit Then
MyMsg = SentMsg
If .Offset(6, 8).Value = SentMsg Then
Call Mail_with_outlook2
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(6, 8).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

** I have made changes to the original code as far as my experience goes

My Module mailcode:

Sub Mail_with_outlook2()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strto = "ron@something.abc"
strcc = "pete@something.abc"
strbcc = ""
strsub = "Remove Access"
strbody = "Hi Guys" & vbNewLine & vbNewLine & _
"Please remove UAT access for the following Users : " & _ **** Formula *****
vbNewLine & vbNewLine & "Kind Regards"

****** Formula ****** i need a formula here that puts the names of the people who require access removing in the email.


With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
'You can add a file to the mail like this
'.Attachments.Add ("C:\test.txt")
.Display ' or use .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I hope this is ok, and again thanks in anticipation of your help and hope i have not transgressed any etiquette


See More: How To Automatically Send Email When Conditions Are Met

Report •

✔ Best Answer
June 29, 2017 at 05:33:24
I'm glad it worked out for you. The technique of continually extending a string by concatenating new data elements onto the existing string is fairly common and very powerful. Here's a tip:

You will notice a vbNewLine is added along with each new name. In this case, that vbNewLine is left as part of the nameList string when it is used in the strBody variable so that there is line feed after the list.

Typically, the extra characters at the end of the string that is built are stripped off. For example, let's say that I wanted to build a string of comma-separated values, e.g.

"Bob, Mary, Sue, Mark"

The code would look something like this, since I need to add a comma and space after each name:

  For nxtCell = 1 to 5
    nameList = nameList & Range("A" & nxtCell) & ", "
  Next

The problem is that the final string would look like this:

"Bob, Mary, Sue, Mark, " <--- Note the comma and space at the end. (Sloppy!)

The workaround is to build a temporary string and then strip off the last 2 characters:

  For nxtCell = 1 To 5
    tmp_nameList = tmp_nameList & Range("A" & nxtCell) & ", "
  Next
     nameList = Left(tmp_nameList, Len(tmp_nameList) - 2)

That leaves you with "Bob, Mary, Sue, Mark"

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

message edited by DerbyDad03



#1
June 15, 2017 at 06:55:29
Sorry I did edit the data so it was readable but on submitting has got scrambled, have i done something wrong.

Report •

#2
June 15, 2017 at 07:01:55
oops forgot to add the that it sends the email when the workbook is opened.

Report •

#3
June 15, 2017 at 07:28:00
Moi ron, this seems like quite a project and since this is user helps user forum It might take a while before you get a decent answer. So be sure to check your thread every day or so.

Because i ain't the best at VBA im not gonna post any code.

Good Luck :)

Simple solutions are often the best


Report •

Related Solutions

#4
June 15, 2017 at 07:46:53
Many Thanks hidde663 will check everyday

Report •

#5
June 15, 2017 at 07:59:28
re: "Sorry I did edit the data so it was readable but on submitting has got scrambled, have i done something wrong."

Please click on the blue line at the end of this post and read the instructions on how to post example data and 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!

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


Report •

#6
June 15, 2017 at 09:28:20
           D                 E             F             G              H
 1    Customer Name   System Required   Email Sent   Raised On	 Required Until
 2    Kay Needham	   UAT	          Sent	     09/05/2017	   13/05/2017
 3    Steve King	   UAT	        Not SenT     16/05/2017	   16/06/2017
 4  Sharren Kjenstad       UAT	          Sent	     22/05/2017	   27/05/2017
 5     Joe Hill	           UAT	          Sent	     23/05/2017	   27/05/2017
 6   Vitor Freitas	   UAT	          Sent	     24/05/2017	   31/05/2017
 7   Dan Jacklin	   UAT	          Sent	     24/05/2017	   27/05/2017
 8   Paul Needham	   UAT	          Sent	     24/05/2017	   27/05/2017
 9   Steve Hurt	           UAT	          Sent	     31/05/2017	   05/06/2017
10   Steve Hurt		   UAT            Sent                     13/06/2017        
11   Steve Hurt		   UAT          Not Sent		   15/06/2017
12  Ron Copeland           UAT            Sent		           13/06/2017
13  Dan Jacklin		   UAT          Not Sent		   15/06/2017
14    Test4		   UAT          Not Sent		   17/06/2017
15  Dan Jacklin		   UAT            Sent		           13/06/2017
16  David Tyler		   UAT            Sent		           13/06/2017
17  David Tyler		   UAT          Not Sent		   15/06/2017
19   Joe Hill		   UAT            Sent		           13/06/2017
20  Pete Hughes		   UAT          Not Sent		   16/06/2017 


Report •

#7
June 15, 2017 at 10:11:19
Thank you for reposting your data in a formatted manner.

Now, may I ask that you repost your VBA code using the same process, so that the indents and other formatting is retained? In most cases, assuming that the code is formatted properly in the VBA editor, simply pasting it between the pre tags will retain its formatting.

Thanks!

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


Report •

#8
June 16, 2017 at 05:05:18
Hi There

Yes I will do that now, sorry for the delay had to dash off yesterday and only just got back, apologies again for not replying sooner.


Report •

#9
June 16, 2017 at 05:49:57
 this is my worksheet code

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 String

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'If date equal MyLimit value it will run the macro
    MyLimit = Date

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

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsDate(.Value) = Date Then
                MyMsg = NotSentMsg
            Else
                If Date = MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(6, 8).Value = SentMsg Then
                        Call Mail_with_outlook2
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(6, 8).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

********************

this is my module mail code

Option Explicit

Public FormulaCell As range

Sub Mail_with_outlook2()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strto = "ron@something.abc"
    strcc = "pete@something.abc"
    strbcc = ""
    strsub = "Remove Access"
    strbody = "Hi Guys" & vbNewLine & vbNewLine & _
              "Please remove UAT access for the following Users : " & _
              vbNewLine & vbNewLine & "Kind Regards"

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        'You can add a file to the mail like this
        '.Attachments.Add ("C:\test.txt")
        .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Thankyou again in anticipation


Report •

#10
June 16, 2017 at 06:22:46
Thanks for posting that.

I'm a bit confused as to what works for you and what doesn't. Please keep in mind that you are intimately familiar with your requirements and process while we are simply looking at somebody else's code and data. You have a distinct advantage. ;-)

At this point, are you only asking for a method to build a list of Names from Column D to put into the body of an email as follows?

strbody = "Hi Guys" & vbNewLine & vbNewLine & _
              "Please remove UAT access for the following Users : " & _
              ****List Of Names Go Here**** & "Kind Regards"

If so, what is the exact criteria associated with the names that should be added to that list?

If there is something else that needs to be fixed, please let us know.

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


Report •

#11
June 19, 2017 at 03:37:34
Hi There DerbyDad03

Yes sorry i have just read what i put and confused myself, This novice will begin again, what i would like to do is when the workbook opens is for the macro to check that the Required Until date is a match to todays date and also that the status is Not Sent if these 2 conditions are met then create the email and add the names that correspond with the 2 conditions.

Thankyou again for taking a look i hope my new explanation is more helpful.


Report •

#12
June 19, 2017 at 08:24:02
This code will build a string containing the names that meet the 2 criteria mentioned above. It will then use the string as part of the strbody variable for the email.

Let me know what you think.

Sub BuildNameList()
Dim nameList As String, strbody As String
Dim rDate As Range
'Build String of names that match criteria
 For Each rDate In Range("H2:H19")
   If rDate = Date And rDate.Offset(0, -2) = "Not Sent" Then
     nameList = nameList & rDate.Offset(0, -4) & vbNewLine
   End If
 Next
'Incorporate nameList string into strbody for email
  strbody = "Hi Guys" & vbNewLine & vbNewLine & _
            "Please remove UAT access for the following Users : " & _
            vbNewLine & vbNewLine & nameList & _
            vbNewLine & "Kind Regards"
'Display sample
  MsgBox strbody
End Sub

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

message edited by DerbyDad03


Report •

#13
June 29, 2017 at 01:33:12
Hi DerbyDad03

Sorry its been a while getting back to you, but was on annual leave.

This worked an absolute treat so much so that I cleaned up my code and got rid of coding that was not necessary. Many many thanks.


Report •

#14
June 29, 2017 at 05:33:24
✔ Best Answer
I'm glad it worked out for you. The technique of continually extending a string by concatenating new data elements onto the existing string is fairly common and very powerful. Here's a tip:

You will notice a vbNewLine is added along with each new name. In this case, that vbNewLine is left as part of the nameList string when it is used in the strBody variable so that there is line feed after the list.

Typically, the extra characters at the end of the string that is built are stripped off. For example, let's say that I wanted to build a string of comma-separated values, e.g.

"Bob, Mary, Sue, Mark"

The code would look something like this, since I need to add a comma and space after each name:

  For nxtCell = 1 to 5
    nameList = nameList & Range("A" & nxtCell) & ", "
  Next

The problem is that the final string would look like this:

"Bob, Mary, Sue, Mark, " <--- Note the comma and space at the end. (Sloppy!)

The workaround is to build a temporary string and then strip off the last 2 characters:

  For nxtCell = 1 To 5
    tmp_nameList = tmp_nameList & Range("A" & nxtCell) & ", "
  Next
     nameList = Left(tmp_nameList, Len(tmp_nameList) - 2)

That leaves you with "Bob, Mary, Sue, Mark"

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

message edited by DerbyDad03


Report •

#15
July 10, 2017 at 08:19:05
Hi

Thankyou, this makes so much more sense to me now i was having a problem at first getting my head around concatenation now it makes my life so much easier as i can build more into what i want to get out of my data.

Again Many thanks


Report •

Ask Question