Excel VBA to get metrics from Outlook Emails is slow in existing Email Profile, but 2-3 times faster in New Email Profile ... WHY?!?

GopherKing

New Member
Joined
Jul 28, 2017
Messages
2
Ok, so this being my first post, I figured I would try to give you guys a good one!! :)

I'll start of by noting that I am using MS Office 2010 for all my applications, with the exception of SharePoint 2013. This is all installed on Windows 7 (yeah, we are a little behind the times on our software, but they want to make sure there are no issues with new software before rolling it out ... [cough] MS Office 2013/Windows 8[cough])

I have a Macro Enabled Excel Spreadsheet that has a bunch of VBA to extract multiple sets of information from a user's MS Outlook Shared Email boxes. Now, we don't have any problems with the code as it is executing without any issues, but to give you a little more background, we are extracting a count of emails per day, a count of emails with a specific subject line and contain a certain file type (.txt) per day, and also finding any emails with a specifically named attachment to extract the text from within that file and get a total line per day. The VBA loops through 8 separate Shared Email Boxes and applies the same code to each one. Now, we don't execute this VBA but once every 2 weeks, since running it on a daily basis isn't necessary, but every 2 weeks is just long enough to where it shouldn't take a large amount of time (expecting no more than 10 mins). I do have in place many bits of code to make sure that all the opening of files and objects are closed out to prevent memory leaks, which the previous owner did not (no wonder he isn't here anymore!), so this was able to help keep the time down.

However, after some time, I noticed that the amount of time to process the last two weeks of emails was taking longer and longer to complete. At some times, it was over an hour (realizing that I was also doing other processes in MS Access which may have contributed to the time due to fighting for PC resources). In the last week, I had a new team member start on our team who I tasked with reviewing the code and potentially moving the counts gathered into a Collection rather than a temporary worksheet (created by the VBA) to speed things back up. But when he ran the process for the first time, it completed in 3 mins!!!!

He had mentioned that he had been encountering issues with his MS Outlook email and had worked with our IT department to setup a new Email Profile via Control Panel --> Mail (32-bit) --> Show Profiles ---> Add. He advised that after doing this, his email was much faster and not as "bogged down" as his 'Outlook Default Profile' (even though they are using the same email address and shared mailboxes).

So, for S&G's (you can guess what they stand for), I decided to try it out myself and created a new Email Profile. After creating it and closing/reopening my MS Outlook to then select which profile I wanted to use, I reran my VBA. The results were that the process was taking me with the new profile on average 8 mins to complete!!! Which is longer than the new guy, but I do have to consider that I am a remote employee connected through a throttled VPN and using a laptop (so my 20mbs compared to his 95mbs is why mine is not that fast!). But, this is better than the 21 mins that it is still taking on my default email profile within MS Outlook. And just to be sure, I ran the process multiple times throughout the day to ensure that it wasn't just because it was early in the morning and no one was really in the office yet killing network bandwidth.

Now, for my question to you ... Can anyone explain to me why accessing MS Outlook via VBA would be slower on a default email profile, but 2-3 times faster if you create a new email profile with the same email and shared email boxes? Is there some cached file that MS Outlook keeps on your local PC that retains any emails that have been deleted from the server (permanently) that when you create a new profile it doesn't download them locally because they aren't on the server anymore?

I've search the net (and someone may have better luck that me as I have seen it multiple times in forums where someone says they searched, but they may not have used the correct verbiage) and haven't found anything yet, so I am looking here to see if anyone else has encountered this issue and found a possible reason as to why it happens. A solution to it would also be great, if it doesn't require creating a new email profile (would like to keep the general masses from trying to do this process, we all know how well they can follow directions at times!).

Thanks in advance!! I look forward to your responses.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

GopherKing

New Member
Joined
Jul 28, 2017
Messages
2
Ok ... so I continued my search for a possible solution and have found the following site explaining about Profiles and the differences between .PST and .OST files: Introduction to Outlook Data Files (.pst and .ost) - Outlook

At my company, we have the .PST option disabled in our MS Outlook applications, so we can't create files that we can store locally or on our network. All of our email default Profiles are retained in an "Online" location (one that we can't get to through Explorer). We can, however, create new Profiles, which in turn create a .OST file that is stored locally on our PC. Now, this .OST files is an "Offline" file that stores all emails that are associated with your Personal and Shared Email Boxes, meaning you can access all the emails that have been downloaded from your email server even if your PC is not online.

With this said, this explains why we are seeing a difference in processing times of our code since our default email Profile is referencing the "Online" location and the other Profiles are referencing the "Local" locations. Of course the VBA will be faster when accessing a back-end data location that is on the same PC as where the code is being executed vs. back-end data location that is on a network drive.

So, I do appreciate if anyone has spent some time on this already. I also wanted to pass this information on just in case anyone else is seeing the same issue and wondering the same questions I had.

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,640
Messages
5,626,046
Members
416,158
Latest member
CaliburBlade138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top