repost: need help trying to automate Outlook from XL, with late binding

[KR]

I'm (for the first time) developing a cross-office mini-app that includes
OL; this is also the first time I've been in an environment where different
levels of the libraries are co-existing, requiring me to use late binding.
So far, I like early binding a lot better :-/

I'm trying to get appointment information from OL using late binding. I've
used intellisense with the library referenced, and also explored (to my
limited understanding) the OL object model via OL VBA. Unfortunately, no
matter what I do, once I try to run my late binding code, it get "object
doesn't support this property or method" errors, and I haven't been able to
figure out what the heck I'm doing wrong.

So, I'm posting the relevant section of my code below, with hat in hand,
hoping someone can lend some syntax help. I appreciate any help you can
offer... if you want to be my hero, please also provide suggestions on
syntax to get the appointment subject, start date, end date, and duration as
well... I haven't even gotten that far yet...
Keith

==========================================
'for late binding:
Dim olApp As Object
Dim olNs As Object
Dim olFldr As Object
Dim olApt As Object

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olApp.GetFolderFromID("MAPIFolder") '<--errors out
Set olApt = olFldr.getappointmentitem("olFolderCalendar")


'for early binding
' Dim olApp As Outlook.Application
' Dim olNs As Namespace
' Dim olFldr As MAPIFolder
' Dim olApt As AppointmentItem

' Set olApp = New Outlook.Application
' Set olNs = olApp.GetNamespace("MAPI")
' Set olFldr = olNs.GetDefaultFolder(olFolderCalendar)

'***** Pull all outlook data into an array *****
For Each olApt In olFldr.Items
If InStr(1, olApt.Subject, "Vacation", vbTextCompare) > 0 Then
If Year(olApt.Start) = 2005 Then
MyDur = olApt.Duration / 60
If MyDur > 8 Then MyDur = 8

' UseRow = Format(olApt.Start, "mm")
eachmonth = Val(Format(olApt.Start, "mm"))
ThisDay = Val(Format(olApt.Start, "dd"))
'LastDay = Val(Format(olApt.End, "dd"))


'Gives starting row position
PasteMonthStartRow = 16 * ((eachmonth - 1) \ 3) + 17

'gives 1, 2, or 3 for the column grouping
PasteMonthStartColumn = (eachmonth Mod 3)
If PasteMonthStartColumn = 0 Then PasteMonthStartColumn = 3
'Gives the number of the actual start column
PasteMonthStartColumn = ((PasteMonthStartColumn - 1) * 7) +
1

OffsetX = (((MAdjArray(eachmonth)) + (ThisDay - 1)) \ 7) * 2
OffsetY = ((MAdjArray(eachmonth)) + (ThisDay - 1)) Mod 7

PasteMonthRow = PasteMonthStartRow + OffsetX
PasteMonthColumn = Trim(Chr((PasteMonthStartColumn +
OffsetY) + 64))

With oWrkSht
.Activate
.Range(PasteMonthColumn & PasteMonthRow).Select
Selection.Value = MyDur
Selection.AddComment (olApt.Subject)
End With

'MsgBox "Appt found:" & Chr(13) & Format(olApt.Start,
"mm/dd/yy") & Chr(13) & _
' "'" & PasteMonthColumn & "' '" & PasteMonthRow &
"'" & Chr(13) & _
' "'" & PasteMonthStartColumn & "' '" &
PasteMonthAddColumns & "'" & Chr(13) & _
' "'" & PasteMonthStartRow & "' '" &
PasteMonthAddRows & "'" & Chr(13)


'Debug.Print olApt.Subject, MyDur, Format(olApt.Start,
"mm/dd/yy")

End If
End If
Next olApt

Set olApt = Nothing
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

 

[Dick Kusleika]

Keith

Set olFldr = olApp.GetFolderFromID("MAPIFolder") '<--errors out

