VBOffice

Limit the Size of Loops

There could be a limit of how many items you can access in a single loop. See how to handle that situation.

Last modified: 2006/12/02 | Accessed: 25.258  | #36
◀ Previous sample Next sample ▶
Reporter Reporter
VBOffice Reporter is an easy to use tool for data analysis and reporting in Outlook. A single click, for instance, allows you to see the number of hours planned for meetings the next month.

Sometimes it isn't possible to loop through more than about 250 items because Outlook doesn't dispose the object references - although you set the variable explicitly to Nothing. This is also know as memory leak.

To solve the issue ensure the object variable goes out of scope, this will definitely dispose all references.


tip  How to add macros to Outlook
Public Sub LoopFolder(Folder As Outlook.MAPIFolder)
  Dim Cnt As Long
  Dim Limit As Long
  Dim CountFrom As Long
  Dim CountTo As Long
  Dim Done As Long
  Dim Items As Outlook.Items

  Set Items = Folder.Items
  Cnt = Items.Count

  ' Set the limit per loop
  Limit = 250

  If Cnt <= Limit Then
    LoopItems Items, 1, Cnt
  Else
    Do While Done < Cnt
      CountFrom = Done + 1
      CountTo = Done + Limit
      LoopItems Items, CountFrom, CountTo
      Done = CountTo
      If Done = Cnt Then
        Exit Do
      ElseIf Cnt - Done <= Limit Then
        Limit = Cnt - Done
      End If
    Loop
  End If
End Sub

Private Sub LoopItems(Items As Outlook.Items, _
  ByVal CountFrom As Long, _
  ByVal CountTo As Long _
)
  Dim i As Long
  Dim obj As Object
  Dim Mail As Outlook.MailItem

  For i = CountFrom To CountTo
    Set obj = Items.Item(i)
    If TypeOf obj Is Outlook.MailItem Then
      Set Mail = obj
      ' do anythign with the item
    End If
  Next
End Sub
ReplyAll ReplyAll
ReplyAll alerts you before unintentionally replying all, or if you are a confidential BCC recipient of the e-mail.
email  Send a message