Pass a time value from Excel to Outlook appointment

S

splodgey

Hi Hope someone can help me out.
I've produced a Macro that sends an appointment to Outlook using the
following code. What I'd like to do is have the appointment time picked up
from a cell rather than TimeValue ("17:00:00). Everytime I change it to
TimeValue ("D14) I get a run time error 13 Type mismatch
Any ideas where I'm going wong!!

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
..Start = Sheet4.Range("B14").Value
..End = Sheet4.Range("C14").Value + TimeValue("17:00:00")
..AllDayEvent = True
..Subject = Sheet4.Range("G3") + " " + Sheet4.Range("M14").Value
..Body = Sheet4.Range("O14").Value
..BusyStatus = olFree
..RequiredAttendees = Sheet4.Range("C4") + ", " + Sheet4.Range("B4").Value
..Recipients.ResolveAll
..MeetingStatus = Outlook.OlMeetingStatus.olMeeting
..Save
..Display
..Send
End With
Set olApt = Nothing
Set olApp = Nothing
MsgBox "Your request for leave has been sent to your line manager for
approval.", , "For Information"

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook private calendar 1
Create Appointment in Outlook 1
Problem with VBA code - Can't figure out what is wrong 1
Problem with code 1
Copying Excel schedules to different Outlook Calendars 0
make appointment in outlook using data in excel -- set recurrence 0
Problem Sending olAppointmentItem 0
Add label colour to outlook appoinment 1

Top