Outlook VBA Problem: For...Next Loop with Items

Discussion in 'OT Technology' started by ez4me2c3d, Jul 24, 2007.

  1. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    I am trying to write a macro that will search through a listing of folders, identify the UNREAD items, and mark them as read.

    I have the code written, I'm just stumped as to why the For...Next loop for the Items is limiting itself to three loops??? I have set exactly 5 mail items as unread in each folder, and you can see this from the debug output.

    The VBA code:
    Code:
    Sub Mark_All_As_Read()
        On Error Resume Next
        Set ol = New Outlook.Application
        Set olns = ol.GetNamespace("MAPI")
        Set oInbox = olns.GetDefaultFolder(olFolderInbox)
        Debug.Print "Start of Loop:"
        For Each oFolder In oInbox.Folders
            Debug.Print "    Folder: " & oFolder.Name & " " & oFolder.UnReadItemCount & " UnRead Items | " & oFolder.Items.Restrict("[UnRead]=true").Count & " Real Count"
            For Each oItem In oFolder.Items.Restrict("[UnRead] = True")
                Debug.Print "        Item: " & oItem & " --- UnRead? " & oItem.UnRead
                oItem.UnRead = False
                Debug.Print "              -- Marked item as READ"
            Next
            Debug.Print "    Done with: " & oFolder.Name
        Next
        Debug.Print "Macro Complete!"
    End Sub
    And here is the output of the script:
    Code:
    Start of Loop:
        Folder: Monitoring 5 UnRead Items | 5 Real Count
            Item: [+] : Critical : XXXX : XXXX : [Thresh] Interface Status - (#8)-x.x.202.93 Multilink1 Index 8 --- UnRead? True
                  -- Marked item as READ
            Item: [-] : Critical : XXXX : XXXX : [Thresh] Interface Status - (#8)-x.x.202.93 Multilink1 Index 8 --- UnRead? True
                  -- Marked item as READ
            Item: [+] : Critical : XXXX : XXXX-2801 : [Thresh] SNMP Status - XXXX-2801 --- UnRead? True
                  -- Marked item as READ
        Done with: Monitoring
        Folder: Tickets Closed 5 UnRead Items | 5 Real Count
            Item: Ticket number 021988 for XXXX -Minneapolis has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 022082 for XXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021982 for XXXX Corporation has been closed  --- UnRead? True
                  -- Marked item as READ
        Done with: Tickets Closed
        Folder: Tickets General 5 UnRead Items | 5 Real Count
            Item: SEV 1 escalation XXXX, ID: 022076  --- UnRead? True
                  -- Marked item as READ
            Item: Automated escalation for XXXX Agency  Ticket ID:  021933  --- UnRead? True
                  -- Marked item as READ
            Item: Logged time in ticket activities  --- UnRead? True
                  -- Marked item as READ
        Done with: Tickets General
        Folder: Tickets Opened 5 UnRead Items | 5 Real Count
            Item: A ticket for XXXX XXXX priority of Critical Ticket ID:  022084 has been opened   --- UnRead? True
                  -- Marked item as READ
            Item: A ticket for XXXX priority of Medium Ticket ID:  022083 has been opened   --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 022083 for XXXX has been opened  --- UnRead? True
                  -- Marked item as READ
        Done with: Tickets Opened
        Folder: Tickets Unassigned 5 UnRead Items | 5 Real Count
            Item: Priority Medium Unassigned Ticket Notification for XXXX  --- UnRead? True
                  -- Marked item as READ
            Item: Priority Medium Unassigned Ticket Notification for XXXX  --- UnRead? True
                  -- Marked item as READ
            Item: Priority Critical Unassigned Ticket Notification for XXXX XXXX  --- UnRead? True
                  -- Marked item as READ
        Done with: Tickets Unassigned
    Macro Complete!
    After the Macro runs I am left with 2 remaining UNREAD mail items in each folder.

    Any VBA gurus out there that can lend a hand?


    UPDATE:
    I had one of the folders with 36 UnRead Items, and the other folders all had zero UnRead Items. This is the result:
    Code:
    Start of Loop:
        Folder: Monitoring 0 UnRead Items | 0 Real Count
        Done with: Monitoring
        Folder: Tickets Closed 36 UnRead Items | 36 Real Count
            Item: Ticket number 021899 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021903 for XXXXX, Murphy has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021902 for XXXXX (Cincinnati, OH) has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021909 for XXXXX Financial has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021908 for XXXXX - Atlanta has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021904 for XXXXX - Grand Ave St. Paul has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021912 for XXXXX - Duluth, Ga has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021905 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021910 for XXXXX Software, Inc has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021898 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021913 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021907 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021906 for XXXXX LLC has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021911 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021900 for XXXXX (formerly Coeus) has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021901 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021864 for XXXXX has been closed  --- UnRead? True
                  -- Marked item as READ
            Item: Ticket number 021860 for XXXXX Inc. has been closed  --- UnRead? True
                  -- Marked item as READ
        Done with: Tickets Closed
        Folder: Tickets General 0 UnRead Items | 0 Real Count
        Done with: Tickets General
        Folder: Tickets Opened 0 UnRead Items | 0 Real Count
        Done with: Tickets Opened
        Folder: Tickets Unassigned 0 UnRead Items | 0 Real Count
        Done with: Tickets Unassigned
    Macro Complete!
    It only marked 18 of the 36 Items as Read. WTF?

    UPDATE 2:
    I've noticed a pattern. The macro will always only mark HALF of the actual number of UnRead Items.

    So when I ran the macro in the above example (with 36 Unread items) this is what happened.
    Code:
    UnRead Items	Macro Was Ran	Remaining UnRead Items
    36		Once		18
    18		Twice		9
    9		Three times	4 (It marked 4.5 rounded up)
    4		Four times	2
    2		Five times	1
    1		Six times	0
     
    Last edited: Jul 24, 2007
  2. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    Solution:
    So I worked it out further, and it turns out I needed to make a choppy ass macro to get this thing to act like I want it. Which still isn't that great.
    Code:
    Sub Mark_All_As_Read()
        On Error Resume Next
        Set ol = New Outlook.Application
        Set olns = ol.GetNamespace("MAPI")
        x = 0
        Set oInbox = olns.GetDefaultFolder(olFolderInbox)
        For Each oFolder In oInbox.Folders
            If oFolder.UnReadItemCount > 0 Then
                For i = 1 To oFolder.UnReadItemCount * 1.5
                    If oFolder.Items.Item(i).UnRead Then
                        oFolder.Items.Item(i).UnRead = False
                        x = 0
                    Else
                        x = x + 1
                        If x = 5 Then
                            Exit For
                        End If
                    End If
                Next
                x = 0
            End If
        Next
    End Sub
    So it basically looks at the count of unread items in each folder

    If there are 0, then it skips that folder

    If there are > 0 then it sets a For...Next loop to the number of unread items * 1.5 (so if there are 20 items, then the loop will go for 30) This accounts for some read items mixed in with unread items

    Then it checks each mail item the folder and if it's unread, it marks it as read.

    If there are 5 consecutive read mail items, the loop quits and moves to the next folder.
     
  3. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    It sounds like the unread items are being removed from the inbox list as the macro runs, but the FOR loop isn't backing up and checking the next item that just got moved into the space formerly occupied by the previous item.

    Sounds like you need to just run the loop over and over again until there's nothing left to process.
     
  4. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    deuse- you were right, which lead me to the creation of this new VBA code
    Code:
    Sub Mark_All_As_Read()
        Set ol = New Outlook.Application
        Set olns = ol.GetNamespace("MAPI")
        Set oInbox = olns.GetDefaultFolder(olFolderInbox)
        For Each oFolder In oInbox.Folders
            Do While oFolder.Items.Restrict("[UnRead]=true").Count > 0
                oFolder.Items.Restrict("[UnRead]=true").Item(1).UnRead = False
            Loop
        Next
    End Sub
    It's a lot cleaner and more compact. It doesn't iterate items that are already read. And! It's much faster than my first attempt.

    Thanks for your help.

    Now if there was just a method like such: :)
    Code:
    Sub Mark_All_As_Read()
        Set ol = New Outlook.Application
        Set olns = ol.GetNamespace("MAPI")
        Set oInbox = olns.GetDefaultFolder(olFolderInbox)
        For Each oFolder In oInbox.Folders
            oFolder.MarkAllAsRead
        Next
    End Sub
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well you can right click a folder to Mark them all read. It just doesn't do subfolders.
     
  6. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    I know, but when you have 5 or more folders to "Mark All As Read" on, it gets old, really quick!
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Or you could just read your e-mail :p
     
  8. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    There's this monitoring system that sends out e-mails AND creates tickets.

    If I just monitor the trouble ticket app, I don't have to read them......hm....

    And if you're thinking I should setup a rule to mark them as read... I have something for that as well.

    I have a rule to move those e-mails to the right folders, (i.e., opened tickets, closed tickets, etc.) and the e-mails never hit my inbox because they are processed on the server.

    But if I select to mark them as read in the same rule, then the rule becomes a client rule and the e-mail hits my inbox first, gets marked as read, then gets sent to the correct folder.

    In that scenario all e-mails that hit my inbox also go to my Blackberry. I can't have 300+ emails a day going to my BB.
     

Share This Page