Results 1 to 2 of 2
2015-05-04, 23:44 #1
- Join Date
- May 2015
- Thanked 0 Times in 0 Posts
3 Email notification before expiry dates
As you can see this is a spreadsheet to keep track of various different expiry dates for a number of different staff members. I would like excel to automatically send an email to a few administrators as well as the staff member concerned when a date comes with either 90 days, 60 days, 30 days and on expiry.
The email addresses should be easy to edit, so something like an additional row above the initials like AMD email address Aamd@aaa.aaa.com.
Importantly, this email should only be sent once for every warning field. So once for the 90 day warning, then once again for 60 day warning and again for 30 day and on expiry.
It is not necessary for the workbook to send it without me opening it, but it should automatically send these warning/reminder emails to everyone whenever I open the file, but it must keep a record (maybe a field true/false) that it wont send the email to someone it already sent during a previous session.
I just need someone to add these functions to the attached workbook I already created. This is a regulatory requirement and it must be fool proof. I open the file a few times a week to edit and update the dates as it goes by. It is then posted on a notice board, but it is important to email people as reminders. An outlook calendar reminder would also be great!
It must be easily expandable for additional staff and fields that require reminding.
This is purely to make my life easier as I manage these 10-15 staff members and would have to manually keep an eye on these dates as I am responsible for keeping everyone current.
Good Day "NAME"
This is .....Days reminder
Your Licence is Expiring on "Expiry Date"
Please contact management regarding this
Outlook saved Signature
I am attaching the sheet also if any one can help please
2015-05-20, 20:49 #2
- Join Date
- Aug 2010
- Pa, USA
- Thanked 644 Times in 588 Posts
Attached is a workbook that will cycle through the columns for each employee and examine the expiration dates. It will place a comment in each expiration cell with the warning type (90 day, 60 day, 30 day, expired) and date the email warning was sent. The next time the code is run, if the employee is still within the same expiration period, no warning will be sent. However, if the employee moved to the next warning period (ex 90 day to 60 day) then a new email warning would be sent and the comment updated new warning type.
Here is a screen shot of the email sent. The employee's email address is taken from a comment added to their initials in row 1. The email has a flagged due date above the recipient field while in Outlook Mail, the email is flagged, while the subject and data for the body get their information from the spreadsheet
At any time you can clear the cell comments using the Clear comment button if you want to resend all the emails.