Automated Reminder Email


 

A gent named Joni Räsänen contacted me on Twitter with a question/request. Here’s Joni’s tweet.

The solution to Joni’s request is to use Outlook’s built-in appointment reminders to trigger sending the email. To do this, we’ll trap the event that fires each time a reminder goes off. The code will check to see if the item that triggered the reminder is an appointment and if it’s a member of a certain category. Checking the category will allow Joni to set reminders on any appointment, including those he doesn’t want an email sent for. If the appointment is a member of the designated category, then the code will create and send a predefined message. If the appointment is not a member of the designated category, then the code will exit without doing anything.

Joni will specify the name of the category he wants to key on, as well as the subject, addressees, and body of the message the code will send. The code will send the message when the reminder goes off, allowing Joni to set different lead times for different appointments. For example, Joni could set one appointment’s reminder to 15 minutes and another appointment’s reminder to 30 minutes. The reminder email would go out 15 minutes before the start of the first appointment and 30 minutes before the start of the second. Note that the code will send more than one message if you snooze the reminder and it fires a second time.

Requirements.

This code should work with Outlook 2003 and later.

Instructions.

Follow these instructions to add the code to Outlook.

Outlook 2007

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click ToolsTrust Center
  6. Click Macro Security
  7. Set Macro Security to Warnings for all macros
  8. Click OK
  9. Close Outlook
  10. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.

Outlook 2010/2013

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click File and select Options
  6. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  7. Click Macro Settings
  8. Select either of the two bottom settings (i.e. Notifications for all macros or Enable all macros (not recommended; potentially dangerous code can run). The choice of which to choose is up to you. If you select Notifications, then you’ll be prompted at times to enable macros. If you pick Enable all then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  9. Click Ok until the dialog-boxes have all closed
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
'Declare some constants
Const TRIGGER_CAT = "Email"

'Declare some variables
Dim WithEvents olkReminders As Outlook.Reminders
 
Private Sub Application_Quit()
    Set olkReminders = Nothing
End Sub

Private Sub Application_Startup()
    Set olkReminders = Application.Reminders
End Sub

Private Sub olkReminders_ReminderFire(ByVal ReminderObject As Reminder)
    Dim olkApt As Outlook.AppointmentItem, olkMsg As Outlook.MailItem
    If ReminderObject.Item.Class = olAppointment Then
        Set olkApt = ReminderObject.Item
        If InStr(1, olkApt.Categories, TRIGGER_CAT) > 0 Then
            Set olkMsg = Application.CreateItem(olMailItem)
            With olkMsg
                'On the next three lines edit the subject, recipient's email addresses, and message body as desired.
                .Subject = "Your Subject Goes Here"
                .To = "someone@company.com"
                .Body = "Your message goes here."
                .Send
            End With
            'Remove the following line if you don't want the code to dismiss the reminder
            ReminderObject.Dismiss
        End If
    End If
    Set olkApt = Nothing
    Set olkMsg = Nothing
End Sub

Using the Solution.

  1. Create an appointment.
  2. Set a reminder for the appointment.
  3. Add the designated category to the appointment.
  4. Save the appointment.

When the appointment’s reminder fires, the code will create and send an email. The code will optionally dismiss the reminder.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s