set reference to Outlook from VBA 2003/2007

[gem1204]

I have created an ms access database that uses a reference to outlook. The only problem is that some of uses still have office 2003 and others have office 2007 so I created 2 versions of the database one for 2003 users and one for 2007 users - I know this is the bad way to do things.

I know there is a way to detect what version of office is being used with vba then programmatically loop through the references collection and set the appropriate references based on the version of office being used. Can someone help me figure this out?

Thanks
GEM

 

[RuralGuy]

Why not simply use late binding?

 

[gem1204]

What does late binding have to do with this. Late binding is used primarily with recordsets or so I thought. Even so, if you solutions is "Late Binding" I still need to know how to detect what version of office is being used to I can make a reference to either office 11 outlook or Office 12 Outlook.

Does anyone know the code to detect what version of office is being used.

Thanks
GEM

 

[Banana]

What RG was trying to tell you is that you can avoid having to create two versions which both reference same library but just different versions of it. By using late binding, you only have to tell it where to get the library, and it will work for any libraries with the same ClassID.

But if you really insist on early binding (and I will caution you- it will only get even more complicated if you persist down this path), you can look at Access help for "References" colleciton and "IsBroken" method.

But late binding is the best way to do it, really.

 

[gem1204]

thanks,
I think I understand now what meant. Would it be possible to get a sample code snipet for MS Outlook 2003 / MS outlook 2007. I have a database that is set up to send emails but I'm having problems because some users still have office 2003 while others have 2007.

I assume I would have to detect what version of office is being used on the machine that opens up the database?

 

[boblarson]

I assume I would have to detect what version of office is being used on the machine that opens up the database?

Nope, that's what late binding does for you so you don't have to know what version you're dealing with. Access does it behind the scenes. So, where you have things like

Dim oMsg As Outlook.Message

you would use

Dim oMsg As Object

And

Dim oApp As Outlook.Application

it would be

Dim oApp As Object
Set oApp = CreateObject("Outlook.Application")

 

[RuralGuy]

thanks,
I think I understand now what meant. Would it be possible to get a sample code snipet for MS Outlook 2003 / MS outlook 2007. I have a database that is set up to send emails but I'm having problems because some users still have office 2003 while others have 2007.

Have you examined the link I posted? It shows how to do it.

I assume I would have to detect what version of office is being used on the machine that opens up the database?

Nope! Not necessary.

 

[gem1204]

I think I actually understand.

If I use Dim oMsg As Outlook.Message, I have to have a reference to either outlook 2003 or 2007 in my vba project.

If I use :
Dim oApp As Object
Set oApp = CreateObject("Outlook.Application")
I don’t need a reference, it creates the object based on the applications installed on the computer? ….Same as VbScript!

I knew how to do that but I never thought of it in this scenario. In fact this is the way I cheat when writing vb script. Write the code with all that wonderful intelisense then just change a few lines of code when declaring the object and everything works fine!

Thanks
GEM

 

[boblarson]

I think I actually understand.

If I use Dim oMsg As Outlook.Message, I have to have a reference to either outlook 2003 or 2007 in my vba project.

If I use :
Dim oApp As Object
Set oApp = CreateObject("Outlook.Application")
I don’t need a reference, it creates the object based on the applications installed on the computer? ….Same as VbScript!

I knew how to do that but I never thought of it in this scenario. In fact this is the way I cheat when writing vb script. Write the code with all that wonderful intelisense then just change a few lines of code when declaring the object and everything works fine!

Thanks
GEM

yep, that's the way to do it.

 

[RuralGuy]

You're on your way. Thanks for jumping in Bob. Merry Christmas everyone.

 

[kpaull]

Can anyone help me, I am experiencing the same thing with my code, I have users on Outlook 2003 and 2007 and for the 2003 users it blows up.

Here is my code:

Sub CreateAppointment()

Dim myOlApp As Outlook.Application
Dim myItem As Outlook.AppointmentItem

Set myOlApp = GetObject(, "Outlook.Application")
Set myItem = myOlApp.CreateItem(olAppointmentItem)
With myItem
.Subject = Sheets("Sheet1").Range("C7")
.Location = " " 'Sheets("Sheet1").Range("A2")
.Start = Sheets("Sheet1").Range("C9")
.End = Sheets("Sheet1").Range("C10") + 1
.AllDayEvent = True
'.Duration = Sheets("Sheet1").Range("C9")
.Body = Sheets("Sheet1").Range("C11")
.MeetingStatus = Sheets("Sheet1").Range("C14")
.ReminderSet = False
'.Send
.BusyStatus = olFree
.ResponseRequested = False
MsgBox "Address the following appointment to your Peers and PM's"
.Display
End With
End Sub

 

[JANR]

Well if you read the tread then LateBind your code and remove the Outlook Refrence from your db. Then it would not matter which version of outlook your users have.

Sub CreateAppointment()
Dim myOlApp As Object
Dim myItem As Object
 
Const olAppointmentItem = 1
Const olFree = 0
 
On Error Resume Next
Set myOlApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then   'Outlook is not open
    Err.Clear
    Set myOlApp = CreateObject("Outlook.Application")
End If
 
Set myItem = myOlApp.CreateItem(olAppointmentItem)
    With myItem
        .Subject = Sheets("Sheet1").Range("C7")
        .Location = " " 'Sheets("Sheet1").Range("A2")
        .Start = Sheets("Sheet1").Range("C9")
        .End = Sheets("Sheet1").Range("C10") + 1
        .AllDayEvent = True
        '.Duration = Sheets("Sheet1").Range("C9")
        .Body = Sheets("Sheet1").Range("C11")
        .MeetingStatus = Sheets("Sheet1").Range("C14")
        .ReminderSet = False
        '.Send
        .BusyStatus = olFree
        .ResponseRequested = False
        MsgBox "Address the following appointment to your Peers and PM's"
        .Display
    End With
 
Set myItem = Nothing
Set myOlApp = Nothing
End Sub

JR

 

[The_Doc_Man]

Now, having seen the description of late binding, you know it is the way to go. However, the way to know what version of Access you are running is

MyVers = Application.Version

(or something like that) where you can look up the properties of the Application object using Access Help.

If I remember correctly, Access 2003 = version 11.0 and Access 2007 = version 12.0