How can I make my VBA Outlook script more efficient

Question

This is my first question on StackExchange ever :-) I am Running the following script in MS Outlook VBA

Sub export()

    On Error resume Next

    Dim Ns As Outlook.NameSpace
    Dim eitem
    Dim oFile As Object
    Dim fso As Object

    Set Ns = Application.GetNamespace("MAPI")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFile = fso.CreateTextFile("C:\Users\chakkalakka\Desktop\mails.txt")

    'Code
    For Each eitem In Ns.Session.Folders.Item(12).Items
        oFile.WriteLine eitem.SenderName & "§" & eitem.SentOnBehalfOfName & "§" & eitem.ReceivedTime
    Next

    oFile.Close
    Set Ns = Nothing
    Set fso = Nothing
    Set oFile = Nothing

    Debug.Print "Completed!"

End Sub

The script in general is working fine and the output is correct. My Problem is: I need to run this inside a folder with > 95000 items and it takes ages.

So my question is: What can I do to improve performance?

Thanks in advance for your help

How long does it actually take? Please remove the `On Error resume Next` part to see if there are any errors causing some delay. If so, post the error messages here. — , Apr 20 '17 at 08:52
Assign the `Items` collection to a local variable. This avoids re-evaluation of the complete object chain in the `For Each` loop. — Axel Kemper, Apr 20 '17 at 09:04
On Error Resume Next should only be used where there is a specific purpose for bypassing an error and then turned off with On Error GoTo 0. See http://stackoverflow.com/questions/31753201/vba-how-long-does-on-error-resume-next-work/31753321#31753321 and http://stackoverflow.com/questions/29390673/error-handling-in-vba-on-error-resume-next/29390944#29390944 — niton, Apr 20 '17 at 10:03
Thanks for your replies. The resume next statement has its purpose as there are around 100 items in the list which are for some reason corrupted and cannot be read. — Dennis, Apr 20 '17 at 10:58
Thanks for the idea to localize the Items collection. I will try this and measure performance. — Dennis, Apr 20 '17 at 10:59
This should actually be posted on the StackExchange CodeReview site found here: https://codereview.stackexchange.com/. SO is intended for problems where the code is not working as intended. CodeReview is a great place for performance improvement. — Brandon Barney, Apr 20 '17 at 12:07
You can lessen this worst practice, On Error Resume Next on the entire code, by putting the On Error Resume Next just prior to oFile.WriteLine then follow immediately with On Error Goto 0. The real issue is likely the items are not mailitems so there is no SenderName property. You can drop the On Error Resume Next if you first test that eitem is a mailitem. — niton, Apr 20 '17 at 16:38
I'm voting to close this question as off-topic because it belongs on https://codereview.stackexchange.com/ — niton, Apr 20 '17 at 16:41

score 0 · Accepted Answer · answered Apr 23 '17 at 21:53

The most inefficient line of code is the following one:

For Each eitem In Ns.Session.Folders.Item(12).Items

You need to break the chain of property and method calls and declare them on separate lines. So each property or method will be declared on a separate line of code. Thus, you will be able to release underlying COM objects instantly. Set a variable to Nothing in Visual Basic to release the reference to the object.

Iterating through all items in the folder is a time-consuming task. Instead, I'd suggest using the Find/FindNext or Restrict methods of the Items class to deal with items that correspond to your conditions. Read more about these methods in the following articles:

Also you may consider using the GetTable method of the Folder class which allows to obtain a Table object that contains items filtered by Filter. If Filter is a blank string or the Filter parameter is omitted, GetTable returns a Table with rows representing all the items in the Folder.

How can I make my VBA Outlook script more efficient

1 Answers1