sherman6789
asked on
Add tickler to Access file that automatically emails timed reminders to certain users
Hello,
I have created an Access database (version 2007) that is used and frequently updated by several staff members in the office. In addition to the updated records, there is a list of associated staff members for each record or type of records. In one field there is a frequency of activity listing (Ex.: weekly, monthly, quarterly, etc.) with a start and end date field.
We would like some type of automatic reminder system that looks in the proper field and automatically generates an email message to all staff members listed on the record to remind them to update the database for that record at a given time. We all use Outlook as the email system. Each associated user's email address is listed in a field on each record. Can this be done and if so, how. If possible, we'd like a cc: to also be sent to the group manager and supervisor.
A separate table listing the associated staff mamberss' names, email addresses, etc. can be made if you think that is best.
I will create any necessary fields to put on the form and make several separate email address fields rather than one, if necessary. Each record in the table has its own list of associated staff members who should be reminded of a due date.
Thank you for any assistance that you can give.
I have created an Access database (version 2007) that is used and frequently updated by several staff members in the office. In addition to the updated records, there is a list of associated staff members for each record or type of records. In one field there is a frequency of activity listing (Ex.: weekly, monthly, quarterly, etc.) with a start and end date field.
We would like some type of automatic reminder system that looks in the proper field and automatically generates an email message to all staff members listed on the record to remind them to update the database for that record at a given time. We all use Outlook as the email system. Each associated user's email address is listed in a field on each record. Can this be done and if so, how. If possible, we'd like a cc: to also be sent to the group manager and supervisor.
A separate table listing the associated staff mamberss' names, email addresses, etc. can be made if you think that is best.
I will create any necessary fields to put on the form and make several separate email address fields rather than one, if necessary. Each record in the table has its own list of associated staff members who should be reminded of a due date.
Thank you for any assistance that you can give.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To: Helen_Feddema:
Thank you for your prompt reponse to my question. This looks great; however, I am probably not knowledgable enough to use it right away and understand the major parts of it. The code should be placed where. How is it triggered? Does someone open the database and click a button then the program evaluates the reminder date then determines when and if a message should be sent? Do I put the email addresses in a particular field or fields.
The staff members who will look at the database will input update information in a given record. If the reminder date "hits", the program will send a message to all of the people on the list for that particular record only and not to everyone. I hope that this makes sense. If it does not, I will re-word it.
Thanks again.
Thank you for your prompt reponse to my question. This looks great; however, I am probably not knowledgable enough to use it right away and understand the major parts of it. The code should be placed where. How is it triggered? Does someone open the database and click a button then the program evaluates the reminder date then determines when and if a message should be sent? Do I put the email addresses in a particular field or fields.
The staff members who will look at the database will input update information in a given record. If the reminder date "hits", the program will send a message to all of the people on the list for that particular record only and not to everyone. I hope that this makes sense. If it does not, I will re-word it.
Thanks again.
ASKER
Thanks to Helen_Feddema,
I am about to close this thread and I believe you have supplied me with the information and examples that I need to complete the project. I am still studying the information but have to work on other projects at the same time. Rather than keep the thread open, I am awarding the points to you with my sincere appreciations. I will probably get back to this project within two weeks. If I have a clarification question, I will try to post it then.
Thanks Again,
WRS
I am about to close this thread and I believe you have supplied me with the information and examples that I need to complete the project. I am still studying the information but have to work on other projects at the same time. Rather than keep the thread open, I am awarding the points to you with my sincere appreciations. I will probably get back to this project within two weeks. If I have a clarification question, I will try to post it then.
Thanks Again,
WRS
ASKER
Your solution is excellent. I will reply as soon as I am able to return to this task within two weeks.
Thanks.
WRS
Thanks.
WRS
In the sample database, there is a Create Reminder button on a form that fires the procedure. You would need to modify the code to first create a filtered recordset, including just the employees who need the remainder, for use in the CreateReminders procedure. You could also call CreateReminders from (say) the AfterUpdate event of a form, or perhaps BeforeUpdate, with a confirmation message.
ASKER
Thank you.
I am capturing all of this information and will try to use it within the next two weeks. I appreciate your inputs and feel that I will be able to succeed in completing this project.
WRS
I am capturing all of this information and will try to use it within the next two weeks. I appreciate your inputs and feel that I will be able to succeed in completing this project.
WRS
Open in new window