Please help: VBA not sending email when Outlook is open, only when closed

Hello all,

I have a very simple code (below) that selects a range and send an email, the macros runs on Workbook_Open. If I just open the workbook myself, everything is working as expected (regardless of whether I have Outlook open or not). However, if I schedule the same spreadsheet to be open from Task Scheduler, it only works if I have Outlook closed. If the Outlook is open at that moment, I get "Method EnvelopeVisible of object '_workbook' failed". I tried various approaches and spend a day searching for a solution only, but nothing seems to help. I am running both 2010 Professional version of both Excel and Outlook. Again to summarize, when Outlook is open, the code only works when I run the spreadsheet, not when it is run from Task Manager (this task runs under my name with highest privileges).

Thank you very much in advance for your help.

-Konstantin

Please Login or Register  to view this content.

Thank you very much for your prompt response I tried three approaches with the code you suggested and here are the results:

- Using Task Scheduler to open the spreadsheet when Outlook is open:
The code breaks at “Set msOutApp = New Outlook.Application”, saying “ActiveX component cannot create object”
- Using Task Scheduler to open the spreadsheet when Outlook is closed:
The code breaks at “.Send”, saying “Application Error”
- Manually opening the spreadsheet, when Outlook is open:
It works and the email is sent, however the body is blank. Could you please advise how to send a selection in the body, as my previous code does?

I believe that it has something to do with Task Scheduler. Not sure, if it is easy to replicate by it would be great if anyone could replicate this using Task Manager. Basically, all my code is in ‘main’ sub and then in ‘ThisWorkbook’, all I have is:

Please Login or Register  to view this content.

Again, thanks much for looking into this!


Regards,
Konstantin

sorry, but .Body does not solve the issue, plus it still does not work with Task Scheduler. Does anyone know how to overcome this issue? There seem to be no references on the Internet how to solve it...wondering if no one experienced the same issue before.

Thank you, I will keep tweaking it all.