Memory management problem

[Culverin]

Hello:

I'm developing a macro that will delete every unused appointment item in a
mailbox that is older than today.
The appointment items are from a custom form. There's a memory management
problem I don't understand and am
unable to solve. Stepping through the code using the Script Debugger, I see
the code for the custom form for
a few steps when the assignment statement for "DeleteDate" executes (line
(A)). I think that an instance of
the form is created in memory whenever a property of the custom form is
referenced for the first time. Since
"TZStartTime" is a custom field on the form, Outlook must place the item in
memory. When this happens, about
1.5K to 2.3K of memory is used. However, I have been unable to determine
how to release this memory when
execution of the While/Wend loop goes to the next item. Consequently,
system memory gets used up rather quickly.
I have 42,000 items to process and can never get past about 1,800.

If I comment out line (A), I see the same thing happening on line (B) for an
item delete. I believe that the
appointment item form must be placed memory before deletion.

This is my first VBA macro for Outlook and I'm hoping that someone will be
able to explain what is happening and
how to release the memory. Any help will be greatly appreciated. Thank you.

Here's the relevant code:

..
..
..
On Error Resume Next

Set nns = Application.GetNameSpace("MAPI")
Set CTL = GetInspector.ModifiedFormPages("Delete Unused Appointments")

Set MyMailbox = nns.Folders("Mailbox -
NAT-CSD-Appointments").Folders("Offices").Folders()
OfficeCount = MyMailbox.Count

For IXA = 1 to OfficeCount
Set MyOffices = nns.Folders("Mailbox -
NAT-CSD-Appointments").Folders("Offices").Folders(IXA).Folders()
CalendarCount = MyOffices.Count

For IXB = 1 to CalendarCount
Set MyCalendars = nns.Folders("Mailbox -
NAT-CSD-Appointments").Folders("Offices").Folders(IXA).Folders(IXB)
ItemCount = MyCalendars.Items.Count

IXC = 1
IXD = 0

While ItemCount > IXD
(A) DeleteDate = MyCalendars.Items(IXC).UserProperties("TZStartTime")
NumDays = DateDiff("d", DeleteDate, Now)
ItemStatus = MyCalendars.Items(IXC).BusyStatus

If NumDays > 0 And ItemStatus = 0 Then
(B) MyCalendars.Items(IXC).Delete
Else
IXC = IXC + 1
End If

IXD = IXD + 1

Wend

Set MyCalendars = Nothing

Next

Set MyOffices = Nothing

Next
..
..
..

 

[Michael Bauer]

Am Sun, 11 Jun 2006 08:30:02 -0700 schrieb Culverin:

That´s the oddest loop I´ve ever seen

In that scenario, i.e. deleting some of a collection´s items only, you must
use a backwards loop:

For ixc=MyCalendar.Items.Count To 1 Step-1
....
If NumDays>0 And ItemStatus=0 Then
MyCalendar.Items(ixc).Delete
Endif
Next

 

[Laughing John]

Hi,

Outlook has a nasty habit of actually opening items when you reference
properties on them, loading the form into memory and even firing any vbscript
code behind them (if it's a custom form).

There are a few ways to get around this. The easiest (IMHO) is to use CDO -
this is a MAPI wrapper and therefore you can get properties from items ina
folder without 'opening' them and it will be much lighter on memory (and
quicker). CDO is an optional component of the Office install. Reference is
here:
http://msdn.microsoft.com/library/d...html/78bd7f77-47e7-43ef-91fd-54abc7ca6a00.asp

You will still need to count the loop backwards as pointed out in the other
reply.

It is also available on the Exchange Server. The new Table object in OL2007
will let you do much the same thing.

Another possibility is to try referencing each message individually, opening
them using code, and then closing them (which should in theory at least free
the memory).


LJ

 

[Culverin]

John, thanks to you and Michael for your replies.

Unfortunately, it doesn't appear that we have CDO installed here. I've also
tried the backwards loop with no result. Lastly, unless I've coded it
incorrectly, it would seem that doing a MyCalendars.Items(IXC).Open and then
a MyCalendars.Items(IXC).Close after the delete also doesn't work. Are there
any other alternatives? Perhaps an Open on the instance of the Message
rather than the Item? If so, how do I code that please? Thanks.

 

[Michael Bauer]

Am Tue, 13 Jun 2006 12:11:02 -0700 schrieb Culverin:

CDO is an optional component from your Outlook/Office cd that isn´t
installed by default.

OL has known problems with a memory leak. But at first I´d try to use more
variables instead of calling references again and again. And as long as the
code doesn´t do what you want to I wouldn´t use the On Error Resume Next
because there might be errors. (For 42,000 items the performance is another,
important issue and therefore I´d also suggest to use CDO or Redemption from
www.dimastr.com.)

Here´s your code in another way:

'On Error Resume Next
Dim nns As Outlook.NameSpace
Dim MyMailbox As Outlook.Folders
Dim MyOffices As Outlook.Folders
Dim MyCalendars As Outlook.Folders
Dim MailboxFolder As Outlook.MAPIFolder
Dim OfficeFolder As Outlook.MAPIFolder
Dim CalendarFolder As Outlook.MAPIFolder
Dim Appointment As Outlook.AppointmentItem
Dim i As Long
Dim DeleteDate As Date
Dim NumDays As Long
Dim ItemStatus As Long

Set nns = Application.GetNamespace("MAPI")

Set MyMailbox = nns _
.Folders("Mailbox - NAT-CSD-Appointments") _
.Folders("Offices").Folders()

Set MailboxFolder = MyMailbox.GetFirst
While Not MailboxFolder Is Nothing
Set MyOffices = MailboxFolder.Folders
Set OfficeFolder = MyOffices.GetFirst
While Not OfficeFolder Is Nothing
Set MyCalendars = OfficeFolder.Folders
Set CalendarFolder = MyCalendars.GetFirst
While Not CalendarFolder Is Nothing
For i = CalendarFolder.Items.Count To 1 Step -1
Set Appointment = CalendarFolder.Items(i)
DeleteDate = Appointment.UserProperties("TZStartTime")
NumDays = DateDiff("d", DeleteDate, Now)
ItemStatus = Appointment.BusyStatus
If NumDays > 0 And ItemStatus = 0 Then
Appointment.Delete
End If
Next
Set CalendarFolder = MyCalendars.GetNext
Wend
Set OfficeFolder = MyOffices.GetNext
Wend
Set MailboxFolder = MyMailbox.GetNext
Wend

 

[Culverin]

Thanks Michael. I'll give it as try.

Am Tue, 13 Jun 2006 12:11:02 -0700 schrieb Culverin:

CDO is an optional component from your Outlook/Office cd that isn´t
installed by default.

OL has known problems with a memory leak. But at first I´d try to use more
variables instead of calling references again and again. And as long as the
code doesn´t do what you want to I wouldn´t use the On Error Resume Next
because there might be errors. (For 42,000 items the performance is another,
important issue and therefore I´d also suggest to use CDO or Redemption from
www.dimastr.com.)

Here´s your code in another way:

'On Error Resume Next
Dim nns As Outlook.NameSpace
Dim MyMailbox As Outlook.Folders
Dim MyOffices As Outlook.Folders
Dim MyCalendars As Outlook.Folders
Dim MailboxFolder As Outlook.MAPIFolder
Dim OfficeFolder As Outlook.MAPIFolder
Dim CalendarFolder As Outlook.MAPIFolder
Dim Appointment As Outlook.AppointmentItem
Dim i As Long
Dim DeleteDate As Date
Dim NumDays As Long
Dim ItemStatus As Long

Set nns = Application.GetNamespace("MAPI")

Set MyMailbox = nns _
.Folders("Mailbox - NAT-CSD-Appointments") _
.Folders("Offices").Folders()

Set MailboxFolder = MyMailbox.GetFirst
While Not MailboxFolder Is Nothing
Set MyOffices = MailboxFolder.Folders
Set OfficeFolder = MyOffices.GetFirst
While Not OfficeFolder Is Nothing
Set MyCalendars = OfficeFolder.Folders
Set CalendarFolder = MyCalendars.GetFirst
While Not CalendarFolder Is Nothing
For i = CalendarFolder.Items.Count To 1 Step -1
Set Appointment = CalendarFolder.Items(i)
DeleteDate = Appointment.UserProperties("TZStartTime")
NumDays = DateDiff("d", DeleteDate, Now)
ItemStatus = Appointment.BusyStatus
If NumDays > 0 And ItemStatus = 0 Then
Appointment.Delete
End If
Next
Set CalendarFolder = MyCalendars.GetNext
Wend
Set OfficeFolder = MyOffices.GetNext
Wend
Set MailboxFolder = MyMailbox.GetNext
Wend

 

[Culverin]

I found the cause of the problem. It is a known issue with Outlook 2000.

See http://support.microsoft.com/kb/293796/en-us

Since the UserProperties field is on a custom form that has VBScript code
behind it, the code is instantiated in memory for each item and is not
unloaded. We do not have CDO avialable in our installation. So, my
workaround is to use a copy of the form that's referenced in my Personal
Folders that has the code deleted. All I have to do is be sure to clear
cache before executing my item cleanup form. This is an ugly workaround, I
know, but it works.

Thanks for your help.