INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Creat Oulook Calendar appointment from excel

VBA Creat Oulook Calendar appointment from excel

(OP)
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

RE: VBA Creat Oulook Calendar appointment from excel

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

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
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

RE: VBA Creat Oulook Calendar appointment from excel

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.

RE: VBA Creat Oulook Calendar appointment from excel

CODE --> VBA

Dim ol As Outlook.ApplicationDim ol as Object
'item As AppointmentItem

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

RE: VBA Creat Oulook Calendar appointment from excel

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.

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
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

RE: VBA Creat Oulook Calendar appointment from excel

What is the value of rDate?

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
21/06/17

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
Hi

Thanks for your help so far

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

RE: VBA Creat Oulook Calendar appointment from excel

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
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

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
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

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
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

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
Sorry another question

I cannot find the correct code to add recipients

Is this something you can help with

Thanks

RE: VBA Creat Oulook Calendar appointment from excel

Item.recipients.add ("Joe Smith")

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
Excellent

Many Thanks to all who have helped

RE: VBA Creat Oulook Calendar appointment from excel

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.

RE: VBA Creat Oulook Calendar appointment from excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close