Check your Outlook reminders in VBA

Your VBA program may need to manage or manipulate Outlook reminders. If so, you'll need a way to grab the entire Reminders collection.

The simple way is:

Dim MyReminders As Outlook.Reminders
Set MyReminders = Outlook.Reminders

Then we can do something like this:

Sub TestReminder()

Dim MyReminder As Outlook.Reminder
Dim MyReminders As Outlook.Reminders

Set MyReminders = Outlook.Reminders

For Each MyReminder In MyReminders
  Debug.Print MyReminder.Caption
  Debug.Print MyReminder.Class
  Debug.Print MyReminder.IsVisible
  Debug.Print MyReminder.NextReminderDate
  Debug.Print MyReminder.OriginalReminderDate
Next MyReminder

End Sub

This method is preferred when you want to do something with the reminders, for example changing the caption on a sought-after reminder. You can directly access each reminder.

Rather than doing it inline, I prefer to move this into a function. The following function returns a String array of all available properties of the existing Reminder objects on a local computer. Then I can put everything into a listbox or iterate through it.

The GetReminders Function

Function GetReminders() As String()

Dim MyReminders As Outlook.Reminders
Dim MyReminder As Outlook.Reminder
Dim tempString() As String
Dim numRows As Long
Dim numCols As Long
Dim i As Long, j As Long

Set MyReminders = Outlook.Reminders

numRows = MyReminders.Count
numCols = 5 ' there are five properties we can assign to strings

ReDim tempString(1 To numRows, 1 To numCols)

  For i = 1 To numRows
    Set MyReminder = MyReminders.Item(i)

    tempString(i, 1) = MyReminder.Caption
    tempString(i, 2) = MyReminder.Class
    tempString(i, 3) = MyReminder.IsVisible
    tempString(i, 4) = MyReminder.NextReminderDate
    tempString(i, 5) = MyReminder.OriginalReminderDate

  Next i

GetReminders = tempString

End Function

Sample usage

Sub TestReminder()

Dim reminderString() As String
Dim i As Long, j As Long

  reminderString = GetReminders

  For i = 1 To UBound(reminderString)
    For j = 1 To UBound(reminderString, 2)
      Debug.Print reminderString(i, j)
    Next j
  Next i

End Sub

Some more Reminder functions you may find useful

The following function can be run against a Reminder Object to tell you if the reminder has ever fired.

Function HasReminderFired(rmndr As Outlook.Reminder) As Boolean
  HasReminderFired = (rmndr.OriginalReminderDate <> rmndr.NextReminderDate)
End Function

Sample usage

Sub TestReminder()

Dim MyReminder As Outlook.Reminder
Dim MyReminders As Outlook.Reminders

Set MyReminders = Outlook.Reminders

For Each MyReminder In MyReminders
  Debug.Print HasReminderFired(MyReminder)
Next MyReminder

End Sub

Dismiss and Snooze Reminders

Dismissing a reminder will stop it from firing, while snoozing it will cause the NextReminderDate property to increment accordingly. You might find these functions useful when working with reminders (especially in event handlers).

Function DismissReminder(rmndr As Outlook.Reminder)
  rmndr.Dismiss
End Function

Function SnoozeReminder(rmndr As Outlook.Reminder)
  rmndr.Snooze
End Function

Related Articles:

• June 14, 2011 -- QuickSteps Week, Part 2
• September 19, 2008 -- Send links via Outlook email
• June 24, 2008 -- Calculate Working Hours in VBA