VBA Creat Oulook Calendar appointment from excel

[Raynepau]

Can anyone please help

I am trying to create a calendar appoint with VBA from excel and have been searching for a solution

I've tried a number of codes but nothing seems to work. I'm a bit of a novice so hoping that someone could help me

My current code is below

I get the following message - User defined type not defined with Dim ol As Outlook.Application highlighted

Sub CreateMeeting_Click()

Dim ol As Outlook.Application
'item As AppointmentItem

Set ol = New Outlook.Application
Set item = ol.CreateItem(olAppointmentItem)

'Set the reminder for 8:30 am on input date
item.Start = rDate + TimeValue("8:30")
'Set one hour duration
item.Duration = 60
'appointment subject
item.Subject = "Write your fancy subject line here"
'location description
item.Location = "Somewhere over there"
'body message
item.Body = "What in the world are you doing at this time?"
'set the busy status
item.BusyStatus = olBusy
'reminder before start
item.ReminderMinutesBeforeStart = 15
'reminder activated
item.ReminderSet = True
'duh! save the thing!
item.Save

'garbage collection - kind of...
Set ol = Nothing
Set item = Nothing

'return true
makeReminder = True
End Sub

 

[FreeSoldier]

Hi,

I think you need to add the "Microsoft Office 12.0 Object Library" to your references ( Tools/References) for your code to use Microsoft application.

Best

 

[Raynepau]

Thanks for the advice

I check the reference library and I'm currently have "Microsoft Office 14.0 Object Library ticked in the reference library. I cannot find Microsoft Office 12.0

Thanks

 

[DBAMJA]

You need to make sure that the Microsoft Outlook XX.X Object Library is referenced.

[flush]

Michael

It is said that God will give you no more than you can handle. I just wish God didn't think I was just a bad ass.

 

[mintjulep]

[s]Dim ol As Outlook.Application[/s][COLOR=#73D216]Dim ol as Object[/color]
'item As AppointmentItem

[s]Set ol = New Outlook.Application[/s][COLOR=#73D216]set ol = CreateObject("Outlook.Application")[/color]
Set item = ol.CreateItem(olAppointmentItem)

 

[Andrzejek]

What mintjulep presented here is changing your early binding to late binding.
More about the differences here or here

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Raynepau]

Hi thanks for your help

I think this change works but I'm now getting the message

'Object doesn't support this property or method'

When I click the Debug it highlights this line

item.Start = rDate + TimeValue("8:30")

Thanks

 

[mintjulep]

What is the value of rDate?

 

[Raynepau]

21/06/17

 

[Raynepau]

Hi

Thanks for your help so far

Are there any other approaches I can use to creat an outtlook calendar

 

[SkipVought]

In your posted code, rDate is never assigned a value???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Raynepau]

Hi Skip

Thanks for your response

rdate picks up a date value from a cell on the worksheet. The format of the cell is set to a date which results in the rdate value being 21/06/17

Also

If I mark this line as a comment and allow the code to run to the next line (Item.Duration = 60) I get the same message

'Item.Start = rDate + TimeValue("08:30")
'Set one hour duration
Item.Duration = 60

 

[Raynepau]

A short update

If I paste the code into Outlook VBA it runs fine

Although there is no value in rDate it defaults to a date in 1899 but the appointment is created

Thanks

 

[Raynepau]

Hi

Not sure what I did but I think I've solved it

Here is my latst code

Sub CreateMeeting_Click()
Dim ol As Object
Dim Item As Object
Const olAppointmentItem = 1
'item As AppointmentItem
Set ol = CreateObject("Outlook.Application")
Set Item = ol.CreateItem(olAppointmentItem)

rDate = [C13]

'Set the reminder for 8:30 am on input date
Item.Start = rDate + TimeValue("08:30")
'Set one hour duration
Item.Duration = 60
'appointment subject
Item.Subject = "Write your fancy subject line here"
'location description
Item.Location = "Somewhere over there"
'body message
Item.Body = "What in the world are you doing at this time?"
'set the busy status
Item.BusyStatus = olBusy
'reminder before start
Item.ReminderMinutesBeforeStart = 15
'reminder activated
Item.ReminderSet = True
'duh! save the thing!
Item.Display

'garbage collection - kind of...
Set ol = Nothing
Set Item = Nothing

'return true
makeReminder = True
End Sub

 

[Raynepau]

Sorry another question

I cannot find the correct code to add recipients

Is this something you can help with

Thanks

 

[mintjulep]

Item.recipients.add ("Joe Smith")

 

[Raynepau]

Excellent

Many Thanks to all who have helped

 

[Andrzejek]

If you found some post(s) helpful, click on Great Post link to award a star.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Raynepau]

Just when I thought I was finished,
I have another question.
I'm now trying to create the body of the appointment and want Bold text with different font colours.
I understand that the body can only be in rich text format which (from what I have read) is painful.
So I thought I would create my formatting in excel and copy / paste. I've got to the copy piece but struggling to find the correct code that will paste into the body.

Is this something that can be done

Thanks