Ok folks,

I have finally found a workaround for a very annoying issue that perhaps many of you have faced at some point [this affects business users who are trying to send email through their work computer using excel, thus constricted by their IT department in what they are allowed to do].

Here is the scenario:

You want to create a macro in your workbook that performs some calculations and then emails the workbook, using your outlook, to a specified email address.

Usual problem:
Everything works well until it actually tries to send the email and you get an annoying popup from outlook saying that someone is trying to send an email using your outlook and you have to actually click on a button to allow it. This stops all dreams of automating the whole procedure.

You may have found other ways of trying to send email (namely the CDO trick where you can send email bypassing outlook) but it is unlikely that they have worked since you won't have an SMTP server in the first place (that is if you working on a company that uses exchange servers etc).

What is the solution:
This problem is caused because of a high macro security setting. If you are a private user (i.e at home in your own pc) you can simply change the security level but if you are at work you are more likely to not have that option as your IT office will refuse to change the outlook security settings. So what do you do?

There is a solution. As anyone who has searches on what macro to use to email a workbook you will have found the code below:
Please Login or Register  to view this content.
The code above will try to automatically send the email to the specified email address, resulting in the annoying popup. The workaround is very simple, instead of relying on the code to automatically do it, you will instead display the composed email and send the keyboard combination of alt+s through the macro to the window to send it. It is the same as if you have composed an email in outlook and pressed on alt+s to send the email.

All you need to do is change the:
Please Login or Register  to view this content.
to:
Please Login or Register  to view this content.
You can experiment with the time value but I would suggest leaving it over 2 seconds. If you apply this fix and run the macro you will see that it will display the email and then send it, instead of doing it in the background.

I hope this helps some people as I have been trying to find a solution to this for a long time.

Regards,

K.