Excel to Outlook VBA giving my Calendar Fits
 

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

Excel to Outlook VBA giving my Calendar Fits
 

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


wrote in message
oups.com...
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

Excel to Outlook VBA giving my Calendar Fits
 

That did the trick Andy. Thanks for your help.