I've never used GetFolderFromID, but I'll hazard a guess that there's not
folder with the id "MAPIFolder". olFolderCalendar is a constant that's
built into the Outlook library. If you don't use the library (i.e. you're
late binding), then you just need to use the intrinsic value of that
constant. Go to the Immediate Window with a reference set as if you're
early binding and type in

?olFolderCalendar
9

Now go back to your sub and put this at the top (under your Dim statements)

Const olFolderCalendar As Long = 9

When you get rid of the reference, you'll have a constant in your procedure
that you can use just like the built-in one. With the addition of the Const
in your code, the early bound and late bound code for that line will be
exactly the same.

It looks like you're going to run into a problem on the next line because
getappointmentitem isn't a method of the MAPIFolder object, or any other
object of which I'm aware.

Here's my late bound conversion checklist. I don't know if it's complete,
but it has worked for me so far

a.. Change all declarations from Outlook objects to the generic Object
data type
b.. Change Set statements to GetObject or CreateObject
c.. Change any built-in constants to their intrinsic values
d.. Add optional arguments that have a default value
It can be found at
http://www.dicks-clicks.com/excel/olBinding.htm#Late_Bound_Conversion_Checklist

One other thing to note. In your EB code, you declare olApt as
AppointmentItem and in your LB code it's Object. A problem that I have run
into is when there's a different kind of Outlook item in the folder. You
should check the type of that object

For Each olApt in olFldr.Items
If TypeName(olApt) = "AppointmentItem" Then
'Do your stuff
End If
Next olApt

It happens so rarely that I don't even remember the specifics of the problem
I encountered, but it will happen to one of your users if you don't test for
it. The problem is that the Object data type will pick up any type of
Outlook item, but will crash when it gets to a property, like Start, that
doesn't relate to the object at hand.

 

[KR]

Dick- thank you for the help- I think I'm a little closer, but I don't have
a programming background, so I still have a conceptual question about the OL
model when late binding from XL. I appreciate your (and everyones) time with
any additional assistance.

Now go back to your sub and put this at the top (under your Dim statements)

Const olFolderCalendar As Long = 9

When you get rid of the reference, you'll have a constant in your procedure
that you can use just like the built-in one. With the addition of the Const
in your code, the early bound and late bound code for that line will be
exactly the same.

I've added this, and at first I thought it was replacing the line:
|| Set olFldr = olApp.GetFolderFromID("MAPIFolder") '<--errors out

but then I noticed two things; the variable name is different (presumable
intentional?) and that the const refers to a value, but I still need to use
that value to actually refer to the folder, so I can cycle through the
appointments:
|| For Each olApt In olFldr.Items

