recurring calendar events (1 Viewer)

[Andy Mc]

Hi
I want to extract some details from my Outlook calendar (subject and start and end dates). I have this code:

Public Function GetEvents()

Dim oOutlook As Outlook.Application
Set oOutlook = CreateObject("Outlook.Application")


Dim oNS As Outlook.NameSpace
Set oNS = oOutlook.GetNamespace("MAPI")


Dim oAppointments As Outlook.MAPIFolder
Set oAppointments = oNS.GetDefaultFolder(olFolderCalendar)


'Apply a filter
Dim sFilter As String

StartDate = Format("01/01/" & Year(Date), ddmmyy)
EndDate = Format("31/12/" & Year(Date), ddmmyy)

sFilter = "[Start] > '" & StartDate & "'" & "AND [End] < '" & EndDate & "'"


Dim oFilterAppointments As Object
Set oFilterAppointments = oAppointments.Items.Restrict(sFilter)

'Iterate through each appt in the calendar and add these to Table CalTemp
Dim oAppointmentItem As Object
Dim strSQL As String
Dim Abbr As String
Dim Sbjt As String

DoCmd.RunSQL "DELETE FROM CalTemp;" 'clear the table

For Each oAppointmentItem In oFilterAppointments

If InStr(oAppointmentItem.Subject, ":") < 1 Then
'do nothing if there's no ":"
Else
Abbr = Left(oAppointmentItem.Subject, InStr(oAppointmentItem.Subject, ":") - 1) 'finds the text before ":"
Sbjt = Mid(oAppointmentItem.Subject, InStr(oAppointmentItem.Subject, ":") + 1) ' finds the text after ":"
'add the event to the table "CalTemp"
strSQL = "INSERT INTO CalTemp VALUES ('" & Abbr & "', '" & Sbjt & "', '" & oAppointmentItem.Start & "', '" & oAppointmentItem.End & "' );"
DoCmd.RunSQL strSQL
End If

Next

End Function


The code runs OK, but when it comes to recurring entries it only picks up the first occurrence (ie the start date of the recurrence), even if this is outside the start filter limits. I would like to get all occurences for each day they appear within the current year.

Can anyone please suggest how to modify the code for this? Thanks

(PS, I'm just an entusiastic amateur, please be gentle )

 

[MajP]

See if this is relevant

Items.IncludeRecurrences property (Outlook)

Office VBA reference topic

docs.microsoft.com

 

[Andy Mc]

Thanks MajP, I'll take a look. I hadn't found that link in my searches.

 

[MajP]

I have reread that a few times and still not sure what it is saying.

This property only has an effect if the Items collection contains appointments and is not sorted by any property other than Start in ascending order. The default value is False. Use this property when you want to retrieve all appointments for a given date, where recurring appointments would not normally appear because they are not associated with any specific date. If you need to sort and filter on appointment items that contain recurring appointments, you must do so in this order: sort the items in ascending order, set IncludeRecurrences to True, and then filter the items. For a code sample showing this order, see the second example below. If the collection includes recurring appointments with no end date, setting the property to True may cause the collection to be of infinite count.

It seems that most times you would get recurrences, but depending on your sort and filter maybe not.
FYI, I am only guessing and have never done this.

 

[Andy Mc]

It is a very complicated statement.

It occurred to me that it might have been because there was no end date (= infinite count). However, I've just ran a test with a new recurring event with an end date but it still only picks up the first occurrence (ie today's date).