Getting Excel to update Outlook Calendar

[Marcus]

This could be a challenge.

I want to get a excel userform to update my Calendar in MS
Outlook.

The aim is to insert dates and times into userform textboxs
and then when I submit this information to a spreadsheet
it automatically insert "busy" into the users calendar.

Is this possible......

 

[Rob van Gelder]

Sub test()
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objAppointment As Outlook.AppointmentItem

On Error Resume Next
Set objApp = GetObject(, "Outlook.Application")
If Err.Number Then Set objApp = CreateObject("Outlook.Application")
On Error GoTo 0

If Not objApp Is Nothing Then
Set objNS = objApp.GetNamespace("MAPI")
objNS.Logon

Set objAppointment = objApp.CreateItem(olAppointmentItem)
objAppointment.Subject = Range("A1").Value
objAppointment.Start = Range("B1").Value
objAppointment.Duration = Range("C1").Value
objAppointment.Save

Set objAppointment = Nothing
Set objApp = Nothing
End If

End Sub


Add a reference to Microsoft Outlook #.0 Object Library

Suggest you play with the objAppointment properties to fill in the other
details you're after

To late bind (no need for references), use:
Dim objApp As Object
Dim objNS As Object
Dim objAppointment As Object

 

[Marcus]

Many thanks.....

Marcus

-----Original Message-----
Sub test()
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objAppointment As Outlook.AppointmentItem

On Error Resume Next
Set objApp = GetObject(, "Outlook.Application")
If Err.Number Then Set objApp = CreateObject ("Outlook.Application")
On Error GoTo 0

If Not objApp Is Nothing Then
Set objNS = objApp.GetNamespace("MAPI")
objNS.Logon

Set objAppointment = objApp.CreateItem (olAppointmentItem)
objAppointment.Subject = Range("A1").Value
objAppointment.Start = Range("B1").Value
objAppointment.Duration = Range("C1").Value
objAppointment.Save

Set objAppointment = Nothing
Set objApp = Nothing
End If

End Sub


Add a reference to Microsoft Outlook #.0 Object Library

Suggest you play with the objAppointment properties to fill in the other
details you're after

To late bind (no need for references), use:
Dim objApp As Object
Dim objNS As Object
Dim objAppointment As Object


--
Rob van Gelder - http://www.vangelder.co.nz/excel





.

 

[Dave Peterson]

I don't have Outlook on my home pc, but if the OP is using latebinding, then
olAppointmentItem
will need to be defined or replaced with a number.

(Any idea what value?)

 

[Rob van Gelder]

Yes, quite right.

Const olAppointmentItem = 1

 

[Marcus]

By any chance doy ou know of any code which would allow me
to update a number of calendar at the sam time if the
person email address was known.

Marcus

 

[Dave Peterson]

me????

Nope.

Maybe an outlook user can chime in.