so somehow I need to identify olFldr, maybe something like:
Set olFldr = (something)(olFolderCalendar)
but browsing the object model (as someone who doesnt really understand what
I'm looking at) I'm not sure what to use...

It looks like you're going to run into a problem on the next line because
getappointmentitem isn't a method of the MAPIFolder object, or any other
object of which I'm aware.

I took your excellent suggestion of verifying the object type by name, so I
think I may be ok (can't test it yet, until I get the earlier code working).
I ended up just commenting out that line, because it will just look for each
"olApt" (object) in the folder anyway, which will be fine now that I will
verify the object type before running additional code.

Here's my late bound conversion checklist. I don't know if it's complete,
but it has worked for me so far

a.. Change all declarations from Outlook objects to the generic Object
data type
b.. Change Set statements to GetObject or CreateObject
c.. Change any built-in constants to their intrinsic values
d.. Add optional arguments that have a default value
It can be found at

http://www.dicks-clicks.com/excel/olBinding.htm#Late_Bound_Conversion_Checklist

Thanks! This is definitely a learning experience for me. And if I do get to
the point that I can late bind code, that will help me on other projects as
well- I've just never been forced into it until now.

best regards,
Keith

code:

 

[Dick Kusleika]

I've added this, and at first I thought it was replacing the line:

but then I noticed two things; the variable name is different
(presumable intentional?) and that the const refers to a value, but I
still need to use that value to actually refer to the folder, so I
can cycle through the appointments:

That line does not replace the Set line, it's in addition to the Set line.
With late binding, your set line should look like this

Set olFldr = olNS.GetDefaultFolder(9)

Now you can cycle through the Items in olFldr. You can make this line more
readable by defining a constant and setting its value equal to 9.

Const olFolderCalendar As Long = 9

Set olFldr = olNS.GetDefaultFolder(olFolderCalendar)

It's not necessary to do this extra step, it's just nice when you look at
your code later. You may look at the literal 9 and say "what the heck was
that for?" but if you define a constant you'll know you meant the Calendar
folder.

so somehow I need to identify olFldr, maybe something like:
Set olFldr = (something)(olFolderCalendar)
but browsing the object model (as someone who doesnt really
understand what I'm looking at) I'm not sure what to use...

So you don't want to change the method in which you assign olFldr when you
convert to late-bound. You still want to use the GetDefaultFolder method of
the NameSpace object. The only problem you had with that line (as written
early bound) is that it used a built-in constant as an argument to
GetDefaultFolder. Once you change that to either the literal value (9) or
to a constant that you define yourself (Const olFolderCalendar As Long = 9)
then your early bound code works just fine in a latebound situation.

Sorry to be so long winded, but here's what you need to do: Start with your
early bound code and use these line (which you already have done)

Then add this line

Set olFldr = olNs.GetDefaultFolder(9)

Then everything from you early bound code below the line

should work fine. Once you've done that and it works, you can (optionally)
define your own constant to replace the literal 9 and check the TypeName of
olApt in your loop.

I hope that helps. Please post back if you need more clarification or have
other questions.

 

[KR]

Dick- my most sincere and humble appreciation for your help- my project is
indeed working on my machine now, so now I'll go find some hapless
victim...err...willing volunteer in my department to make sure it works on
everyone's machine. My coworkers will be very happy that they no longer have
to enter their data in two separate systems.

My continuted appreciation to all the MVPs for their help, especially in
this group, where I post (and lurk) the most.

Dick (and others) - one final question, now that I see the benefits of late
binding, but still don't have a terribly good understanding of how to make
it work (I don't have a programming background)... would my time be best
spent learning the object models of the various office programs (word and
powerpoint, most often), or should I first look for VBA books to see if any
give special attention to late binding (theory, practice, common pitfalls,
etc.)?

Best regards,
Keith

 

[Dick Kusleika]

Keith

Dick (and others) - one final question, now that I see the benefits
of late binding, but still don't have a terribly good understanding
of how to make it work (I don't have a programming background)...
would my time be best spent learning the object models of the various
office programs (word and powerpoint, most often), or should I first
look for VBA books to see if any give special attention to late
binding (theory, practice, common pitfalls, etc.)?

No VBA books are going to give special attention to late binding. They'll
have one section in a chapter that discusses the pros and cons. But there's
not really much to know. If you want to automate in Excel (ie, control
other applications from within Excel), then you'll either late bind or early
bind. Most people will tell you to avoid late binding unless it's
absolutely necessary. Late binding makes your program run significantly
slower than early binding.

The best thing you can do is program early bound and use the lowest version
of the application you want to support. If you're automating Word and you
want to support Word97 and newer, then early bind to the Word97 object
library. If a user has WordXP, the library will automatically update to the
correct version, but it won't go backward, only forward.

Sometimes you have to late bind. If you're automating Outlook and you have
OLXP on your machine, but you want to suppor OL98 and newer, you have to
late bind. You can't have more than one Outlook on your machine, so your
stuck with XP library and for users with older version to use it, it will
have to be late bound.

The hardest part of automation, for me, is knowing the other program's
object model. I think your time will be best spent understanding the
objects, properties, and methods of the programs you want to automate.