how do you trigger an email from excel using a DATE RANGE

August 3, 2017 at 03:11:19
Specs: Windows 7
I can trigger e-mail from a fixed date, which doesn't work if excel is not open on e.g. 60 days ahead of todays date. How can I trigger an e-mail ONCE, if a workbook is opened at any time between today and 60 days?

Many thanks, Mike


See More: how do you trigger an email from excel using a DATE RANGE

Reply ↓  Report •

#1
August 3, 2017 at 03:35:25
You can use a helper column in which you can set a flag once an email if sent, this way you can do two tests,

1) is the date 60 days ahead of todays date
2) has an email already been sent

If both conditions are true the nothing will happen

if condition 1 is true and 2 is false then an email is sent, and the flag is set to something like "Sent"

             A                      B              C
            Date                   Flag
1         01/10/2017    
2         01/08/2017               Sent

As an example, because row two has the 'Sent' flag set it will no longer send another email even if the date is triggered

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#2
August 3, 2017 at 07:30:09
Thanks for the input. Perhaps I didn't make myself clear, as the main challenge is to get excel to send an e-mail within a date RANGE. So, once todays date is at ANY point within 60 days of an 'overdue' date, then an e-mail will be triggered. The problem of requiring excel to fire an e-mail on a specific date, is that the workbook needs to be open on the 'right' day (ie 60 days before the 'overdue' date).

I currently have 2 columns with 2 different dates( Date A and DateA+60 days), and I wish to trigger the e-mail between the two dates. Alternatively, I need code to work off the first column, as long as the e-mail is triggered any time the file is opened within the 60 days.

How do I set up the flag to indicate an email has been sent - like the idea, but my programming is extremely limited!

Thanks again for the input.


Reply ↓  Report •

#3
August 3, 2017 at 08:12:39
I've never tried this, but what if you used the Windows Task Scheduler to open the Excel file every day, say at 1:00AM?

Then inside the workbook, use your current VBA process, via the Workbook_Open event, to "trigger e-mail from a fixed date" and also add the "Sent" flag to a column on the same Row of any date that triggered the email. If I saw the code you are using to trigger the email, I could probably explain how to add the Sent flag to "that" Row. Generically, it would look like this:

For curRow = 1 to lastRow
 If (Today's Date Is Within Your Date Range) And (Range("B" & curRow) <> "Sent") Then
    Send Email
    Range("B" & curRow) = "Sent" '<----Set Flag
 End If
Next

Since the Sent flag is set after the email is sent, the code will "ignore" that row next time and not send duplicate emails even if the date is within the required range.

Finally, the VBA code would save and close the workbook, where it will wait patiently for the Windows Task Scheduler to open it "tomorrow".

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

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
August 3, 2017 at 08:55:36
Discovered it!:

=if(A1=MEDIAN(A1:A3),"YES","NO")

with A1 = todays date, A2 being e.g. the expiry date, and A3= renewal notice

Many thanks for the input.


Reply ↓  Report •

#5
August 3, 2017 at 09:03:17
DerbyDad03 -

Re the second part of my jigsaw, my current VB code (courtesy Dinesh Takyar) to trigger email is :


Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailitem = OutLookApp.CreateItem(0)

With OutLookMailitem
maildest = “”
For icounter = 1 To WorksheetFunction.CountA(Columns(10))
If maildest = "" And Cells(icounter, 10).Offset(0, -1) = "send reminder" Then
maildest = Cells(icounter, 10).Value
ElseIf maildest <> "" And Cells(icounter, 10).Offset(0, -1) = "send reminder" Then
maildest = maildest & ";" & Cells(icounter, 10).Value
End If
Next icounter

.bcc = maildest
.Subject = “FYI”
.Body = "Reminder: Your PGD is due to expire in the next 60 days or less. Please initiate review and submit through the normal channels. If this process has begun, then please ignore this reminder. Many thanks, the PGD Leads."
.send
End With

Set OutLookMailitem = Nothing
Set OutLookApp = Nothing
End Sub

Where column 10 contains email addresses, and column 9 = "send reminder".

It would be great to be able to truly auto-trigger the reminder letters I'm trying to send, even when excel is 'closed'.



Reply ↓  Report •

#6
August 3, 2017 at 09:24:42
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!

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


Reply ↓  Report •

#7
August 4, 2017 at 00:51:17
Thanks for that DerbyDad03 -


Re the second part of my jigsaw, my current VB code (courtesy
Dinesh Takyar) to trigger email is :

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailitem = OutLookApp.CreateItem(0)

With OutLookMailitem
 maildest = “”
For icounter = 1 To WorksheetFunction.CountA(Columns(10))
If maildest = "" And Cells(icounter, 10).Offset(0, -1) = "send reminder" Then
 maildest = Cells(icounter, 10).Value
 ElseIf maildest <> "" And Cells(icounter, 10).Offset(0, -1) = "send reminder" Then
 maildest = maildest & ";" & Cells(icounter, 10).Value
 End If
 Next icounter

.bcc = maildest
.Subject = “FYI”
.Body = "Reminder: Your PGD is due to expire in the next 60 days or less. Please initiate review and submit through the normal channels. If this process has begun, then please ignore this reminder. Many thanks, the PGD Leads."
.send
End With

Set OutLookMailitem = Nothing
 Set OutLookApp = Nothing
 End Sub

Thanks again, and I'll try your suggestion above.


Reply ↓  Report •

Ask Question