VBA to send outlook appointment with recurrence

pmellor

New Member
Joined
Mar 20, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've got the below code and it works apart from the recurrence does't pick up, can anyone make any suggestions as to what I've got wrong here?

[/CODE]
VBA Code:
Public Sub CreateOutlookAppointments()
   Sheets("Delivery Timeline").Select
    On Error GoTo Err_Execute
    
    Dim olApp As Outlook.Application
    Dim olAppt As Outlook.AppointmentItem
    Dim blnCreated As Boolean
    Dim olNs As Outlook.Namespace
    Dim CalFolder As Outlook.MAPIFolder
    Dim olRecurrPatt As RecurrencePattern
    Dim xRg As Range
    
    Dim i As Long
    
    On Error Resume Next
    Set olApp = Outlook.Application
    
    If olApp Is Nothing Then
        Set olApp = Outlook.Application
         blnCreated = True
        Err.Clear
    Else
        blnCreated = False
    End If
    
    On Error GoTo 0
    
    Set olNs = olApp.GetNamespace("MAPI")
    Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar)
    Set xRg = Range("E10:N10")
    For i = 1 To xRg.Rows.Count
    
    Set olAppt = CalFolder.Items.Add(olAppointmentItem)
    Set olRecurrPatt = olAppt.GetRecurrencePattern
    
    olAppt.MeetingStatus = olMeeting
    
        With olRecurrPatt
        .RecurrenceType = olRecursWeekly
        .Duration = 10
        .StartTime = #9:00:00 AM#
        .EndTime = #9:10:00 AM#
        .DayOfWeekMask = olMonday
        .PatternStartDate = xRg.Cells(i, 1)
        .Interval = 3
        .PatternEndDate = xRg.Cells(i, 2)
        End With
    With olAppt
        'Define calendar item properties
        .Subject = xRg.Cells(i, 3)
        .Body = xRg.Cells(i, 8)
        .BusyStatus = xRg.Cells(i, 6)
        .ReminderMinutesBeforeStart = xRg.Cells(i, 7)
        .ReminderSet = True

' get the recipients
        Dim RequiredAttendee As Outlook.Recipient
        Set RequiredAttendee = .Recipients.Add(xRg.Cells(i, 10).Value)
            RequiredAttendee.Type = olRequired
            
            

 ' For meetings or Group Calendars
' use .Display instead of .Send when testing or if you want to review before sending
        .Display
        

    End With
    
    Next
    
    Set olAppt = Nothing
    Set olApp = Nothing
    Set myRecurrPatt = Nothing
        
    Exit Sub
    
Err_Execute:
    MsgBox "An error occurred - Exporting items to Calendar."
    
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,129,490
Messages
5,636,632
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top