Tech Support Guy banner
Status
Not open for further replies.

Send Email Reminders From Excel to Outlook When Triggered?

Solved 
15K views 49 replies 3 participants last post by  Adrian888 
#1 ·
Hi All,

I am new to VBA and although there are many links in the forum regarding the topics of using Excel to send Email reminders to Outlook, my requirement requires an additional option which i do not know how to program to make it work. I hope I can be assisted.

I am currently using Outlook & Excel 2010, Windows 7.

Using the attached test example, I have created a spreadsheet which is used daily. It requires a reminder email to be automatically sent out ONLY if the following is triggered.

Row H (Send Reminder) must show YES, then it will only send on the date shown on Row G (Due Date). However, if Row H shows NO, it will not send even though Row G has Due Dates.

The body of the reminder message would say:

Subject: Reminder

The project assigned to you under reference number, "cell D3" in the name of "from cell E3" for the confirmation date of "from cell N3" is now G3 - C3 days old.

If this has been completed, please ignore.
 

Attachments

See less See more
#3 ·
Thanks for providing all the links. Unfortunately, I do not have VBA skills and the sample codings i have no idea what affects what and how it can be incorporated in my cell ranges.

It's like a novice driver given to the keys to the Ferrari and all the instructions are in Italian. lol
 
#4 ·
So from your workbook I have written the following code.

Just a question

What is meant to trigger this event?

will it be

1) When the workbook is open
2) when something changes on the workbook
3) after a period of time say every two hours

Dim uRange
Dim lRange
Dim BCell As Range
Dim iBody As String
Dim iTo As String
Dim iSubject As String
Dim DaysOverdue

Sub SetEmailParams()

Set uRange = Sheet1.Range("H3")
Set lRange = Sheet1.Range("H" & Rows.Count).End(xlUp)

iBody = Empty
iSubject = Empty
iTo = Empty

For Each BCell In Range(uRange, lRange)

If BCell.Value = "YES" Then

If DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(Range("G3"), "dd/mm/yyyy")) <= 0 Then

DaysOverdue = DateDiff("d", Format(BCell.Offset(0, -5)), Format(BCell.Offset(0, -1)))

iTo = BCell.Offset(0, -5).Value
iSubject = "Reminder"
iBody = "The project assigned to you under reference number " & BCell.Offset(0, -4) & " in the name of " & BCell.Offset(0, -3) & " for the confirmation date of " & BCell.Offset(0, 6) & " is now " & DaysOverdue & " days old."

SendEmail
End If

End If

Next BCell

End Sub

Private Sub SendEmail()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

On Error Resume Next
With OutMail
.To = iTo
.CC = ""
.BCC = ""
.Subject = iSubject
.Body = iBody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 
#6 ·
Hi Adrian,

No worries mate anytime!

I have attached the file with the code, but will also paste the code below for everyone elses benefit too. Hope this helps.

When you open the workbook (you may have to enable macros) it will go through the list and start to generate emails if 'Send Reminder' is set to 'YES' and the due date has past the 'Assigned Date'

*****NOTE****

Currently this code will generate and DISPLAY the emails, if you want them to automatically send then please change '.Display' to '.Send'

Module Code
Code:
 Dim uRange
Dim lRange
Dim BCell As Range
Dim iBody As String
Dim iTo As String
Dim iSubject As String
Dim DaysOverdue

Public Sub SetEmailParams()

Set uRange = Sheet1.Range("H3")
Set lRange = Sheet1.Range("H" & Rows.Count).End(xlUp)

iBody = Empty
iSubject = Empty
iTo = Empty

For Each BCell In Range(uRange, lRange)

If BCell.Value = "YES" Then

If DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(Range("G3"), "dd/mm/yyyy")) <= 0 Then

DaysOverdue = DateDiff("d", Format(BCell.Offset(0, -5)), Format(BCell.Offset(0, -1)))

iTo = BCell.Offset(0, -6).Value
iSubject = "Reminder"
iBody = "The project assigned to you under reference number " & BCell.Offset(0, -4) & " in the name of " & BCell.Offset(0, -3) & " for the confirmation date of " & BCell.Offset(0, 6) & " is now " & DaysOverdue & " days old."

SendEmail
End If

End If

Next BCell

End Sub

Private Sub SendEmail()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

On Error Resume Next
With OutMail
.To = iTo
.CC = ""
.BCC = ""
.Subject = iSubject
.Body = iBody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send to automatically send without displaying
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
Code for 'ThisWorkbook"

Code:
Private Sub Workbook_Open()
    SetEmailParams
End Sub
 

Attachments

#8 ·
No problem, what I have done is added 'Reminder Sent' in column 'P' so when a reminder is sent, it will time/date stamp that row with the time and date it was sent.

only one line of code was added to 'SetEmailParams'

Updated file also attached

Code:
BCell.Offset(0, 8).Value = Now()
Hope this helps
 

Attachments

#10 ·
Hi All,

I hope DA can help me again. I thought i had this resolved until my co-workers asked me to add or re-arrange some columns. I tried editing the macro to accommodate the change but nothing happens as in no error message nor email notifications. I'm not sure whether i had coded properly. Please find attached the modified spreadsheet. Please note that the workbook only shows February but ultimately, it would have worksheets for each of the remaining months.
 

Attachments

#11 ·
Hi,

There we multiple issues with this workbook which I have now resolved.

The key issues were

1) If column 'M' was 'YES' but column 'L' was empty then it would throw an error
2) Because you have changed the column arrangements the email body was not being constructed correctly

Please see attached amended workbook.

Please let us know if it works
 

Attachments

#15 ·
Here is the modified version.

