Tuesday, September 05, 2006

Getting back into the swing of things

Hi guys,

Again, sorry for being away for so long. I'm going to make a stand now that I will use my blog as and when i have something important to write about, rather than just because I haven't used it in a long time.
Today I'm going to tell you about how to get your outlook calendar items into Microsoft Access, including the fields you want rather than the ones provided when you link. Linking can be annoying because you dont get the basics such as start date / time.
The way I did it uses simple VBA to open an outlook object, open the calendar and put it into a recordset. It then goes through the recordset adding the records you want to look at.
This is great because you can also choose appointments which have certain attributes.

Anyway, enough rambling, here's the code:

Sub getcalendarappts()
DoCmd.SetWarnings (no)

Dim myApp As New Outlook.Application
Dim myNS As NameSpace
Dim myFolder As MAPIFolder
Dim myItems As Items
Dim MyItem As AppointmentItem
Dim intLoopCounter As Integer, intBusy As Integer

Set myNS = myApp.GetNamespace("MAPI")
Set myFolder = myNS.GetDefaultFolder(olFolderCalendar)
Set myItems = myFolder.Items

For intLoopCounter = 2 To myItems.Count
mysubject = myItems(intLoopCounter).Subject
mysubject = Replace(mysubject, "'", "")
mystart = myItems(intLoopCounter).Start
myend = myItems(intLoopCounter).End
myrecur = myItems(intLoopCounter).GetRecurrencePattern
mycreate = myItems(intLoopCounter).CreationTime

With CurrentDb
SQLcode = "Insert Into Outlook_Calendar values('" & mysubject & "',#" & mystart & "#,#" & myend & "#," & myrecur & ",#" & mycreate & "#)"
DoCmd.RunSQL (SQLcode)
End With
Next intLoopCounter

Set myNS = Nothing
Set myFolder = Nothing
Set myItems = Nothing
Set MyItem = Nothing

DoCmd.SetWarnings (yes)

End Sub

OK, so you're probably going Huh?! right now, so I'll walk you through it.
Firstly, it stops any warnings such as "You are about to add 1 row".
Then it declares and creates our main variables and objects. This is the stage where we actually drill down into the calendar. The first variable is an outlook object - this allows you to access the contents of outlook. It then creates a namespace (folder) reference, declares the folders we want to look at as MAPI folders, and creates variables for the actual appointment items, and a couple of integers so that we can count through.

It then sets the namespace to the MAPI folders of outlook, sets that to the actual calendar folder, and sets the items to refer to the items within the calendar.

Now we're onto the actual data harvesting.
For every record from #2 through to the end of the table (#1 is the titles which you dont need unless you're doing a CSV - which I'll talk about later with regards to using the stream writers), assign each of the values you want to see to a variable.
You can view the available values by looking at AppointmentItem in the object browser.
You'll notice I did some replacing with the subject - this simply removes the ' s from the subject line as they can mess around with the SQL code. Which brings me nicely onto the SQL code! This bit will vary depending on what your table looks like, but the basic syntax is "Insert Into ('') Values('');" I haven't included the fieldnames simply because I'm going to use all of them, in this case access simply works through them in order. You refer to the variables by using " & variable & " as you will probably have done whilst working with strings.

It then runs the SQLcode (hopefully adding the calendar item to the table).
and moves onto the next item in the outlook calendar.
The last bit is simply clearing up - setting the variables to nothing, and turning the warnings back on (otherwise they wont work until you next open access).

I hope that helped, as I had to trawl the net for hours when I first needed to do it.

All the best,