Win10/O365 Email Macro Slowness

[ButtonsUK]

Hi There!

I'm a novice VBA user and am looking for some help!

A few years ago, I created a VBA script that sends emails out using the outlook client installed on the machine.
Typically this script would be used to send anywhere around 30-50 emails at a time.

The company i work for used to be on Windows 7 / Office 2010 (32 bit), we have recently moved to Windows 10 / Office 365 (64 bit) and the end users of my script have reported slowness when sending emails.
The below video demonstrates the difference side by side, Win7 took 18 seconds to send 20 emails, Win10 takes 1min 55 secs.
These videos were recorded on clean laptops (new image of both OS & Office versions) using the same spreadsheet and test data.

The forum wont let me post a link, so replace the dollar sign with a dot to see the video:

youtu$be/KF-j800bUaA

(NOTE: The white boxes on the video are intentional, to hide company or personal data.)

I know the code isn't likely to be efficient, however, i wouldn't expect a 539% time increase!

Does anyone know of any reason why this would be the case?
Attached is the code if that helps.

[rorya]

I'd start by moving the Statusbar updating outside your loop. Then you should really look at removing all that unnecessary selecting.

[CK76]

Office 365 has bunch of animation etc that makes it slower than Office 2010 by design.

You'll need to make sure all those are turned off etc. Check File -> Options.

1. In Advanced -> Dislpay. Make sure Disable hardware graphics acceleration is checked (turned off).
2. This shouldn't have impact on the code, but also in Formulas section Enable multi-threaded calculation is on & Use all processors on this computer is selected.
3. Also under Advanced -> General. Tick Enable multi-threaded processing
4. In Ease of Access, uncheck Provide feedback with animation.

In addition, you may experience some speed gain if you turn off Office Animations (not just Excel).
Go to Ease of Access -> Simplify and personalize Windows and turn off Show animations in Windows.

Code itself, as you mentioned, isn't optimized for speed. If you need help in optimizing code, I'd recommend uploading sample workbook that mirror your set up (with desensitized data).

[ButtonsUK]

Thanks Rory, i'll make these amends.

Thanks CK76, everything except point 4 and animations in windows were already done - time to run went down to 1min 48 sec (woo!).

I have attached the full spreadsheet with code.

[CK76]

When tested on my machine (Office 365 64 bit, Win 10).

Ran 10 trials: avg 31.2 Sec. Min 29.8 Sec. Max 32.3 Sec.

Looking at your code, I've got couple of questions.

1. Will there be any case where more than single line of data is emailed in single email? I ask, since your code has the logic for it in Selection.Copy section. But not in Loop section.
2. Looks like Qty is always changed to positive value when sent in email. Correct?

[ButtonsUK]

Hi CK,

Interesting that your machine with no changes does it in a much better time!

Answers below

1. Yes, there are usually multiple lines for the same "house", i have done 20 individual ones as an example.
2. Yes this is correct, when the data is copy & pasted from another system it is a negative number (e.g. -10 steaks from stock), however, when we email out to the "house" the end users wanted this as a positive value "we have removed 10 steaks from your stock" (tbh I think it should be a minus but i was over-ruled on how it displays!)

[CK76]

Here ,try attached. Only change is in Module4.
I've split into 3 separate procedure and doing loop operation on variant array (in memory) along with dictionary.

On my machine took about 24~25 sec.

Note: This method as is, will only work on Windows based machine.

[ButtonsUK]

Thanks CK, sadly the time hasn't improved.

I tested with 2 other colleagues and times were -:
Me - Old Code: 1min 23s New Code: 1min 38s
Colleague 1 - Old Code: 1min 7s New code: 1 min 35s
Colleague 2 - Old Code: 54 sec New code: 1min 6s

Based on the results we are seeing and that you are getting, I think its pointing to environmental? Any ideas on what else could be checked on that front or anything that i could do to 'debug' that side?

Thanks

[CK76]

Exporting and saving data, along with generating email would be the slowest part of the code. Try turning off all Office Animation, it may help.

Other than that... for mass email. I tend not to use Outlook. I either use CDO (if I need to use Excel) or use Blat SMTP mailer.