Send meeting to outlook - address item line causing error

[jamierbooth]

Hi All, I'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.

Error message highlights the red line below with the following message:

Runtime error '438'
Object doesn't support this property or method

I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.

Any help would be grand!
Cheers, Jamie.

Private Sub cmdSend_Click()
 
    Dim outApp As Object
    Set outApp = CreateObject("Outlook.application")
    Set outmail = outApp.CreateItem(olappointmentitem)
 
        outmail.MeetingStatus = olMeeting
        [COLOR=red]outmail.To = "[/COLOR][COLOR=red]emailaddress1@mail.com;emailaddress2@mail.com[/COLOR][COLOR=red]"[/COLOR]
        outmail.Start = Me!ApptDate & " " & Me!ApptTime
        outmail.Duration = 60
        outmail.Subject = Me!txtFirstname.Value & " " & Me!txtSurname.Value
        outmail.Body = "To see" & " " & Me!txtToSee.Value
    outmail.display
    Set outmail = Nothing
    Set outApp = Nothing
End Sub

 

[AOB]

Hi jamierbooth,

Looks like you are using late-binding (a good thing! ) to define your objects but then referring to Outlook-specific constants (olMeeting and olAppointmentItem) which VBA will only be able to translate if early-binding.

You would have picked up on this if you quoted 'Option Explicit' at the top of your module (highly recommended!)

Can I suggest, especially if you're new to VBA, that you use early-binding when you're writing your code, get that working, then switch to late-binding when you want to deploy it or pass it around.

