Pass a time value from Excel to Outlook appointment

[Guest]

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

 

[Bernie Deitrick]

Try

TimeValue(Range("D14").Text)

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie worked a treat
Dave

Try

TimeValue(Range("D14").Text)

HTH,
Bernie
MS Excel MVP