Creating a Recurring Appointment in Excel VBA

When creating a recurring appointment, an additional object is involved: a RecurrencePattern object that defines the pattern for the series of appointments.

Here is an example:

Sub CreateRecurringAppointment()
    Dim appOutlook As Outlook.Application
    Dim appointment As Outlook.AppointmentItem
    Dim pattern As Outlook.RecurrencePattern
    Set appOutlook = CreateObject("Outlook.Application")
    ' Create a new appointment
    Set appointment = appOutlook.CreateItem(olAppointmentItem)
    ' Assign properties
    appointment.Start = "10/06/2025 07:45"
    appointment.Duration = 45 ' Duration in minutes
    appointment.Subject = "Test"
    ' Get the recurrence pattern object
    Set pattern = appointment.GetRecurrencePattern
    ' Set recurrence properties
    pattern.RecurrenceType = olRecursWeekly
    pattern.DayOfWeekMask = olWednesday Or olSaturday
    pattern.PatternStartDate = "18.03.2020"
    pattern.PatternEndDate = "04.04.2020"
    ' Save the recurring appointment
    appointment.Save
    ' Clean up
    appOutlook.Quit
    Set pattern = Nothing
    Set appointment = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:

• The CreateItem() method creates an object of type AppointmentItem.
• The appointment’s properties such as Start, Duration, and Subject are assigned.
• The GetRecurrencePattern() method returns an object of type RecurrencePattern.
• Some important properties of the RecurrencePattern object are then set:
  • RecurrenceType: Defines the recurrence frequency and must be set first. Valid values include:
    • olRecursDaily (daily),
    • olRecursWeekly (weekly),
    • olRecursMonthly (monthly).
  • DayOfWeekMask: Specifies the days of the week on which the appointment occurs. This is a bitmask composed by bitwise OR of day constants:
    • olSunday (1),
    • olMonday (2),
    • olTuesday (4),
    • olWednesday (8),
    • olThursday (16),
    • olFriday (32),
    • olSaturday (64).

You can combine any combination of these constants using the Or operator.

• • PatternStartDate and PatternEndDate: Define the date range during which the recurring appointments occur.
• Finally, the recurring appointment is saved using the Save() method.