What's wrong with this code?Not working with me.

[villy]

To all gurus,

Please tell me why I am having error in red highlighted saying runtime erro 429 - ActiveX components can't create object.

I just copied the code from one site:

Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Set o = GetObject(, "Outlook.application")
Set ai = o.CreateItem(olAppointmentItem)
ai.Body = "Do abc"
ai.Subject = "Things to do"
ai.Start = "05/19/05 04:00:00 PM"
ai.Duration = 30
ai.Close olSave
End Sub

I know there will be someone out there got an idea.
What I wanted to do is to link a spreadsheet into Outlook calendar.
But trying the above code gives me an error.
Anyone?

 

[GTO]

I used set o = new outlook.application but still getting the same error
any thought?

AFAIK, error 429 occurs when the app is not running. I do not see how you could be getting the same error.

Then you might not have set the reference and New will not work.
Check under Tools/References... do you have a reference to the Microsoft Outlook Object Library there?

The New creates indeed a new instance of Outlook (not visible to the user, until you say something like o.Visible=True). If you want the existing instance of Outlook, that is more difficult, I don't have a solution for that.

See above, but if a reference to the library is not set, the error descript is close to "User-Defined Type not defined"

For the life of me, I cannot recall how to make Outlook .Visible, but .Visible is not there. Hopefully, some kind soul will make his/her way here.

Certainly agreed as to New. If the library is referenced.

As to an existing instance, I believe Outlook is a single-instance app. That is, CreateObject will reference the same (original) instance if it is already running. Again, hopefully someone will correct me if I amd wrong.


AND...

just a wild hunch, but maybe it means your Microsoft Exchange server is unavailable...

The line of the night (or day as the case mey be). I nearly fell out of my chair

 

[Hermanito]

Sorry to almost make you fall on the floor there GTO

I think it is possible to have multiple Outlook instances, but maybe you can only have one visible. I seem to recall a certain Outlook addin I once worked with, and it did a check for the number of instances before starting. This was also written to a logfile and I distinctly remember that this number was often more than 1.
Of course, the counting of instances might have been wrong too, not all too sure there, the code quality wasn't exactly stellar

 

[villy]

How can I tell outlook to create a new folder in calendar to link my excel file instead of linking it to calendar itself...
And, if there is someone out there who have other idea how to link excel file to a new folder in calendar please give me some of it thanks

 

[villy]

Guys, i've change my code to the one i am going to link with..

Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Dim r&, sSubject$, sBody$, sLocation$, sCategory$, dStartDate, dEndDate, dRemDate As Date, dDuration#
 
Set shtMain = ActiveWorkbook.Sheets("Main")
Set ai = o.CreateItem(olAppointmentItem)
LR = Cells(Rows.Count, "K").End(xlUp).Row
 
For r = 7 To LR
'Assuming that Subject is in column I
sSubject = shtMain.Range("I" & r).Value
'Assuming that Body is in Column D
sBody = shtMain.Range("D" & r).Value
'Assuming that Start Date and Time is in Column K
dStartDate = shtMain.Range("K" & r).Value
'Assuming that Duration is in Column M
dEndDate = shtMain.Range("M" & r).Value
'Assuming that Duration is in Column L
dRemDate = shtMain.Range("L" & r).Value
'Assuming that Duration is in Column C
dCategory = shtMain.Range("C" & r).Value
'Assuming that Duration is in Column H
dLocation = shtMain.Range("H" & r).Value
ai.Body = sBody
ai.Subject = sSubject
ai.Start = dStartTime
ai.Duration = dDuration
ai.Close olSave
Next r
End Sub

i am getting error 91: Object variable or with block variable not set.
Then highlighting the red one. What's this supposed to mean how can I correct it?
How can i link my spreadsheet into the new folder under calendar in which part of the program should I mention that to create forlder or should I do it manually in outlook then just call it by code..Please give some thoughts.
Thanks

 

[villy]

To all gurus,

I just copied the code from one site:

Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Set o = GetObject(, "Outlook.application")
Set ai = o.CreateItem(olAppointmentItem)
ai.Body = "Do abc"
ai.Subject = "Things to do"
ai.Start = "05/19/05 04:00:00 PM"
ai.Duration = 30
ai.Close olSave
End Sub

i tried to run this one inside the network we are using but I am getting now different error which is the one in red saying cannot find project or library.. Where to correct it. What am I missing.. sorry for too much question huh just need to figure it out.
Thanks again

 

[GTO]

Sorry to almost make you fall on the floor there GTO

I think it is possible to have multiple Outlook instances, but maybe you can only have one visible. I seem to recall a certain Outlook addin I once worked with, and it did a check for the number of instances before starting. This was also written to a logfile and I distinctly remember that this number was often more than 1.
Of course, the counting of instances might have been wrong too, not all too sure there, the code quality wasn't exactly stellar

I was quite happy laughing at the end of an all-to-long day!

For some reason, this took me a while to find, but thought you might find it interesting:

http://msdn.microsoft.com/en-us/library/aa164542(office.10).aspx

"A multi-use application makes it possible for host applications to share the same instance of the application. The next example creates a new instance of Microsoft® Outlook® only if Outlook is not running when the code is executed. Because Outlook is a multi-use application, if Outlook is running already when this code is run, the object variable points to the currently running instance."

 

[GTO]

Guys, i've change my code to the one i am going to link with..

Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Dim r&, sSubject$, sBody$, sLocation$, sCategory$, dStartDate, dEndDate, dRemDate As Date, dDuration#
 
Set shtMain = ActiveWorkbook.Sheets("Main")
Set ai = o.CreateItem(olAppointmentItem)
LR = Cells(Rows.Count, "K").End(xlUp).Row
 
'...remaining code...

i am getting error 91: Object variable or with block variable not set.
Then highlighting the red one. What's this supposed to mean how can I correct it?
How can i link my spreadsheet into the new folder under calendar in which part of the program should I mention that to create forlder or should I do it manually in outlook then just call it by code..Please give some thoughts.
Thanks

Sorry Villy, somehow I lost text in my last post? Anyways, in the above, you appear to have deleted setting a reference to Outlook altogether.

i tried to run this one inside the network we are using but I am getting now different error which is the one in red saying cannot find project or library.. Where to correct it. What am I missing.. sorry for too much question huh just need to figure it out.
Thanks again

What OS and what version of Excel are you using?

 

[villy]

I am using Windows XP and Excel 2003..
I was able to run without errors now just do some playing with the references.
My problem now is that it directly added onto calendar which what I wanted is to create another folder under calendar then link it to that created folder.How can I do that (to create a folder)