Early-binding requires adding a reference to library which then allows you to see properties (helpful when you're putting it together)

Late-binding removes this dependancy but means you have to use generic objects and set them as you go (helpful when you want other people to use your code without errors)

So - early-binding first - in the VBA editor, go to Tools > References... and add the Microsoft Office Outlook X.0 Object Library

Now you have the whole Outlook development library at your disposal which makes it a lot easier to put the code together. You'll notice you get all the Outlook options in the intellisense (autopopulate) as you write

Here is an early-bound version of your code; the pieces in red require that reference to the Outlook library to be present :

Option Explicit
 
[COLOR=black][FONT=Verdana]Private Sub cmdSend_EarlyBinding()[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]  Dim outApp As [/FONT][/COLOR][COLOR=red][FONT=Verdana]Outlook.Application[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]  Dim outmail As [/COLOR][/FONT][COLOR=red][FONT=Verdana]Outlook.AppointmentItem[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]  Set outApp = CreateObject("Outlook.application")[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]  Set outmail = outApp.CreateItem([/COLOR][/FONT][COLOR=red][FONT=Verdana]olAppointmentItem[/FONT][/COLOR][COLOR=black][FONT=Verdana])[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]  With outmail[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]    .MeetingStatus = [/COLOR][/FONT][COLOR=red][FONT=Verdana]olMeeting[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]    .Recipients.Add "emailaddress1@mail.com;emailaddress2@mail.com"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Start = Me!ApptDate & " " & Me!ApptTime[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Duration = 60[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Subject = Me!txtFirstname.Value & " " & Me!txtSurname.Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Body = "To see" & " " & Me!txtToSee.Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Display[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  End With[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]  Set outmail = Nothing[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]  Set outApp = Nothing[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]

This should work - now you want to switch it to late-binding (so that dependancy on the reference is no longer required) We just need to switch the dependant pieces to something generic. Revert to generic objects and 'enumerate' the Outlook constants (you can get the numeric values quite easily on MSDN or even Google)

Here is the late-bound version :

Option Explicit
 
[COLOR=black][FONT=Verdana]Private Sub cmdSend_LateBinding()[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]  Dim outApp As [/FONT][/COLOR][COLOR=red][FONT=Verdana]Object[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]  Dim outmail As [/COLOR][/FONT][COLOR=red][FONT=Verdana]Object[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]  Set outApp = CreateObject("Outlook.application")[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]  Set outmail = outApp.CreateItem([/COLOR][/FONT][COLOR=red][FONT=Verdana]1[/FONT][/COLOR][COLOR=black][FONT=Verdana])[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]  With outmail[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]    .MeetingStatus = [/COLOR][/FONT][COLOR=red][FONT=Verdana]1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    .Recipients.Add "emailaddress1@mail.com;emailaddress2@mail.com"[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]    .Start = Me!ApptDate & " " & Me!ApptTime[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Duration = 60[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Subject = Me!txtFirstname.Value & " " & Me!txtSurname.Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Body = "To see" & " " & Me!txtToSee.Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]    .Display[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  End With[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]  Set outmail = Nothing[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]  Set outApp = Nothing[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]

Hope this makes sense and works for you!

 

[AOB]

Oh, just to clarify, once you have the late-bound version working, remove the reference from Tools > References... Even though it is no longer required, it can cause an error for other users (for example, if they have an older version of Office installed) You don't need it anymore so don't refer to it!

 

[jamierbooth]

Hi, Sorry its been a while since my reply, but I've been in demand elsewhere and only just come back to this project. Thanks for the response, however.... I fall down at the first hurdle - and some other issues....

When you say - "So - early-binding first - in the VBA editor, go to Tools > References... and add the Microsoft Office Outlook X.0 Object Library" - I've done this, however I don't have the X.0 Object Lib you mention in my list available.

2nd, I don't know the difference between early or late binding, or how to switch between them or why I should. No-one else will be viewing or using this code once its completed and I'm not too fussed about how I write it. Most of it is taken/adapted from what I have taught myself and taken from the web.

Also, it seems that when I change the first bit

Private Sub cmdSend_Click()

To

Private Sub cmdSend_EarlyBinding()

that the command button loses its connection to the on click event and nothing fires when I hit the button... Maybe I'm missing something. My code is set up as part of a class object and not in its own module.

???

Cheers, Jamie.

 

[AOB]

Hi Jamie,

Okay, my apologies, I may have confused the issue somewhat...

Try this article for some info on the difference between early and late binding. There are plenty of others on the web if you need further detail.

The reason I went down that road was because you appeared to be using elements of both - therefore I wasn't sure which method you were aiming to use?

The reasons you would use one over the other are varied but in short - if you want to give this workbook to someone else, for them to use, without knowing what versions of various applications they are using, you should probably use late-binding. Because then you don't have to depend on them having certain libraries saved which you make reference to in your code.

If you are only developing this workbook for yourself OR you know that anybody you share this with will have the same libraries as you, then by all means use early-binding - it will be a lot easier to code, especially if you are using unfamiliar objects and properties.

But you have to pick one or the other. If you look at my two versions, you will see what the differences are - they are minor but important.

You should not change the name of your sub from what it was originally : cmdSend_Click. This is necessary for it to be detected as an event linked to that command button. I only changed the names to help you distinguish between them. So your sub should be renamed back to cmdSend_Click.

If you do not have an object library named something like "Microsoft Outlook X.0 Object Library" then you will definitely have a problem with early-binding. You have to have that reference active for early-binding to work. Otherwise, the VBA does not know what things like Outlook.AppointmentItem and olMeeting are - they are taken from the library at the very start.

May seem like a silly question but do you have Microsoft Outlook installed and, if so, what version of it?

 

[jamierbooth]

Ah. Things are becoming clear. The DB I'm creating will be shared amongst a number of staff but we all have access to identical applications, as this is the way our ICT dept have set us all up. We all have Windows 7 OS, MS Access 2000 and MS Outlook 2010. (and Word & Excel 2010 - ICT don't like us using access but refuse to buy my team a database, hence me making one for them, and why they haven't updated it!)

Probably the reason my code is a mix of the both is that I've taken it from the net somewhere and don't fully understand the differences between them both. From what you have written here, early binding should be ok for me, however I have the reference to MS Outlook 14.0 Object Library ticked on the list of references.

Re: name of sub, I did think this was the case and have not altered it. But what about the top line of my code? Currently it says Option Compare Database, and there are several other subs on the same page (class object). Should I still change this to Option Explicit? (Sorry, I also never quite understood this from my Access Reference book...)

Cheers.

 

[AOB]

You can - and should - use both :

[FONT=Courier New]Option Compare Database[/FONT]
[FONT=Courier New]Option Explicit[/FONT]

See this thread...

As long as you're happy that everybody has the same versions, and you have that reference to MS Outlook 14.0 Object Library ticked in the References dialog, then early-binding will work for you :

[FONT=Courier New]Option Compare Database[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
 
[FONT=Courier New][COLOR=black]Private Sub cmdSend_Click()[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]Dim outApp As [/COLOR][COLOR=red]Outlook.Application[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]Dim outmail As [/COLOR][COLOR=red]Outlook.AppointmentItem[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]Set outApp = CreateObject("Outlook.application")[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]Set outmail = outApp.CreateItem([/COLOR][COLOR=red]olAppointmentItem[/COLOR][COLOR=black])[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]With outmail[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black] .MeetingStatus = [/COLOR][COLOR=red]olMeeting[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] .Recipients.Add "emailaddress1@mail.com;emailaddress2@mail.com"[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] .Start = Me!ApptDate & " " & Me!ApptTime[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] .Duration = 60[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] .Subject = Me!txtFirstname.Value & " " & Me!txtSurname.Value[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] .Body = "To see" & " " & Me!txtToSee.Value[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] .Display[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]End With[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]Set outmail = Nothing[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]Set outApp = Nothing[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]End Sub[/COLOR][/FONT]

Try this first and see if it compiles for you and then if it runs as expected. If not, debug it (step through line by line and find where it falls over, and with what error) and post the outcome back on here and I'll take a look.

The reason I tend to prefer late-binding is that I often *think* everybody is using the same version, then one guy raises his head and says he's still using, say, Excel 2003 over Excel 2007, and suddenly it won't work for him (because I refer to the Excel 12.0 library and he only has Excel 10.0 and can't link to the required reference)

Late-binding removes that issue, albeit at the expense of more difficult coding (as you don't have the intellisense properties and predefined constants inherent to the library at your fingertips) Hence my advice would generally be to use early-binding to get your code right, then switch to late-binding when you're ready to share it around. Having said that, there are advantages to keeping code early-bound, and there will be advocates of both. It all depends on what your needs are I guess...

 

[jamierbooth]

Thanks AOB.

Have tried that, but I'm getting a Runtime Error 429 'ActiveX component can't create object'.

If this isn't something that can be fixed with late binding or anything else you can think of, it might have something to do with the archictecture of our system here. We work on a 'virtual desktop' which I believe does something to our MS office license so I may have to ditch any idea of sending things to Outlook from Access.

Cheers, Jamie.

 

[AOB]

Hi Jamie

Sorry, I was away at a wedding for a few days

What line are you getting that error on?

I'm not sure which component in there is ActiveX - I wouldn't have thought any of them (I tend to avoid ActiveX if I possibly can)

 

[jamierbooth]

Debug is stopping at this one.....

    Set outapp = CreateObject("Outlook.application")

 

[AOB]

Try this article maybe?...