I have created some new sheets named

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

I copied the data from Feb into each sheet and modified some of the dates (had to overwrite your formulas). I then ran the code and it looped through each sheet, checked each item in column M and generated emails accordingly.

I am using an array to set the name of each sheet that needs to be worked on. You can see this in Sub Setup. Note that each sheet name will have to be exactly as in the array list, I noticed you had something like 'Apr 2' this will not work as my code is right now, you will need to change the array list in Setup to match the sheet names in your workbook.

Also note that if you want the emails to send automatically without being displayed, change the .Display to .Send

Let us know how this works for you
 

Attachments

#18 ·
Actually, this is one part of the spreadsheet which has been resolved. I was also thinking of using a separate email advisory within the spreadsheet but this time to my "suppliers" using their email addresses manually inputted (instead of using drop down menu) found in Cell range V. It would be a simple message confirming receipt of supplies by noting the date received and quoting the request number.

However, I'm not sure how to implement since the scenario is different as in not all suppliers have email addresses. Since I won't be using a YES/NO option to control whether the email goes out or not, I also need to ensure the message does go out to that supplier only once since the spreadsheet is constantly updated throughout the day. When sending, i'm not sure whether it should be done when saving the spreadsheet or send every 15 minutes as an example.

Your thoughts, please.
 
#19 · (Edited)
Try the attached, I have added a new sheet called "Email" which has a button at the top. This button when pressed will

Loop through all sheets (Jan - Dec)
Look at column G to determine if it has a value
Then Check column V to ensure it has a value
Then look at column Z and compare it to todays date to ensure only one email is sent per day

if all the criteria above are met then an email is generated to the supplier, including the request number is the subject line and body.

NOTE
I have added a new column (only in Sept you can add it to the rest of the sheets) to capture the date the email to the supplier was sent.

Let us know how this works for you.
 

Attachments

#20 · (Edited)
It works great.

The items I would like to amend are:

* Move the time sent from column Z and insert to Column W. It should show time and date please. The other columns would shift to the right.
* Please refer to the attached. If you can update the outgoing message as formatted, it would finally complete my objectives.

Thanks again.
 

Attachments

#21 ·
Hi,

So now Column W has the date/time stamp for when the email is sent to the supplier, please ensure the column is formatted to show date and time, you can do tis by choosing a custom format and selecting the appropriate option.
All other columns have moved to the right

The email body has been updated to display as per your attachment.

Please note I have only modified the 'Sep' sheet you will need to move the headings from Z to W on all other sheets
 

Attachments

#24 ·
Unfortunately, i just noticed that the reminder email is going out the same day that was entered. Example: Enter it today (May 11). Due Date would be 5 business days (May 18). Yet, the reminder email is generated saying that the request is now 7 days late.
 
#25 · (Edited)
Please use sheet 'Sep' from now on so that we are on the same page, please keep in mind I am currently working on around 8 different macros for work, home and other forums so I can get a little confused. I have changed all Values on all sheets to 'No' bar 'Sep' so please use this for testing purposes.

In sheet 'Sep' I have the following:

Due Date (L7) = 11/05/2016
Assigned Date(F7) = 18/05/2016

What should happen when the macro is run, which dates should be checked to determine if an email is generated and what should the email say

currently the macro is checking the Due Date (11/05/2016) and admittedly the marco is producing an email saying that the task is 7 days old, which is happening because it is comparing the Due Date with the Assigned Date

Something is obviously wrong here. if you can give me steps to how you would compare the dates and what you expect the outcome to be I can update this accordingly. please use cell references when referring to dates so I know what is what and where.
 

Attachments

#26 · (Edited)
Thanks for your patience and hardwork in helping me with this.

I have updated & uploaded the September spreadsheet with the following sample:

Assigned Date: iMay 11 in (F7) + 30 business days before due date & (F8) May 11 +5 business days before due.
Due Date: should be L7 = June 22, 2016 and L8 due date should be May 18.

The email reminder should not be generating until the spreadsheet is opened on the day of May 18th and June 22nd hit respectively.
The message should just remind that they're 30 or 5 days late. However, it is generating a reminder today and calculating it as due.
---
Dear Bob YAK,

The project assigned to you under reference number BC 2017-01 in the name of a for the confirmation date of 1/1/2016 is now 136 days old.
---
Dear Don CAN,

The project assigned to you under reference number BC 2017-02 in the name of b for the confirmation date of 01/201/2016 is now 171 days old.
---
I forgot to mention that it seems to work perfectly at home. It is only at work that i am getting these errors. I did not do any changes in the macros or columns except width size.

I am using the same version Outlook and Excel in both cases. Only difference is at work, i'm on their network.

Lastly, is it possible to have the CC: just for this email notice below, please? I tried but it didn't work for me.

iTo = BCell.Offset(0, -6).Value
iSubject = "Reminder"
iBody = "The project assigned to you under reference number " & BCell.Offset(0, -4) & " in the name of " & BCell.Offset(0, -3) & " for the confirmation date of " & BCell.Offset(0, 6) & " is now " & DaysOverdue & " days old."
 

Attachments

#27 ·
Ok I think I get it now (hopefully)

if the workbook is opened anywhere between 18 May and 22 June

An email should be generated telling you how many days late you are between the ASSIGNED Date and the DUE Date?

So if

Assign Date F7 = 11/05/2016
and
Due date L7 = 22/06/2016

The difference between these two dates in workdays is 31 days according to the formula '=NETWORKDAYS(F7,L7,0)'

So the email should be notifying them of how many days late they are from the assigned date, in this case 31?

If this is correct i'll make the changes tomorrow as I am a little pushed for time today.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top