Excel to Outlook VBA giving my Calendar Fits

[micheldevon]

I've been researching how to create a VBA macro that will use Excel
Data to create an Outlook appointment. So far I've cobbled together a
really great working model that does almost everything I need it too.
Almost.

What I don't get is how to set the start time. When I put in a start
time Outlook seems to ignore it and starts the appointment at 2:24 PM
or 9:24 PM.

If I set the AllDayEvent to True then the day "begins" at 2:24 PM and
ends at the same time on the next day.

I'm sure part of the problem is that my Start is partly a cell
reference and partly not, so I'm not sure how to join the two in VBA.
When I type in #mm/dd/yy hh:mm am/pm# I get the correct time and date,
but without the macro reading the cell reference the entire code is
useless.

I'm very much a novice here, so any help (in plain english) would be
greatly appreciated.

------Code Begins Here------

Sub CreateAppointment()

Dim myOlapp As Object
Dim myitem As Object

Set myOlapp = CreateObject("Outlook.Application")
Set myitem = myOlapp.CreateItem(1)



With myitem
.Body = "Blah Blah Blah..."
.AllDayEvent = False
.Start = Range("='MODIFIER_GRID'!I12") + TimeValue("7:00:00
AM")
.End = .Start + TimeValue("00:30:00")
.Subject = "Final Due Date - C#: " &
Range("='MODIFIER_GRID'!G12")
.ReminderMinutesBeforeStart = 10
.Save

End With

Set myitem = Nothing
Set myOlapp = Nothing

End Sub

 

[Andrew Houghton]

What you could do is to evaluate the cell which contains the date first of
all and then use it in a format and cdate function:

Dim dtDate As Date
Range("'MODIFIER_GRID'!I12") .Select
dtDate = Format(CDate(ActiveCell.Value), "YYYY-MM-DD") & " 07:00:00"

With myitem

.Body = "Blah Blah Blah..."
.AllDayEvent = False
.Start = dtDate

I haven't tried using this to create appointments but I have used this
method to create valid dates and times in SQL databases.

Hope this helps

Andy

 

[micheldevon]

That did the trick Andy. Thanks for your help.