Outlook Appointment VBA Issues

[McKilty]

Whoops! I posted this earlier in the wrong newsgroup:

I am writing a routine that will Outlook Appointment items and write
them into a database. When an item is recorded, I write the ID
created in Access into a custom field in the appointment item. This
is kinda working, but there are some strange issues.

I have 22 items in the Calendar. My filter is looking for the past
10
days and the next 10 days, so there will be additional items based on
recurrence. On the last run I did, 28 items were created.

#1 - When I view the custom field in Outlook, only 3 of the 22 items
show the ID that I wrote, yet on a second run where I check to see if
the items have an ID, they do. Why then, are they not showing?

#2 - When I run this a second time, 3 items are added again. These
are not the same items from issue #1

As for why we are doing it this way rather than just linking to the
calendar items, The user also needs to view the data while offline.

Thank you.

Public Function ImportCalendar_TEST()
'Access Calendar items (Past 10 days, Next 10 days
'Add Access-Created Calendar ID
'If Calendar ID field has a value, compare CORE fields values on
ietsm with matching ID, date, & time
'''If changes then save make a copy of existing values, save new
values
'''If No ID, add record; record Access ID in Calendar
'OUTLOOK
Dim objFPRecip As Outlook.Recipient
Dim SafeAppointment, oAppointment
Dim olOutlook As Outlook.Application
Dim nsNameSpace As NameSpace
Dim mItemCollection As Items
Dim myItems As Items
'ACCESS
Dim dbMain As DAO.Database
Dim rsAppointment As DAO.Recordset
'OTHER
Dim sFilter As String
Dim lAppID As Long
sFilter = "[End] >= '" & Format(Date - 10, "yyyy/mm/dd") & "' AND
[Start] <= '" & Format(Date + 10 & " 11:59 PM", "yyyy/mm/dd hh:nn") &
"'"
'LOCAL
Set olOutlook = CreateObject("Outlook.Application")
Set nsNameSpace = olOutlook.GetNamespace("MAPI")
Set myItems = nsNameSpace.GetDefaultFolder(olFolderCalendar).Items
myItems.Sort "[Start]", False
myItems.IncludeRecurrences = True
Set mItemCollection = myItems.Restrict(sFilter)
Set dbMain = CurrentDb
' LOOP OF CALENDAR ITEMS
***************************************************************************
******************
For Each Item In mItemCollection
'APPOINTMENT CHECK
If Item.Class = olAppointment Then
Set SafeAppointment =
CreateObject("Redemption.SafeAppointmentItem")
Set oAppointment = Item
SafeAppointment.Item = oAppointment
If Not IsNumeric(SafeAppointment.UserProperties.Find("APPID"))
Then
'Appointment was never added. Add Appointment to Database
SafeAppointment.UserProperties.Add "APPID", olText, True
Set rsAppointment = dbMain.OpenRecordset("SELECT * FROM
Appointment_TBL;")
rsAppointment.AddNew
rsAppointment("EntryID") = SafeAppointment.EntryID
rsAppointment.Fields("Start") = SafeAppointment.Start
rsAppointment.Fields("StartDate") = Format(SafeAppointment.Start,
"mm-dd-yyyy")
rsAppointment.Fields("StartTime") = Format(SafeAppointment.Start,
"hh:nn ampm")
rsAppointment.Fields("End") = SafeAppointment.End
rsAppointment.Fields("EndDate") = Format(SafeAppointment.End,
"mm/
dd/yyyy")
rsAppointment.Fields("EndTime") = Format(SafeAppointment.End,
"hh:nn AMPM")
rsAppointment("ConversationTopic") =
SafeAppointment.ConversationTopic
rsAppointment("Subject") = SafeAppointment.Subject
rsAppointment("Body") = SafeAppointment.Body
'Add Access ID to Outlook
SafeAppointment.UserProperties.Find("APPID") =
rsAppointment("Appointment_ID")
SafeAppointment.Save
rsAppointment.Update
Set rsAppointment = Nothing
Debug.Print SafeAppointment.UserProperties.Find("APPID") & ":
Created"
Else
Debug.Print SafeAppointment.UserProperties.Find("APPID") & ":
Already Exists"
End If
Set SafeAppointment = Nothing
End If
Next
' END LOOP
***************************************************************************
*************************
End Function

[McKilty]

[Being written before any replies are visible]

So I did some tests and found that if I removed the filter and exclude
recurrences, I can see everything in the Calendar. Subsequent runs do
not add any additional items. So #1 appears that it has something to
do with recurrences. As long as the ID's are recorded somewhere,
that's great. I don't care whether I can see them or not. If I can
create an ID for each occurrence and record it in Outlook somewhere,
that's great.

Issue #2 is still an issue. I need to find a way to eliminate those
items from being recreated. Why are the ID's not being read for
them?

[McKilty]

It appears as if maybe I screwed up the fields prior to getting this
running. It is working fine and those issues were resolved by setting
the ID's to empty text.

However, there is a new issue. If a calendar item is created as a
single instance, but is then changed to a recurring issue, the
recurring issues are not picked up in the database. I think because
the ID I assigned was copied to each recurrence. If it is created as
a recurring issue right from the start, then it is fine.

[McKilty]

Okay, I confirmed that once I assign an ID, then change the item to
recurring, the ID is copied to all of the recurrences. The next time
I import, I need to note that an item was changed to recurring. I
want to create a new item collection filtering on my custom ID, then
changed the ID and add these as new items in my database. Is this
possible?

I changed my filter string to this...

sFilter = "[End] >= '" & Format(Date - 10, "yyyy/mm/dd") & "' AND
[Start] <= '" & Format(Date + 10 & " 11:59 PM", "yyyy/mm/dd hh:nn") &
"' AND [APPID] = '39'"

...but it returns ZERO records. When I remove the APPID filter, it
does work. The COUNT for either filter is 2147483647-- which must be
code since that isn't right.


The goal is to give each recurrence it's own ID. If I can not filter,
I suppose I could just open a new duplicate item collection and update
the ID where the ID matches. This doesn't seem very efficient. Is
there a better way? I thought of writing an array where I'd note the
ID where a new recurring item item appeared, then check all further
items for that ID (meaning it's a duplicate) and then add and update
them. I just don't have a good mastery of arrays.

[Ken Slovak]

A recurring item with no end or limit on number of occurrences will return
that number of entries as it's the largest number a 32-bit signed long will
hold.

Any property you add to the master item will be added to all items, as there
is really only 1 item plus an Exceptions collection. Recurring instances are
calculated from the recurrence pattern and the start settings plus which
occurrence is being accessed.

If an item is modified or deleted it's added to the Exceptions collection.
When you access individual occurrences you have to check for GetOccurrence()
and if that fails try to get the item in the Exceptions collection.
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

Post by McKilty
Okay, I confirmed that once I assign an ID, then change the item to
recurring, the ID is copied to all of the recurrences. The next time
I import, I need to note that an item was changed to recurring. I
want to create a new item collection filtering on my custom ID, then
changed the ID and add these as new items in my database. Is this
possible?
I changed my filter string to this...
sFilter = "[End] >= '" & Format(Date - 10, "yyyy/mm/dd") & "' AND
[Start] <= '" & Format(Date + 10 & " 11:59 PM", "yyyy/mm/dd hh:nn") &
"' AND [APPID] = '39'"
...but it returns ZERO records. When I remove the APPID filter, it
does work. The COUNT for either filter is 2147483647-- which must be
code since that isn't right.
The goal is to give each recurrence it's own ID. If I can not filter,
I suppose I could just open a new duplicate item collection and update
the ID where the ID matches. This doesn't seem very efficient. Is
there a better way? I thought of writing an array where I'd note the
ID where a new recurring item item appeared, then check all further
items for that ID (meaning it's a duplicate) and then add and update
them. I just don't have a good mastery of arrays.

[McKilty]

The way it runs now, I am able to add an my own ID to every occurrence
that doesn't have an occurrence. If I understand you correctly, as
each occurrence get's it's own value an exception is created. It
seems that it does not take affect until after I've released my
itemcollection.

For example, my item collection contains recurrences, so I check if it
has an ID, if it does not, I assign one. When I get to the next
occurrence, it was not updated from the first occurrence, so I can add
another new ID.

If I come across an item that has an ID I assigned, but has since been
named a recurring item, I can remove the ID and save it, but since I'm
still in that collection, the subitems will still have the ID. I
would have to recreate the item collection and then assign ID's to the
occurrences. I think that would work.

However, that only works for items within the date range I'm looking
at. Once I get to the next date range, I'll have items with duplicate
ID's of the primary task. I can't use the same method for updating
all of the occurrences because I will no longer know that this item is
recurring. That actually applies to every item...

Hmm...

What if I did this for every recurring item I found? If an item is
recurring, then remove the ID, save it, and open the item collection
again. Any occurrence of that item would be updated and would be able
to be updated again. Every occurrence that has a different ID would
remain the same...

I think there are two issues here. One, I would need to add that same
ID back to that item it was removed from-- easy, create a new fields
and store the ID there and check for it on the next run. Two, every
exception would look like a recurring ID, so it would strip the ID.

FINALLY...
Is there a way to distinguish between an original item and an
exception? If so, then I think I have this import licked.

phew...

[Ken Slovak]

The Exceptions collection will only hold exceptions, so that's how you
determine what's what. If it's not an exception you can get it using
GetOccurrence(), that will fail if it's an exception.
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

Post by McKilty
The way it runs now, I am able to add an my own ID to every occurrence
that doesn't have an occurrence. If I understand you correctly, as
each occurrence get's it's own value an exception is created. It
seems that it does not take affect until after I've released my
itemcollection.
For example, my item collection contains recurrences, so I check if it
has an ID, if it does not, I assign one. When I get to the next
occurrence, it was not updated from the first occurrence, so I can add
another new ID.
If I come across an item that has an ID I assigned, but has since been
named a recurring item, I can remove the ID and save it, but since I'm
still in that collection, the subitems will still have the ID. I
would have to recreate the item collection and then assign ID's to the
occurrences. I think that would work.
However, that only works for items within the date range I'm looking
at. Once I get to the next date range, I'll have items with duplicate
ID's of the primary task. I can't use the same method for updating
all of the occurrences because I will no longer know that this item is
recurring. That actually applies to every item...
Hmm...
What if I did this for every recurring item I found? If an item is
recurring, then remove the ID, save it, and open the item collection
again. Any occurrence of that item would be updated and would be able
to be updated again. Every occurrence that has a different ID would
remain the same...
I think there are two issues here. One, I would need to add that same
ID back to that item it was removed from-- easy, create a new fields
and store the ID there and check for it on the next run. Two, every
exception would look like a recurring ID, so it would strip the ID.
FINALLY...
Is there a way to distinguish between an original item and an
exception? If so, then I think I have this import licked.
phew...

[McKilty]

Thanks, I'm racking my brain trying to figure something out.
Hopefully I get work that into a logical solution.

[McKilty]

Is there a way to assign a value via code so that it adds it as a new
exception? In other words, can I add my initial ID not to the parent,
but to the recurrence only?

This would be the same as editing an Outlook item and specifying you
want to update the occurrence and not the series. This would be the
end all solution I believe...

[Ken Slovak]

Have you looked at the GetOccurrence() method I keep mentioning?
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

Post by McKilty
Is there a way to assign a value via code so that it adds it as a new
exception? In other words, can I add my initial ID not to the parent,
but to the recurrence only?
This would be the same as editing an Outlook item and specifying you
want to update the occurrence and not the series. This would be the
end all solution I believe...

[McKilty]

Yes, but I cannot figure out how I would use that or how it works.

expression.GetOccurrence(StartDate)
expression: Required. An expression that returns one of the objects in
the Applies To list.
StartDate: Required Date that represents local time.

Would I use the current date/time to retrieve the next occurrence?
Would I use the next date/time that would occur if it one exists?

'Get the recurrence pattern for this appointment and obtain the
occurrence for 3/12/03.
myDate = #3/12/2003 3:00:00 PM#
Set myRecurrPatt = myApptItem.GetRecurrencePattern
Set myOddApptItem = myRecurrPatt.GetOccurrence(myDate)


Also, I'm not sure how to handle the failure. I was checking of the
error description was "You changed one of the recurrences of this
item, and this instance no longer exists. Close any open items and try
again." but that doesn't seem accurate, so I think the code is wrong.
I had to use the description because the error number was different
every time...

--------------------------------------------------------------------------------
I thought I came up with another solution, but I'm unsure. If the
field is blank, set it to "A". If the field is "A", set it to an ID
and add it to the database. If the field is a number, it is an ID and
has been set. I'm just afraid that when I set it from "A" to an ID,
it will do that to all items not in my current item collection.

---------------------------------------------------------------------------------

Help on either of those would be appreciated. I'm going to work on
GetOccurrence and see if can get it to work using the current date
time.

[McKilty]

I think it's working with GetOccurrence. Let me know if you would have
done this differently:

Dim myRecurrPatt As Outlook.RecurrencePattern
Dim myOddApptItem As Outlook.AppointmentItem

Set myRecurrPatt = SafeAppointment.GetRecurrencePattern

On Error Resume Next
Set myOddApptItem = myRecurrPatt.GetOccurrence(Date)
On Error GoTo 0

If myOddApptItem Is Nothing Then
'This is an exception.
Else
'This is not an exception.
End if

[McKilty]

I spoke too soon. It appeared to work when new occurrences outside
the original date range are viewed. However, when an item starts as a
single instance, gets an ID, and then is changed to a recurring item,
it fails.

ITEM
Subject: Test A - Tuesday - Single
Date 8/24

Routine runs and assigns an ID.

ITEM ALTERED MANUALLY in OUTLOOK
Subject: Test A - Tuesday - Recurring
Date 8/24
Date 8/25 - recurrence
Date 8/26 - recurrence

When the routine runs again, all three appear as an Exception using
this code:

Set myRecurrPatt = SafeAppointment.GetRecurrencePattern

On Error Resume Next
Set myOddApptItem = myRecurrPatt.GetOccurrence(Date)
On Error GoTo 0

If myOddApptItem Is Nothing Then
'EXCEPTION
End if

[Ken Slovak]

For GetOccurrence() you have to start at the original start and then use the
RecurrencePattern and calculate to the next instance, and so on. Each
calculation is iterative. If you don't get an item from the call you can
check the Exceptions collection for an item with an OriginalDate matching
your calculated setting. then you have the Exception and can get the new
AppointmentItem properties and check to see if it was deleted.
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

Post by McKilty
I spoke too soon. It appeared to work when new occurrences outside
the original date range are viewed. However, when an item starts as a
single instance, gets an ID, and then is changed to a recurring item,
it fails.
ITEM
Subject: Test A - Tuesday - Single
Date 8/24
Routine runs and assigns an ID.
ITEM ALTERED MANUALLY in OUTLOOK
Subject: Test A - Tuesday - Recurring
Date 8/24
Date 8/25 - recurrence
Date 8/26 - recurrence
When the routine runs again, all three appear as an Exception using
Set myRecurrPatt = SafeAppointment.GetRecurrencePattern
On Error Resume Next
Set myOddApptItem = myRecurrPatt.GetOccurrence(Date)
On Error GoTo 0
If myOddApptItem Is Nothing Then
'EXCEPTION
End if

[McKilty]

Hmmm..Correct me if I'm wrong, but that seems to be a solution only if
the date/time changed. If the date/time stayed the same, it seems it
would still be found. What if it wasn't that date/time that changed?

[Ken Slovak]

It doesn't matter. If the time is the same and the item is an exception you
wouldn't find it using GetOccurrence() but you would find it using the
Exceptions collection and the original start. Even if it hasn't changed it's
still the original start. The appointment exposed there has an instance
specific properties.
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

Post by McKilty
Hmmm..Correct me if I'm wrong, but that seems to be a solution only if
the date/time changed. If the date/time stayed the same, it seems it
would still be found. What if it wasn't that date/time that changed?