Automating MS Outlook Agenda

[Arno R]

Hi all,

I need to get at specific (shared) agenda-data from MS outlook 2003.

I need every day to get the scheduled 'appointments' for some rooms to Access.
In Access I will show the time-periods that the rooms are booked for that day.
I know how to code all this in Access.
I can show some testdata for 5 rooms quite nicely on a form.

I never ever needed to use Outlook-to-Access before, so I need a good starting point to get the data.
Where do I start to look for some code? I intend to use late-binding.
If I could get the appointments to a csv-file it would be good enough.

Thanks
Arno R

[Tom van Stiphout]

On Wed, 10 May 2006 23:05:27 +0200, "Arno R"
<ar***********@ tiscali.nl> wrote:

One option is to attach the data: Link Tables > Exchange.
Another is to use the Outlook object model. Be sure to perform a FULL
installation of Outlook, so you get all the help files.
Not sure why you would want the appointments as csv (if it's even
possible). I would MUCH rather use one of the above.

If using Automation, and you eventually want late binding, I always
use early binding during development, so I have the benefit of
intellisense.

-Tom.

Hi all,

I need to get at specific (shared) agenda-data from MS outlook 2003.

I need every day to get the scheduled 'appointments' for some rooms to Access.
In Access I will show the time-periods that the rooms are booked for that day.
I know how to code all this in Access.
I can show some testdata for 5 rooms quite nicely on a form.

I never ever needed to use Outlook-to-Access before, so I need a good starting point to get the data.
Where do I start to look for some code? I intend to use late-binding.
If I could get the appointments to a csv-file it would be good enough.

Thanks
Arno R

[Arno R]

"Tom van Stiphout" <no************ *@cox.net> schreef in bericht news:ok******** *************** *********@4ax.c om...

On Wed, 10 May 2006 23:05:27 +0200, "Arno R"
<ar***********@ tiscali.nl> wrote:

One option is to attach the data: Link Tables > Exchange.
Another is to use the Outlook object model. Be sure to perform a FULL
installation of Outlook, so you get all the help files.
Not sure why you would want the appointments as csv (if it's even
possible). I would MUCH rather use one of the above.

If using Automation, and you eventually want late binding, I always
use early binding during development, so I have the benefit of
intellisense.

-Tom.

Thanks Tom,
I will need to investigate more on this.
I never used Outlook myself, so I don't know the object model, not the way Outlook stores data.
I will install Outlook2003 (and install all as you said) and I guess I will find out how I can link to other than the default pst-file.

What I need is to present today's appointments for about 10 meeting-rooms.
I need to present this data in Access (NOT in Outlook)
So what I need is to get today's appointments from these 10 'users'.
I also need to 'massage' the output a bit, that's why I was thinking of importing (to csv or whatever)
From that point (when I have the data) I can do what I want.

I did read the automation-files from ms but I did not find much about importing FROM Outlook.
I looked at some Outlook newsgroups for automation examples, but until now I did not find usable code examples.

Arno R

[Arno R]

"Tom van Stiphout" <no************ *@cox.net> schreef in bericht news:ok******** *************** *********@4ax.c om...

One option is to attach the data: Link Tables > Exchange.

Hi Tom,

I installed Outlook2003 and made a few Agenda entries.
I attached to the Agenda data with Link Tables > Exchange (same result for Link Tables > Outlook here)
All fine exect that the most important field aren't there ... ??
I see all kind of fields including subject, but NO fields for appointment date or appointment time ?????
This is crucial data for an appointment !!
Am I doing something very wrong here, or is MS doing something wrong ???

Testing:
When I export my agenda from Outlook to Access format, and link to the table Agenda in the created mdb then all the data I need is there.
However this method is not what I need.

Thanks,
Arno R

[Tom van Stiphout]

On Thu, 11 May 2006 18:44:45 +0200, "Arno R"
<ar***********@ tiscali.nl> wrote:

Here is a code fragment to get you started:

Public Sub ReadOutlook()
Dim olApp As Outlook.Applica tion
Dim olNameSpace As Outlook.NameSpa ce
Dim objCalendarFold er As Outlook.MAPIFol der
Dim objAppointment As Outlook.Appoint mentItem

Set olApp = CreateObject("O utlook.Applicat ion")

olApp.GetNamesp ace("MAPI").Log on "Microsoft Outlook"

Set olNameSpace = olApp.GetNamesp ace("MAPI")

