Excel Outlook Calendar code works.... 1 question
 

Hi all,

I have the following code that adds an calendar appointment to Outlook from
excel, it works great however is there a way I can substitute the .Start =
Range("B1") & " 1:30:00 PM" part so instead of having to manually add the
start and finish time into the code this can link to a cell reference? (the "
1:30:00 PM" bit).

Sub AddToOLCalendar1()

Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long

Set objOL = CreateObject("Outlook.Application")

Sheets("Sheet1").Select
lngRow = 4

If ActiveSheet.Cells(lngRow, 2).Text < "" Then
Set objItem = objOL.CreateItem(1) ' constant olAppointmentItem = 1

With objItem
.Start = Range("B1") & " 1:30:00 PM"
.End = Range("B1") & " 5:00:00 PM"
.Subject = Range("C1")
.Location = Range("F1")
.Save
End With
End If
lngRow = lngRow + 1

Set objItem = Nothing
Set objOL = Nothing
MsgBox " Added to Outlook "
End Sub

Many thanks for any help or direction.
Regards
Andy

Excel Outlook Calendar code works.... 1 question
 

Solved it, hope this helps others!

..Start = Format(Range("D1").Value, "hh:mm AMPM") + _
Format(Range("B1").Value, "mm/dd/yyyy")

"Andy the yeti" wrote:

Hi all,

I have the following code that adds an calendar appointment to Outlook from
excel, it works great however is there a way I can substitute the .Start =
Range("B1") & " 1:30:00 PM" part so instead of having to manually add the
start and finish time into the code this can link to a cell reference? (the "
1:30:00 PM" bit).

Sub AddToOLCalendar1()

Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long

Set objOL = CreateObject("Outlook.Application")

Sheets("Sheet1").Select
lngRow = 4

If ActiveSheet.Cells(lngRow, 2).Text < "" Then
Set objItem = objOL.CreateItem(1) ' constant olAppointmentItem = 1

With objItem
.Start = Range("B1") & " 1:30:00 PM"
.End = Range("B1") & " 5:00:00 PM"
.Subject = Range("C1")
.Location = Range("F1")
.Save
End With
End If
lngRow = lngRow + 1

Set objItem = Nothing
Set objOL = Nothing
MsgBox " Added to Outlook "
End Sub

Many thanks for any help or direction.
Regards
Andy