dond123
asked on
Looping through Outlook Calendar Items in VBA (MS Access)
Our team uses a custom built task management software built in MS Access. We also import appointments from Outlook into the database since there are a lot of meetings that need to be included in our time management reporting to Sr. Leadership of the team,
Up until now the code below has worked relatively well. But, now it is suddenly not working for me and a few other people. It is erroring out on the line "For Each itm in Itms" as a type mis-match error. To make matters more interesting, it errors out the first time through and then will work the next time. The third time it will fail. The forth time it will work, etc.
We are running MS Access 2003 and MS Outlook 2003 (both with SP3) remoted into our team server that are running Windows Server 2008.
Public Sub GrabAppointments(dteStart As Date, dteEnd As Date)
Dim OUT As Outlook.Application
Dim itms As Outlook.Items
Dim itm As Outlook.AppointmentItem
Dim strLoggedIn As String
Dim ns As Outlook.NameSpace
Set OUT = New Outlook.Application
Set ns = Outlook.GetNameSpace("MAPI ")
Dim rst As New ADODB.recordset
Dim rstTarget As New ADODB.recordset
Dim dteMax As Date 'stores latest event with Project assignment
Dim intCount As Long 'to count current appointments
strLoggedIn = LoggedIn()
If ns.CurrentUser = DLookup("OutlookUserName", "tblUsers", "User='" & strLoggedIn & "'") Then
DoCmd.RunSQL "Delete * FROM tblTempAppointments;"
rst.Open "Select * FROM tblTempAppointments", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set itms = ns.GetDefaultFolder(olFold erCalendar ).Items
itms.IncludeRecurrences = True
itms.Sort "[Start]"
Set itms = itms.Restrict("[Start] >= '" & Format(dteStart, "mm/dd/yyyy") & "' AND [End] <='" & Format(dteEnd, "mm/dd/yyyy") & "'")
'*** The Next Line is erroring out *******
For Each itm In itms
If itm.Sensitivity <> olPrivate Then
rst.AddNew
rst("RAT") = strLoggedIn
rst("OutlookID") = itm.EntryID
rst("StartDate") = Format(itm.Start, "mm/dd/yyyy")
rst("EndDate") = IIf(itm.AlLDayEvent = True, Format(itm.End - 1, "mm/dd/yyyy"), Format(itm.End, "mm/dd/yyyy"))
rst("StartTime") = Format(itm.Start, "h:m")
rst("EndTime") = IIf(itm.AlLDayEvent = True, "11:59", Format(itm.End, "h:m"))
rst("BusyStatus") = TranslateOlBusyStatus(itm. BusyStatus )
Select Case itm.Sensitivity
Case olConfidential
rst("Subject") = "Confidential Appointment"
rst("Location") = ""
Case olNormal
rst("Subject") = itm.Subject
rst("Location") = itm.Location
Case olPersonal
rst("Subject") = "Personal Appointment"
rst("Location") = ""
Case olPrivate
rst("Subject") = "Private Appointment"
rst("Location") = ""
End Select
rst("AlLDayEvent") = itm.AlLDayEvent
rst.Update
End If
Next
Set rst = Nothing
********* Code that does that uses the new data to update the existing data via ADO recordsets ***********
Set OUT = Nothing
Set ns = Nothing
Set itms = Nothing
Set itm = Nothing
Set rst = Nothing
Any help would be greatly appreciated.
Thanks,
Amy Young
Up until now the code below has worked relatively well. But, now it is suddenly not working for me and a few other people. It is erroring out on the line "For Each itm in Itms" as a type mis-match error. To make matters more interesting, it errors out the first time through and then will work the next time. The third time it will fail. The forth time it will work, etc.
We are running MS Access 2003 and MS Outlook 2003 (both with SP3) remoted into our team server that are running Windows Server 2008.
Public Sub GrabAppointments(dteStart As Date, dteEnd As Date)
Dim OUT As Outlook.Application
Dim itms As Outlook.Items
Dim itm As Outlook.AppointmentItem
Dim strLoggedIn As String
Dim ns As Outlook.NameSpace
Set OUT = New Outlook.Application
Set ns = Outlook.GetNameSpace("MAPI
Dim rst As New ADODB.recordset
Dim rstTarget As New ADODB.recordset
Dim dteMax As Date 'stores latest event with Project assignment
Dim intCount As Long 'to count current appointments
strLoggedIn = LoggedIn()
If ns.CurrentUser = DLookup("OutlookUserName",
DoCmd.RunSQL "Delete * FROM tblTempAppointments;"
rst.Open "Select * FROM tblTempAppointments", CurrentProject.Connection,
Set itms = ns.GetDefaultFolder(olFold
itms.IncludeRecurrences = True
itms.Sort "[Start]"
Set itms = itms.Restrict("[Start] >= '" & Format(dteStart, "mm/dd/yyyy") & "' AND [End] <='" & Format(dteEnd, "mm/dd/yyyy") & "'")
'*** The Next Line is erroring out *******
For Each itm In itms
If itm.Sensitivity <> olPrivate Then
rst.AddNew
rst("RAT") = strLoggedIn
rst("OutlookID") = itm.EntryID
rst("StartDate") = Format(itm.Start, "mm/dd/yyyy")
rst("EndDate") = IIf(itm.AlLDayEvent = True, Format(itm.End - 1, "mm/dd/yyyy"), Format(itm.End, "mm/dd/yyyy"))
rst("StartTime") = Format(itm.Start, "h:m")
rst("EndTime") = IIf(itm.AlLDayEvent = True, "11:59", Format(itm.End, "h:m"))
rst("BusyStatus") = TranslateOlBusyStatus(itm.
Select Case itm.Sensitivity
Case olConfidential
rst("Subject") = "Confidential Appointment"
rst("Location") = ""
Case olNormal
rst("Subject") = itm.Subject
rst("Location") = itm.Location
Case olPersonal
rst("Subject") = "Personal Appointment"
rst("Location") = ""
Case olPrivate
rst("Subject") = "Private Appointment"
rst("Location") = ""
End Select
rst("AlLDayEvent") = itm.AlLDayEvent
rst.Update
End If
Next
Set rst = Nothing
********* Code that does that uses the new data to update the existing data via ADO recordsets ***********
Set OUT = Nothing
Set ns = Nothing
Set itms = Nothing
Set itm = Nothing
Set rst = Nothing
Any help would be greatly appreciated.
Thanks,
Amy Young
ASKER
Thanks PhilAI. I tried that. None of the citm in itms are resolving as an Outlook.AppointmentItem, so nothing us updated.
Any idea how I can figure out what item type the citm is? I tried citm.type. No luck.
That does appear to be related to the problem since when I comment out lines 29 and 57 now, it gives me the same type mis-match error on line 30.
Any idea how I can figure out what item type the citm is? I tried citm.type. No luck.
That does appear to be related to the problem since when I comment out lines 29 and 57 now, it gives me the same type mis-match error on line 30.
Try using the function TypeName(citm) to find out the type.
Sorry it has taken me so long to reply - had to go out.
Sorry it has taken me so long to reply - had to go out.
To check if an item is an appointment item try one of these methods ....
1) If oItem.Class = Outlook.OlObjectClass.olAp
End If
Or
2) If Item.MessageClass.ToString
End If
ASKER
PhilAI's solution for finding out if the type of the citm in question.
? TypeName(citm) returned "AppointmentItem" so I modified line 29 in his first suggestion to read:
If TypeName(citm) = "AppointmentItem" Then
That works . . . again, only the 1st, 3rd, 5th, etc. time through.
Now it is giving me the type mis-match error on line 30 in the above code.
Here's what I know:
It is the first item in the list that is causing the issue on both my computer (and thus, my calendar) and another team member's computer (and thus, her calendar)
Yesterday, when the same issue was happening, the list of appointments would have started on the 8/15/2010.
Today, the list will start on the 8/16/2010.
The other team member and I do not share any appointments on the 8/16/2010 or 8/15/2010.
The same code works fine on my computer. It does not work when I have remoted into our server and attempt to run the code, even though the calendar and versions are the same.
The same code works fine on another team member's computer, and fine when he logs into our server and runs the code.
I've tried working with Outlook Redemption to grab calendar information and have had no luck.
Any ideas?
Thanks for all the help.
? TypeName(citm) returned "AppointmentItem" so I modified line 29 in his first suggestion to read:
If TypeName(citm) = "AppointmentItem" Then
That works . . . again, only the 1st, 3rd, 5th, etc. time through.
Now it is giving me the type mis-match error on line 30 in the above code.
Here's what I know:
It is the first item in the list that is causing the issue on both my computer (and thus, my calendar) and another team member's computer (and thus, her calendar)
Yesterday, when the same issue was happening, the list of appointments would have started on the 8/15/2010.
Today, the list will start on the 8/16/2010.
The other team member and I do not share any appointments on the 8/16/2010 or 8/15/2010.
The same code works fine on my computer. It does not work when I have remoted into our server and attempt to run the code, even though the calendar and versions are the same.
The same code works fine on another team member's computer, and fine when he logs into our server and runs the code.
I've tried working with Outlook Redemption to grab calendar information and have had no luck.
Any ideas?
Thanks for all the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The one difference it seems between your code and my code is that I include recurring events.
itms.IncludeRecurrences = True
Thanks again for all the help!
itms.IncludeRecurrences = True
Thanks again for all the help!
I noticed that. And tested it with recurrences also. Even created a recurrence that got picked up. And it still works!?
In fact, I have now left my code to pick up recurrences because I thought it would be a good idea - thanks!
Sorry to bombard you with copmments, but I wonder whether the data is corrupt? Have you tired stepping through the code in Debug mode and checking the object using the Immediate window?
ASKER
Yep. I step through code. It breaks on the first interation through the code at that line. And the plot thickens.
In an attempt to get past the type mis-match error, I change my itm variable to a variant. And success! I got past the type mis-match error.
The code breaks again on this line: rst("OutlookID") = itm.EntryID
It returns Run-time error 287 (Application-defined or object-defined error).
Using the immediate window, I can successfully print to the debug window:
itm.start
itm.subject
itm.end
itm.busystatus
etc.
The one item I can't seem to capture is itm.entryID.
I use that field later on to determine if the new data should replace existing data in the database.
Any ideas?
Thanks!
In an attempt to get past the type mis-match error, I change my itm variable to a variant. And success! I got past the type mis-match error.
The code breaks again on this line: rst("OutlookID") = itm.EntryID
It returns Run-time error 287 (Application-defined or object-defined error).
Using the immediate window, I can successfully print to the debug window:
itm.start
itm.subject
itm.end
itm.busystatus
etc.
The one item I can't seem to capture is itm.entryID.
I use that field later on to determine if the new data should replace existing data in the database.
Any ideas?
Thanks!
Have a look at this?
http://msdn.microsoft.com/en-us/library/bb175171(office.12).aspx
http://msdn.microsoft.com/en-us/library/bb175171(office.12).aspx
Sorry, forgot to say why. It seems the EntryID property has changed slightly - not sure how this affects you but it certainly could be the reason. Do you know if the error occurs on a recurrence appointment? Perhaps this is a versioning problem? Do you have different version of Outlook?
ASKER
Thanks for the tip. Did see that comment about the MAPI entryID. We are not running MS Office 2007 on the server, so that shouldn't be the issue.
I verified the version of Outlook across the different workstations and the serers. Same Version. Same Service Pack.
And here's the really fun part. There is no longer any issue. I've modified the code as you suggestion PhilAI, but that's only on my development version of the database. The original user that had the problem also reports that the problem is resolved and she is running the old code.
Sigh. At least it is fixed for the moment. I'll let you know if it comes back up on Monday.
Thanks for the help!
I verified the version of Outlook across the different workstations and the serers. Same Version. Same Service Pack.
And here's the really fun part. There is no longer any issue. I've modified the code as you suggestion PhilAI, but that's only on my development version of the database. The original user that had the problem also reports that the problem is resolved and she is running the old code.
Sigh. At least it is fixed for the moment. I'll let you know if it comes back up on Monday.
Thanks for the help!
Very strange, but it could be down to the appointments in the calendar. Keep running this code daily for a week - if all is good. I'd love my answer accepted. Thanks.
ASKER
PhilAI,
Your solution (along with instructing the users to make sure to open Outlook on the server before running the code) seems to have resolved the issue. I think it was a combination of the code and having Outlook opened based upon some additional information I found online.
Thanks,
Your solution (along with instructing the users to make sure to open Outlook on the server before running the code) seems to have resolved the issue. I think it was a combination of the code and having Outlook opened based upon some additional information I found online.
Thanks,
This variable is not restricted to Outlook.AppointmentItem objects so if you change the code as below you should see success for now.
It's not the ideal solution for you, but it should stop the error happening... I hope!
N.B. I have tagged the lines I've changed with a comment at the end like this ' *** NEW LINE ***
Open in new window