How to Break the 250 Item Limit in Exchange 2003

  • Thread starter Thread starter Eddie McGlone
  • Start date Start date
E

Eddie McGlone

Hi all.

I have some code in a VBA macro which loops through a number of Task Items
stored in an Exchange Public Folder and copies the values of various fields
into an Excel Spreadsheet. The user controls which Items are used by
selection.
Since we moved over to Exchange 2003, the code no longer works if there are
more than 250 items selected. This is due to a limit being placed on the
number of items that a user can have open simultaneously in Exchange 2k3.
I am using for ...next loops to gather the data.
Does anyone know how I can get around this? For example is there any way to
force Outlook to release each Item as it is stepping through the code?

Thanks in advance

Ed
 
You can increase the limit by modifying the registry, at the expense of
increased memory usage:

Your Exchange Server 2003 computer may stop responding after a MAPI client
opens more than the default value of certain server objects:
http://support.microsoft.com/?id=830829

You should also ensure that you are releasing any object variables
appropriately. CDO is also faster when manipulating large collections of
objects.
 
Thanks Eric.

I am aware that I can increase the limit within Exchange server but I am
wary of doing that and anyway, what should I set it to? - the user may
potentially want to report on thousands of items.

I have never used CDO - can it access Task items?

The offending code snippet is below. I'm not an expert coder - what can I do
to explicitly close the items as it moves through the loop?

Thanks for responding.

Ed

Code Snippet
-------------------

Set myOlExp = MyOlApp.ActiveExplorer
Set MyOlsel = myOlExp.Selection
MyListNo = MyOlsel.Count
Dim Itemlist() As String
ReDim Itemlist(MyListNo)
For i = 1 To MyOlsel.Count
Itemlist(i) = MyOlsel(i).Subject
Next i

For j = 1 To MyOlsel.Count
ExcelApp.activesheet.cells(MyRow, MyCol).Select
ExcelApp.ActiveCell.Value = Itemlist(j)
MyRow = MyRow + 1
Next j
For i = 1 To MyOlsel.Count
Itemlist(i) = MyOlsel(i).StartDate 'It fails here once i
reaches 250
Next i
MyRow = 3
MyCol = 2

For j = 1 To MyOlsel.Count
ExcelApp.activesheet.cells(MyRow, MyCol).Select
ExcelApp.ActiveCell.Value = Itemlist(j)
MyRow = MyRow + 1
Next j
 
First, my apologies for mentioning CDO - it of course cannot be used with
Tasks (beyond standard message properties anyway).

That's actually a lot of unnecessary looping, and you don't need the array
either. Try this code and see if it resolves your issues:

Sub OutputSelectedTasksToExcel()
Dim myOlExp As Outlook.Explorer
Dim MyOlsel As Outlook.Selection
Dim objTask As Outlook.TaskItem

Dim objWkb As Excel.Workbook, objWks As Excel.Worksheet
Dim objExcel As Excel.Application
Dim i As Integer, j As Integer

Set myOlExp = Application.ActiveExplorer
Set MyOlsel = myOlExp.Selection

Set objExcel = New Excel.Application
Set objWkb = objExcel.Workbooks.Add
Set objWks = objExcel.ActiveSheet
objExcel.Visible = True

For i = 1 To MyOlsel.Count
Set objTask = MyOlsel.Item(i)
objWks.Cells(i, 1).Value = objTask.Subject
objWks.Cells(i, 2).Value = IIf(objTask.StartDate = #1/1/4501#, "",
objTask.StartDate)
Set objTask = Nothing
Next

Set objWks = Nothing
Set objExcel = Nothing
Set objWkb = Nothing
Set myOlExp = Nothing
Set MyOlsel = Nothing
End Sub

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/
 
Thanks for putting me in my place Dan. I'm getting old or not getting enough
sleep, or was thinking of something else. You are right of course, and I've
dealt with CDO enough with all Outlook item types to know better.
 
Eric, thanks for responding - I have not been at my desk since Thursday
evening.

I realised that there's an excess of looping going on - it came about
through testing after I found out that the code runs fine if all I want to
extract is the subject.

I've tried your code but it failed due to "user defined types" probably
because I don't have a reference to the Excel object library in my project?
(Working in the vbaproject.otm file)

Anyway, I took out ...
Dim objWkb As Excel.Workbook, objWks As Excel.Worksheet
Dim objExcel As Excel.Application
and put in...
Set objExcel = CreateObject("Excel.Application")
Set objWkb = objExcel.Workbooks.Add
Set objWks = objExcel.ActiveSheet

The code then runs but stops when i gets to 247 with a "Type Mismatch".
Exchange server is generating the more than 250 items event. It's as if it's
not closing down the items with...

Set objTask = Nothing

Although in the locals window it is indeed set to nothing.

Most frustrating

Any more light to cast?

Appreciated

Ed
 
Yeah, you need an early bound reference to Excel if you want to explicitly
declare those objects for your variables, else use CreateObject like you are
doing.

The "Type Mismatch" is occurring because somehow a non-Task item is getting
processed. Add an evaluation to ensure that MyOlsel.Item(i).Class = olTask
before assigning a strongly typed variable to it.

All I can suggest with the Exchange item limit is to increase it, at least
to test that you can get farther than 250.

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/
 
Thanks Eric.

In fact the items in the folder are all task items.

I enclosed most of the loop in an If statement which tests for message class
and it fails at the same point - ie after it has checked that the item is
indeed a task item!! Tearing my hair out here!!

This is all to do with the Exchange limit on number of open items so it
looks like I'm going to have to bite the bullet and increase that limit.
What I'm not getting is why this code is not opening only one item at a
time - when an object variable is set to nothing, I would expect that item
to be closed on the server. The other mystery is that if I only access the
subject property of the items, the code runs successfuly without any
apparent limit.

Thanks for all your help.

Ed
 
Back
Top