AppointmentItem

  • Thread starter Thread starter Fil
  • Start date Start date
F

Fil

Hi,

I am trying to save the birthdays of some friends programmaticaly from an
Excel spreadsheet into Outlook as AppointmentItems (First column of excel
spreadsheet is the date and the second is the name). I want them to be
AllDayEvent=true and to start at midnight as per the Time Zone where my
friend lives (let say GMT+1 in the example). So I wrote the below code:

----------------------------------------------------------------------------------------
Public Sub test()
Dim otl As New Outlook.Application
Dim cal As Outlook.Folder
Dim newItem As Outlook.AppointmentItem
Dim rec As Outlook.RecurrencePattern
Dim tz_Paris As Outlook.TimeZone
Set tz_Paris = Outlook.TimeZones("Romance Standard Time")
Set cal = otl.GetNamespace("MAPI").Folders("Personal
Folders").Folders("Calendar")
While Not IsEmpty([a1].Offset(i, 0))
Set newItem = cal.Items.Add(olAppointmentItem)
newItem.Subject = "Birthday of " & [a1].Offset(i, 1).Value
Set rec = newItem.GetRecurrencePattern
rec.RecurrenceType = olRecursYearly
rec.DayOfMonth = CInt(Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1,
2))
rec.MonthOfYear = Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1 + 2, 2)
rec.NoEndDate = True
rec.StartTime = [a1].Offset(i, 0).Value
newItem.StartTimeZone = tz_Paris
newItem.EndTimeZone = newItem.StartTimeZone
newItem.StartInStartTimeZone = [a1].Offset(i, 0).Value
newItem.AllDayEvent = True 'Doesn't work
newItem.Duration = 24 * 60 'Doesn't work
newItem.Save
Set newItem = Nothing
Set rec = Nothing
i = i + 1
Wend
End Sub
----------------------------------------------------------------------------------------
When the AppointmentItems are saved in Outlook, they are 30 minutes long and
with start and end time specified as per GMT, even if I tried in 2 different
ways to tell outlook this is an AllDayEvent and to consider times in GMT+1.
My instructions work (I can see they work when I look at the objects in the
watch window) but when they are saved some properties are reset to default.

Do you know that problem? (I remember that in the past I had problems with
Categories, it might be the same issue)
 
Fil said:
Hi,

I am trying to save the birthdays of some friends programmaticaly from an
Excel spreadsheet into Outlook as AppointmentItems (First column of excel
spreadsheet is the date and the second is the name). I want them to be
AllDayEvent=true and to start at midnight as per the Time Zone where my
friend lives (let say GMT+1 in the example). So I wrote the below code:

----------------------------------------------------------------------------------------
Public Sub test()
Dim otl As New Outlook.Application
Dim cal As Outlook.Folder
Dim newItem As Outlook.AppointmentItem
Dim rec As Outlook.RecurrencePattern
Dim tz_Paris As Outlook.TimeZone
Set tz_Paris = Outlook.TimeZones("Romance Standard Time")
Set cal = otl.GetNamespace("MAPI").Folders("Personal
Folders").Folders("Calendar")
While Not IsEmpty([a1].Offset(i, 0))
Set newItem = cal.Items.Add(olAppointmentItem)
newItem.Subject = "Birthday of " & [a1].Offset(i, 1).Value
Set rec = newItem.GetRecurrencePattern
rec.RecurrenceType = olRecursYearly
rec.DayOfMonth = CInt(Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1,
2))
Click to expand...

Another problem: this fails for the 31st of any month
rec.MonthOfYear = Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1 + 2, 2)
rec.NoEndDate = True
rec.StartTime = [a1].Offset(i, 0).Value
newItem.StartTimeZone = tz_Paris
newItem.EndTimeZone = newItem.StartTimeZone
newItem.StartInStartTimeZone = [a1].Offset(i, 0).Value
newItem.AllDayEvent = True 'Doesn't work
newItem.Duration = 24 * 60 'Doesn't work
newItem.Save
Set newItem = Nothing
Set rec = Nothing
i = i + 1
Wend
End Sub
----------------------------------------------------------------------------------------
When the AppointmentItems are saved in Outlook, they are 30 minutes long and
with start and end time specified as per GMT, even if I tried in 2 different
ways to tell outlook this is an AllDayEvent and to consider times in GMT+1.
My instructions work (I can see they work when I look at the objects in the
watch window) but when they are saved some properties are reset to default.

Do you know that problem? (I remember that in the past I had problems with
Categories, it might be the same issue)
Click to expand...
 
You need to set the time zone for the appointment before you set the
recurrence pattern if you want the times entered using that time zone and
not the default. You can just use the appointment.StartTimeZone property.

Then set the type to yearly after getting the recurrence pattern. Then set
the StartTime property of the recurrence pattern. That should be whatever
the date is plus 12:00 AM as the time. Then set Duration.

See if that works.




Fil said:
Fil said:
Hi,

I am trying to save the birthdays of some friends programmaticaly from an
Excel spreadsheet into Outlook as AppointmentItems (First column of excel
spreadsheet is the date and the second is the name). I want them to be
AllDayEvent=true and to start at midnight as per the Time Zone where my
friend lives (let say GMT+1 in the example). So I wrote the below code:

----------------------------------------------------------------------------------------
Public Sub test()
Dim otl As New Outlook.Application
Dim cal As Outlook.Folder
Dim newItem As Outlook.AppointmentItem
Dim rec As Outlook.RecurrencePattern
Dim tz_Paris As Outlook.TimeZone
Set tz_Paris = Outlook.TimeZones("Romance Standard Time")
Set cal = otl.GetNamespace("MAPI").Folders("Personal
Folders").Folders("Calendar")
While Not IsEmpty([a1].Offset(i, 0))
Set newItem = cal.Items.Add(olAppointmentItem)
newItem.Subject = "Birthday of " & [a1].Offset(i, 1).Value
Set rec = newItem.GetRecurrencePattern
rec.RecurrenceType = olRecursYearly
rec.DayOfMonth = CInt(Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"),
1,
2))
Click to expand...

Another problem: this fails for the 31st of any month
rec.MonthOfYear = Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1 +
2, 2)
rec.NoEndDate = True
rec.StartTime = [a1].Offset(i, 0).Value
newItem.StartTimeZone = tz_Paris
newItem.EndTimeZone = newItem.StartTimeZone
newItem.StartInStartTimeZone = [a1].Offset(i, 0).Value
newItem.AllDayEvent = True 'Doesn't work
newItem.Duration = 24 * 60 'Doesn't work
newItem.Save
Set newItem = Nothing
Set rec = Nothing
i = i + 1
Wend
End Sub
----------------------------------------------------------------------------------------
When the AppointmentItems are saved in Outlook, they are 30 minutes long
and
with start and end time specified as per GMT, even if I tried in 2
different
ways to tell outlook this is an AllDayEvent and to consider times in
GMT+1.
My instructions work (I can see they work when I look at the objects in
the
watch window) but when they are saved some properties are reset to
default.

Do you know that problem? (I remember that in the past I had problems
with
Categories, it might be the same issue)
Click to expand...
Click to expand...
 
Thanks for the insight.
I had it working with the following order:

------------------------------------------------------------------------------------
Public Sub test()
Dim otl As New Outlook.Application
Dim cal As Outlook.Folder
Dim newItem As Outlook.AppointmentItem
Dim rec As Outlook.RecurrencePattern
Dim tz_Paris As Outlook.TimeZone
Set tz_Paris = Outlook.TimeZones("Romance Standard Time")
Set cal = otl.GetNamespace("MAPI").Folders("Personal
Folders").Folders("Calendar")
While Not IsEmpty([a1].Offset(i, 0))
Set newItem = cal.Items.Add(olAppointmentItem)
newItem.Subject = "Birthday of " & [a1].Offset(i, 1).Value

newItem.AllDayEvent = True 'Doesn't work

newItem.StartTimeZone = tz_Paris

Set rec = newItem.GetRecurrencePattern

rec.RecurrenceType = olRecursYearly
rec.DayOfMonth = CInt(Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1,
2))
rec.MonthOfYear = Mid(Format([a1].Offset(i, 0).Value, "ddmmyy"), 1 + 2, 2)
rec.StartTime = [a1].Offset(i, 0).Value
rec.NoEndDate = True

newItem.EndTimeZone = newItem.StartTimeZone
newItem.StartInStartTimeZone = [a1].Offset(i, 0).Value

newItem.Save
Set newItem = Nothing
Set rec = Nothing
i = i + 1
Wend
End Su
------------------------------------------------------------------------------------

If I assign the AllDayEvent property later on, it doesn't work.

I still have a problem when the day of the month is a 31. Do you know why?
Thank you
 
Well, since you're not doing what I said I wouldn't expect things to work
correctly. Don't set AllDayEvent, don't set MonthOfYear, don't set
DayOfMonth, just do what I mentioned.
 
Back
Top