Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010

    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
            Case scanSuccess
                  MailObject.Move SuccessBox
            .... (etc)
            End Select
        End If
    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?


  2. #2
    Join Date
    Mar 2010
    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.

    Added code:
    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
         myItem.Move FailedBox
    ... 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.

    Any ideas for fixing this?

    Or alternate approaches to my original problem?


  3. #3
    Join Date
    Mar 2010
    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)

    and all is well!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts