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

Try

TimeValue(Range("D14").Text)

HTH,
Bernie
MS Excel MVP


"splodgey" wrote in message
...
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

Thanks Bernie worked a treat
Dave

"Bernie Deitrick" wrote:

Try

TimeValue(Range("D14").Text)

HTH,
Bernie
MS Excel MVP


"splodgey" wrote in message
...
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