Mobile Appointment Reminders


 

I wrote this post in response to an idea Fred Cleveland posted to my Suggestions page. Fred explained that he’s not always at his desk when an Outlook appointment reminder pops up, and mused that it would be nice if Outlook could send a reminder to his cell phone about 5 minutes before the start of the appointment. At first glance, that might seem like an odd request given how prevalent smartphones are that sync with our Outlook calendar and remind us of impending appointments no matter where we are. Still, not everyone has a smart phone, and some of those who do may work for a company that does not allow syncing with the corporate email system. Those who don’t have a smart phone probably do have a cell phone that can at least receive text messages. What Fred needs is a solution that can send him an email, if he has a smart phone, or a text message, if he only has a cell phone.

I’ve created a solution for Fred that uses an appointment’s reminder to trigger sending a message to his phone to remind him of the appointment. Fred only wants one reminder, about 5 minutes before the appointment, so I included the necessary logic to keep Outlook from sending a message each time an appointment’s reminder fires. For example, let’s say that Fred has an appointment at 10:00 AM. He set the reminder for that appointment to appear 15 minutes before the appointment starts. At 9:45 AM the reminder pops up on Fred’s computer. That triggers the script which sees that the appointment is more than 5 minutes away and does nothing. At 9:50 Outlook displays another reminder onscreen, and again the script does nothing because the appointment is still more than 5 minutes away. Finally, at 9:55 AM, Outlook displays a third reminder. This time the script sees that the appointment is within 5 minutes of starting and sends a reminder to Fred’s phone. The reminder Fred receives will have three pieces of information: start time, subject, and location. If Fred chooses the email notification approach, then the message he receives will be in HTML format. If instead he opts for a text message, then the message will go out in plain text format and will be briefer and to the point. In order to receive the reminder as a text message, Outlook send an email through an email-to-SMS gateway. Most cellular providers (including AT&T, Sprint, and Verizon) have such a gateway. To see a list of email-to-SMS gateways, please consult this Wikipedia article. It not only shows which providers have gateways, it also shows how to format the email address for each provider. If your cellular provider isn’t listed, then you’ll either have to have a smart phone and use the email option, or find an email-to-SMS gateway you can use.

Hopefully this solution is what Fred’s looking for and will help ensure that he’s reminded of upcoming appointments no matter where he is. Of course, Fred will have to leave Outlook open and running on his computer for this to work. He’ll also need to make sure that every appointment has a reminder set.

Instructions.

Follow these instructions to add the code to Outlook.

The code comes in two parts.

Follow these instructions to add part 1 of the code.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select InsertClass Module
  5. In the Properties panel click on Name and enter clsMeetingReminder
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included a comment where changes are needed or allowed.
  8. Click the diskette icon on the toolbar to save the changes
'On the next line set the value to True if you want the notification as an SMS, False if you want it as an email
Const SEND_AS_SMS = True
'On the next line enter the email address of your SMS account (if using SMS) or of your smartphone's email account
Const EMAIL_ADDR = "someone@company.com"

Private WithEvents olkRmd As Outlook.Reminders

Private Sub Class_Initialize()
    Set olkRmd = Application.Reminders
End Sub

Private Sub Class_Terminate()
    Set olkRmd = Nothing
End Sub

Private Sub olkRmd_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 DateDiff("n", olkApt.Start, Now) <= 5 Then
            Set olkMsg = Application.CreateItem(olMailItem)
            With olkMsg
                .Recipients.Add EMAIL_ADDR
                .Recipients.ResolveAll
                If SEND_AS_SMS Then
                    .BodyFormat = olFormatPlain
                    .Subject = "Appt Reminder"
                    .Body = olkApt.Start & vbCrLf & olkApt.Subject & vbCrLf & olkApt.Location
                Else
                    .BodyFormat = olFormatHTML
                    .Subject = "Appointment Reminder"
                    .HTMLBody = "Starts: <b>" & olkApt.Start & "</b><br>Subject: <b>" & olkApt.Subject & "</b><br>Location: <b>" & olkApt.Location & "</b>"
                End If
                .Send
            End With
        End If
    End If
End Sub

Part 2.

Follow these instructions to add part 2 of the code.

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.

  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.
Dim olkMtgRem As clsMeetingReminder

Private Sub Application_Quit()
    Set olkMtgRem = Nothing
End Sub

Private Sub Application_Startup()
    Set olkMtgRem = New clsMeetingReminder
End Sub
Advertisements

14 comments on “Mobile Appointment Reminders

    • security setting was signed macros only. I got it working, so thanks for the code! Works great. The fix was that I had a self-signed certificate that I had previously used a long time ago. I used this same cert to sign the macro. As it turns out, self-signed certs are only valid on the machine where they were created. I had since moved to a new laptop. Creating a new cert cleared things up. Thanks for replying to a three year old post!

  1. Hi David, I was delayed trying your code. When I run it I get a ‘User-defined type not defined’ error with respect to the following lines:
    Private Sub Application_Startup()
    Set olkMtgRem = New clsMeetingReminder
    Am I missing an object library? Thanks!

    • Hi, Fred.

      Did you add the code from Part 1? If so, please check to make sure there isn’t a typo in the class name (step #5 in the instructions for part 1).

    • You are correct. I didn’t follow that direction. I have now carefully checked all directions. I appended Code Section 2 to the end of Code Section 1. (I’m a VB newbie as if you couldn’t tell.) No errors now,,, and after restarting Outlook, enabling macros and setting up some test appointments, it isn’t sending me an sms. I tried both TRUE and FALSE, i.e., as SMS and as EMAIL. I know the address is good because I can manually send my phone a message. Thanks!

    • Fred,

      The code from the two parts don’t belong in the same place, so if you truly appended the code from section 2 to the end of section 1 the code is in the wrong place. The code from section 1 belongs in a class module. The code from section 2 belongs in the ThisOutlookSession module.

    • Hi David. OK, that does make more sense. I rearranged the code and created a test meeting with 15 and 5 minute reminders. I received one text 5 minutes before the scheduled time — as designed. I don’t need a smart phone and don’t want the bulk of one in my pants pocket, so this little “app” suits my need. Thank you very much!

  2. Is there a way to do this without needing to have outlook open? (Possibly without a script or maybe even just email notifications as part of outlook… I can get my email but I cannot get my calender alerts)

    Good Article!

    • Thanks!

      I don’t know of any way to do this without having Outlook open. The exception to that might be Exchange. Although I’m not aware of one, it’s possible that there’s a third-party tool for Exchange that sends notifications. Of course that would only be useful if your mailbox is on an Exchange server and you’re willing to buy the tool (assuming there is one). Outlook does have a built-in means of doing this called “Outlook Mobile Service” (you can read more about it here), but using it requires subscribing to a mobile service provider. None of he ones I’ve looked at are free. Prices differ depending on the provider and the specific plan you sign up for. The ones I’ve looked at tend to charge at least 5-6 cents per message. Some plans cost 10+ cents per message.

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