Get Calendar Dates from Outlook

[Imdabaum]

Is there a way to import calendar events of category "Holiday" and Location "United States" from Access?

I'm trying to create a database that will automatically fetch holidays for previous 5 years and the 5 years after so it would have the major holidays for an 11 year span.

I've tried importing the data from Mailbox.Calendar but it only gives me the names of events and dates of events.

[JP2112]

Outlook version?

2003: http://support.microsoft.com/kb/924423
2007: http://office.microsoft.com/en-us/outlook-help/add-or-delete-holidays-and-custom-events-in-your-calendar-HP001230406.aspx
2010: http://office.microsoft.com/en-us/help/add-or-delete-holidays-and-custom-events-in-your-calendar-HP010355028.aspx

If you have custom holidays, you could also write a procedure that grabs the information from Access and creates each holiday (this requires VBA). All you need to do is create an AppointmentItem for each holiday, set AllDayEvent to True and mark the time as Free.

[Imdabaum]

Right but say I have UK Holidays added into my Outlook view because I like to be aware of what holidays are coming up that my wife might want to celebrate. But nobody at work gets those days off.

So I want to grab all the US holidays that are displayed in Outlook and then filter them according to the ones work recognizes as federal/state holidays. I'll add those dates to a table of holidays that work uses. I can add the state holidays fine. It's fetching the existing ones and putting them into a database with their name and respective date. That seems to be troubling me.

I want to automate this so that the holidays are added each time the new year ticks or by some such pattern.

Is there a way to view the location and category of the event? So far I've managed to get a list of holidays in an Appointment object, but can't seem to retrieve the names out of that array list.

[Imdabaum]

http://www.codeforexcelandoutlook.com/blog/2008/08/extract-calendar-data-from-outlook-into-excel/

I found this link... and coincidentally I think it may be yours JP. But I couldn't ever get it to work. It always exited the process. I have all my appointments and All UK holidays and US holidays added to my Outlook calendar. but when I tried this I still had 0 items in the calendar according to the code.

[JP2112]

OK, I was confused because the subject of this thread is "Get Calendar Dates from Outlook" but you asked how to import holidays from Access.

I can't see your Outlook so I don't know how your Calendar is set up. Try running this procedure and see if it produces anything:

Sub getholidays()
Dim appts As Outlook.Items
Dim appt As Outlook.AppointmentItem
Set appts = GetItems(GetNS(GetOutlookApp), olFolderCalendar)
For Each appt In appts
If appt.AllDayEvent Then
Debug.Print appt.Subject
End If
Next appt
End Sub

Function GetItems(olNS As Outlook.NameSpace, folder As OlDefaultFolders) As Outlook.Items
Set GetItems = olNS.GetDefaultFolder(folder).Items
End Function
Function GetOutlookApp() As Outlook.Application
' returns reference to native Application object
Set GetOutlookApp = Outlook.Application
End Function
Function GetNS(ByRef app As Outlook.Application) As Outlook.NameSpace
Set GetNS = app.GetNamespace("MAPI")
End Function

If this procedure doesn't produce anything in the Immediate Window --

1- Are you sure you are looking at your local default Calendar?
2- Are you sure these items are true holidays? Holidays are all day appointments with the time marked as free.

[Imdabaum]

OK, I was confused because the subject of this thread is "Get Calendar Dates from Outlook" but you asked how to import holidays from Access.

I can't see your Outlook so I don't know how your Calendar is set up. Try running this procedure and see if it produces anything:

Sub getholidays()
Dim appts As Outlook.Items
Dim appt As Outlook.AppointmentItem
Set appts = GetItems(GetNS(GetOutlookApp), olFolderCalendar)
For Each appt In appts
If appt.AllDayEvent AND appt.Location = "United States" Then
Debug.Print appt.Subject
End If
Next appt
End Sub

Function GetItems(olNS As Outlook.NameSpace, folder As OlDefaultFolders) As Outlook.Items
Set GetItems = olNS.GetDefaultFolder(folder).Items
End Function
Function GetOutlookApp() As Outlook.Application
' returns reference to native Application object
Set GetOutlookApp = Outlook.Application
End Function
Function GetNS(ByRef app As Outlook.Application) As Outlook.NameSpace
Set GetNS = app.GetNamespace("MAPI")
End Function
If this procedure doesn't produce anything in the Immediate Window --

1- Are you sure you are looking at your local default Calendar?
2- Are you sure these items are true holidays? Holidays are all day appointments with the time marked as free.
This is exactly what I was looking for!! Thank you. I only had to add the following modification to check appt.Location property and make sure it was United States before entering the debug. Now I can send each of these items into the holiday table. Thanks a million. Sorry about the late reply.