# Very slow loop - vba/outlook 2003



## bdimag (Apr 8, 2001)

ok, i dunno if its just like this (i don't do many macros)... but its been a slow day here at work and i decided to make a little macro to open an email, check for dates, all the good stuff... thats all fine...

but now then i got to a point that i wanted the letter to auto-file... and to find the right folder, because i can't figure out how to get a folder object from a path (string), i was going to have it parse through the folders to where it goes, which is like 4 folders deep...

the loop only runs through 20-30 times, but takes 9 seconds! 

*is it always like that?*

i've included the code, and threw in debugs to make sure it wasnt looping when it shouldnt..

_by the way: *fy* = fiscal year of the date, *mon* = format(date,"MMM YY")

and it does get to the correct folder: \\Mailbox - EAST\Inbox\Registrations\FY08 - Registrations\JAN 08_


```
Debug.Print "1 - " & Now
    While q <= objNS.folders.Count
        If objNS.folders.Item(q).Name = "Mailbox - EAST" Then
            Set objFolder = objNS.folders.Item(q)
            GoTo ex
        End If
        q = q + 1
    Wend
    Exit Sub
ex:
    Debug.Print "2 - " & Now
    q = 1
    While q <= objFolder.folders.Count
        sn = objFolder.folders.Item(q).Name
        Debug.Print "* - " & Now
        If sn = "Inbox" Or sn = "Registrations" Or sn = "FY" & fy & " - Registrations" Or sn = mon Then
            Set objFolder = objFolder.folders.Item(q)
            If sn = mon Then GoTo done
            GoTo ex
        End If
        q = q + 1
    Wend
done:
    Debug.Print "3 - " & Now
```
1 - 1/3/2008 1:57:19 PM
2 - 1/3/2008 1:57:19 PM
* - 1/3/2008 1:57:19 PM
* - 1/3/2008 1:57:20 PM
2 - 1/3/2008 1:57:20 PM
* - 1/3/2008 1:57:20 PM
* - 1/3/2008 1:57:21 PM
* - 1/3/2008 1:57:21 PM
* - 1/3/2008 1:57:21 PM
* - 1/3/2008 1:57:22 PM
* - 1/3/2008 1:57:22 PM
* - 1/3/2008 1:57:22 PM
* - 1/3/2008 1:57:23 PM
* - 1/3/2008 1:57:23 PM
2 - 1/3/2008 1:57:23 PM
* - 1/3/2008 1:57:24 PM
* - 1/3/2008 1:57:24 PM
* - 1/3/2008 1:57:25 PM
* - 1/3/2008 1:57:26 PM
2 - 1/3/2008 1:57:26 PM
* - 1/3/2008 1:57:26 PM
* - 1/3/2008 1:57:27 PM
* - 1/3/2008 1:57:27 PM
* - 1/3/2008 1:57:27 PM
* - 1/3/2008 1:57:28 PM
* - 1/3/2008 1:57:28 PM
3 - 1/3/2008 1:57:28 PM


----------



## Chicon (Jul 29, 2004)

Hi bdimag,

I'm not a VB expert but I can tell you that the *GoTo ex* statement in the second *While* loop may be the cause of your problem.


----------



## calvin-c (May 17, 2006)

With the exception of a folder in the middle (time=1:57:25) it appears to check about 3 folders/second pretty consistently. do you believe it should be checking them faster? If so, why?

If not, then about all I see to speed it up is optimize your search. A lot of that depends on how reliably your folders are organized.

The GoTo's aren't good code but it's working. Another place you can get help (focussed entirely on VB) is www.vbforums.com


----------



## bdimag (Apr 8, 2001)

its taking 3 seconds to go through the list of subfolders each time? just seems long to me.. its not reading the messages, just the names of subfolders.. each #2 is when it finds the appropriate subfolder, and each * under it is reading the names of the subfolders until it finds the next "#2"..

so i guess my main question was, is that normal? does it take that long to pull information from outlook?

i moved the goto out of the while loop and used some vars as an exit clause in the while, no change in time... 

and i saw that other post here about VBA - so i registered and posted there a few hours ago - still nothing... although if youre also calvin-c there, you got a referal..


----------



## Chicon (Jul 29, 2004)

bdimag said:


> ...
> so i guess my main question was, is that normal? does it take that long to pull information from outlook? ...


Did you compact your Outlook folders ? It may be a cause of slowing.


----------



## calvin-c (May 17, 2006)

I don't use Outlook so I don't know what's normal. I wouldn't think fragmented folders would present that consistent performance-unless maybe you've never compacted them. I know that in T-Bird some folders are more active than others. The folders that aren't active basically stay in a compacted (or near-compacted) state for a long time. Assuming that's standard for email rather than specific to T-Bird, I'd expect performance affected by compaction to go fast on some folders & slow on others-which isn't happening here.

I am calvin-c on vbforums so thanks.

I'm wondering if you can somehow cache the folder tree so you only have to traverse it for the first message. Maybe create an array & store each folder's path & name in it? Traversing an array should be *lots* faster than traversing the folders for each message.


----------



## bdimag (Apr 8, 2001)

what would compacting do? at this point i am not dealing with any emails except the one that is initially opened (and is virtually instant, might i add)... just the *names* of the folders

i like the idea of caching.. but this is kind of a 1 email per run thing...

a request will come in somewhat infrequently, and so far i've made it so i press a button on the toolbar, a window pops up with the information basic-_ified_, and there are 2 buttons: approve or deny

after an action taken, it finds the folder it needs to go, and puts it there (also sends a reply email but i didn't do that yet because this is bugging me)


----------



## calvin-c (May 17, 2006)

OK, if caching won't do it & I suppose the folder structure is too dynamic to build the path that way, then the only way I see to speed it up is to reduce the time it takes to retrieve the folder name from Outlook.

Compacting would, most likely, speed retrieval of subfolder names-but I don't use Outlook so this is pure speculation on my part. On a hard drive the subfolder list in a folder is like any other file-it can get fragmented. Defragging the drive (similar to compacting an Outlook folder, I think) would make retrieval of the subfolder names faster. But probably not a great deal unless your Outlook folders badly need to be compacted.

I doubt if I can be of any further help on this-you need an Outlook expert. You could do various things, e.g. if the folders are alphabetical you could do a binary search, but the basic problem is that it appears to take about 1/3 second to retrieve the name of each folder or subfolder. To speed up the program you either reduce that time or develop some way of finding the folder without searching thru all of them. If the folder structure is chaotic & malleable then I don't see any way of finding the folder without searching. And, as I said, I'm not an Outlook expert so I don't know of any way to reduce the time it takes to retrieve a folder name.


----------

