Unanswered: Timing problem driving Outlook from Access
Hi folks - not purely an Access problem here, but not sure where else to go!
I'm using Access 2007 VBA to read messages in an Outlook 2007 inbox - extract data, stuff it in my database, and optionally send replies. It all seems to be working - except that I have what I think is a timing problem which is making the program behave erratically.
I have a sub to scan the inbox - using code I've seen a dozen times on the net. Basically something like (highly abbreviated here)
Dim Inbox as Outlook.MAPIFolder
Dim InboxItems as Outlook.Items
Dim MailObject as Object
(connect to Outlook)
(Set Inbox = ...)
Set InboxItems = Inbox.Items
For Each MailObject in InboxItems
If MailObject.Class = olMail Then
result = ProcessItem(MailObject)
Select Case result
The problem is that if I have, say, 4 items in my Inbox and I run the scan routine it will only handle 2 or maybe 3 of them. The others are simply missed - the code doesn't get to the Processing bit at all. If I then run the scan routine again (the 2 or 3 having been moved out of the inbox by my code) then it will probably find the others. But I might have to run the scan several times to catch all the messages that are sitting in the inbox.
This tells me that the logic and syntax of my code is essentially correct (which is why I've not repeated all of it - there's a lot!). But I can only imagine that there is some sort of timing problem going on here - Outlook and Access are not playing nicely together.
Normally, the Inbox is actually a MAPI folder in gmail. However I have confirmed that I get the same problem when I use a local folder as my "inbox".
The problem seems to be related to the "MailObject.Move" code ... if I comment out all of the lines relating to this (moving the item to a "Success" or "Failed" box, depending on what happens in ProcessItem), then all of the items in the inbox get handled (but not, of course, moved to the right place which means I'll handle them all over again at the next scan!).
Without really knowing what I'm doing, I tried a "DoEvents" inside the end of my loop, but it doesn't seem to make much difference if any.
Has anyone else seen anything like this, and do you have any bright ideas for a solution?
Update: I thought that if the problem is being caused by doing a Move on an Inbox item while I'm looping through the Inbox, I'd separate the two.
Dim DoneObjects as New Collection
Dim FailedObjects as New Collection
Dim myItem as Outlook.MailItem
For Each MailObject in InboxItems
(if Failure then)
(if Success then)
For Each myItem in FailedObjects
... but this gives me Error 424 at the last For Each. I've not used Collections much before, I guess I'm getting my typing wrong. I tried Dim'ing myItem as a generic "Object" but get the same result. It does appear that Items are being added to the Collections, though.
Update ... I never did solve the type problem with my Collections approach, but I went back to the original problem and found a different way of solving it.
Here Working with Members of an Items Collection - about half way down, is a discussion about what happens if you try to delete members of a collection while you loop through it. Basically, it gets confused, and the answer is to loop through the collection backwards so that the deletion (or move, in my case, which comes to the same thing) doesn't mess up the indexing of the remaining items.
Start the loop with
For i = InboxItems.Count to 1 Step -1
Set MailObject = InboxItems(i)