Set objCalendarFold er =
olNameSpace.Get DefaultFolder(o lFolderCalendar )
With objCalendarFold er.Items
Set objAppointment = .GetFirst
While Not objAppointment Is Nothing
Debug.Print "Subject=" & objAppointment. Subject & ",
StartTime=" & objAppointment. Start & ", Duration=" &
objAppointment. Duration
Set objAppointment = .GetNext
Wend
End With

Set objCalendarFold er = Nothing
Set olNameSpace = Nothing
Set olApp = Nothing
End Sub

Also, OutlookSpy is a great tool if you want to know all the details
of the Outlook objects.

-Tom.

"Tom van Stiphout" <no************ *@cox.net> schreef in bericht news:ok******** *************** *********@4ax.c om...

One option is to attach the data: Link Tables > Exchange.

Hi Tom,

I installed Outlook2003 and made a few Agenda entries.
I attached to the Agenda data with Link Tables > Exchange (same result for Link Tables > Outlook here)
All fine exect that the most important field aren't there ... ??
I see all kind of fields including subject, but NO fields for appointment date or appointment time ?????
This is crucial data for an appointment !!
Am I doing something very wrong here, or is MS doing something wrong ???

Testing:
When I export my agenda from Outlook to Access format, and link to the table Agenda in the created mdb then all the data I need is there.
However this method is not what I need.

Thanks,
Arno R

[Arno R]

"Tom van Stiphout" <no************ *@cox.net> schreef in bericht news:23******** *************** *********@4ax.c om...

Here is a code fragment to get you started:

Public Sub ReadOutlook()
Dim olApp As Outlook.Applica tion
Dim olNameSpace As Outlook.NameSpa ce
Dim objCalendarFold er As Outlook.MAPIFol der
Dim objAppointment As Outlook.Appoint mentItem

Set olApp = CreateObject("O utlook.Applicat ion")

olApp.GetNamesp ace("MAPI").Log on "Microsoft Outlook"

Set olNameSpace = olApp.GetNamesp ace("MAPI")

Set objCalendarFold er =
olNameSpace.Get DefaultFolder(o lFolderCalendar )
With objCalendarFold er.Items
Set objAppointment = .GetFirst
While Not objAppointment Is Nothing
Debug.Print "Subject=" & objAppointment. Subject & ",
StartTime=" & objAppointment. Start & ", Duration=" &
objAppointment. Duration
Set objAppointment = .GetNext
Wend
End With

Set objCalendarFold er = Nothing
Set olNameSpace = Nothing
Set olApp = Nothing
End Sub

Also, OutlookSpy is a great tool if you want to know all the details
of the Outlook objects.

-Tom.

Thanks much Tom,

Your code errors at my place (at home) on the line
olApp.GetNamesp ace("MAPI").Log on "Microsoft Outlook"
If I comment this line out the code works.

Yesterday I came as far as this with similar code:
Sub TestAutomationO utlook()
On Error GoTo Err_OutL
Dim i As Integer
Dim objOutlook As Outlook.Applica tion
Dim nms As Outlook.NameSpa ce
Dim fld As Outlook.MAPIFol der
Dim itms As Outlook.Items
Set objOutlook = CreateObject("O utlook.applicat ion")
Set nms = objOutlook.GetN amespace("MAPI" )
Set fld = nms.folders("Pe rsoonlijke mappen").folder s("Agenda")
Set itms = fld.Items
For i = 1 To fld.Items.Count 'This is NOT zero-based ???
If DateValue(fld.I tems(i).Start) = Date Then
Debug.Print fld.Items(i).Su bject & "; " & fld.Items(i).St art & "; " & fld.Items(i).Du ration
End If
Next

CleanUp:
Set itms = Nothing
Set fld = Nothing
Set nms = Nothing
Set objOutlook = Nothing
Exit Sub

Err_OutL:
MsgBox Err & " " & Error$, vbCritical
Resume CleanUp
End Sub
I like your code better (more generic) so I will adapt it and add the DateValue restriction to get only the Items of today.

==> Next hurdle is to get at the shared folders.
Suppose I need the shared calendar for "MeetingRoo m1"
In what way do I need to change the codeline
Set objCalendarFold er =olNameSpace. . . . . . . . ??

Something like
Set objCalendarFold er =olNameSpace("S hared folders").folde rs("MeetingRoom 1") ??

(I can't test shared agenda's here at home, don't have exchange server here)

Thanks,
Arno R