Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
repost: need help trying to automate Outlook from XL, with late binding
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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
repost: need help trying to automate Outlook from XL, with late binding
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/ol...sion_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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com KR wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
repost: need help trying to automate Outlook from XL, with late binding
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/ol...sion_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: ========================================== '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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
repost: need help trying to automate Outlook from XL, with late binding
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: 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. 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... 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) 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") Then add this line Set olFldr = olNs.GetDefaultFolder(9) Then everything from you early bound code below the line '***** Pull all outlook data into an array ***** 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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
repost: need help trying to automate Outlook from XL, with late binding
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" wrote in message ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
repost: need help trying to automate Outlook from XL, with late binding
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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Late Binding to Outlook from Excel: Outlook modifies email body | Excel Programming | |||
Late Binding help, Please | Excel Programming | |||
EARLY binding or LATE binding ? | Excel Programming | |||
DAO Late Binding? | Excel Programming | |||
Creating a MailItem in Outlook from Excel using Late Binding | Excel Programming |