Solved

Add tickler to Access file that automatically emails timed reminders to certain users

Posted on 2011-02-11
7
1,885 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:sherman6789
  • 4
  • 3
7 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 34872573
I just wrote an Access Archon article on this topic.  It has a sample database that creates emails to be sent at a future date.  You can adapt the sample database for your needs.  Here is a link for downloading it:

http://www.helenfeddema.com/Files/accarch204.zip
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34872606
Here is the Access code that creates tasks with reminders, and the Outlook event procedure that creates the emails when the reminders fire:
Public Sub CreateReminder(dteReminder As Date, strEmployeeName As String, _
   strEmail As String)
'Created by Helen Feddema 27-Dec-2010
'Last modified by Helen Feddema 28-Dec-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim tsk As Outlook.TaskItem
   Dim strMessage As String
   
   'Create task item for sending the mail message later
   strMessage = "When the task reminder fires, an email message will " _
      & "be created and sent"

   Set tsk = appOutlook.CreateItem(olTaskItem)
   With tsk
      .Display
      .Subject = "Time Sheet Reminder"
      .DueDate = dteReminder
      .StartDate = dteReminder
      .Categories = "Reminder"
      .Body = strMessage
      
      'Store info for mail message in unused Task fields
      .BillingInformation = strEmail
      .Mileage = "Please submit a signed time sheet for last week by end of work today"
      .CardData = "Time sheet reminder"
      
      'Set task reminder for date when message should be sent
      .ReminderSet = True
      .ReminderTime = dteReminder
      .Close (olSave)
   End With
   
   strTitle = "Information"
   strPrompt = "Task created; when the task reminder fires, an email message will " _
      & "be created and sent"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle
   
ErrorHandlerExit:
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CreateReminder procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

=========================

Private Sub Application_Reminder(ByVal Item As Object)
'Created by Helen Feddema 28-Dec-2010
'Last modified by Helen Feddema 28-Dec-2010

On Error GoTo ErrorHandler

   Dim msg As Outlook.MailItem
   
   If Item.Categories = "Reminder" Then
      Set msg = Application.CreateItem(olMailItem)
      With msg
         .To = Item.BillingInformation
         .Subject = Item.CardData
         .Body = Item.Mileage
         .BodyFormat = olFormatPlain
         .Save
      End With

   End If
   
   'Uncomment the following line to display the mail message
   'msg.Display
   msg.Send
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in Application_Reminder procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:sherman6789
ID: 34872712
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:sherman6789
ID: 34962381
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
0
 

Author Closing Comment

by:sherman6789
ID: 34962466
Your solution is excellent.  I will reply as soon as I am able to return to this task within two weeks.
Thanks.

WRS
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34962540
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.
0
 

Author Comment

by:sherman6789
ID: 34963627
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now