VBA to send Outlook email - stuck in Outbox
 

Hi. At work, employees have an individual report in a folder that they
are supposed to do each day. When they are done, there is a signoff
sheet in their workbook to which they are supposed to add their
initials. Obviously, that sometimes gets forgotten. So, my colleague
and I created a macro the loops through all of the workbooks in the
folder and looks at the sign off sheet. If the individual's initials
are not there, the macro sends the person an email. This works
absolutely fine if/when Outlook is open, but if Outlook happens to be
closed, the emails get stuck in the Outbox.

We searched the internet on this issue, and we found and implemented
Ron De Bruin's code to initiate Outlook and send the emails, but they
are still staying stuck in the Outbox until Outlook is opened and
either the files are released manually, or another email is sent that
pushes the others through.

We are using Outlook 2016, version 16.0.6741.2047. Does anyone have
any thoughts on how to push this through? As it stands right now, we
are going to set up a Windows task scheduler event to open Outlook to
ensure it's open. And yes, we think that will work, but we are hoping
to find the 'right' solution. Any thoughts? Thanks!

Frank

VBA to send Outlook email - stuck in Outbox
 

Hi Frank,

Am Mon, 20 Jun 2016 08:35:15 -0400 schrieb Phrank:

Hi. At work, employees have an individual report in a folder that they
are supposed to do each day. When they are done, there is a signoff
sheet in their workbook to which they are supposed to add their
initials. Obviously, that sometimes gets forgotten. So, my colleague
and I created a macro the loops through all of the workbooks in the
folder and looks at the sign off sheet. If the individual's initials
are not there, the macro sends the person an email. This works
absolutely fine if/when Outlook is open, but if Outlook happens to be
closed, the emails get stuck in the Outbox.

with
set appOut = CreateObject("Outlook.Application")
you start Outlook.

That your mail is in the outbox causes of the settings in Outlook.
Activate in Outlook options = Advanced = Send and receive
"Send immediatly when connected"


Regards
Claus B.
--
Windows10
Office 2016

VBA to send Outlook email - stuck in Outbox
 

On Mon, 20 Jun 2016 15:01:52 +0200, Claus Busch
wrote:

Hi Frank,

Am Mon, 20 Jun 2016 08:35:15 -0400 schrieb Phrank:

Hi. At work, employees have an individual report in a folder that they
are supposed to do each day. When they are done, there is a signoff
sheet in their workbook to which they are supposed to add their
initials. Obviously, that sometimes gets forgotten. So, my colleague
and I created a macro the loops through all of the workbooks in the
folder and looks at the sign off sheet. If the individual's initials
are not there, the macro sends the person an email. This works
absolutely fine if/when Outlook is open, but if Outlook happens to be
closed, the emails get stuck in the Outbox.

with
set appOut = CreateObject("Outlook.Application")
you start Outlook.

That your mail is in the outbox causes of the settings in Outlook.
Activate in Outlook options = Advanced = Send and receive
"Send immediatly when connected"


Regards
Claus B.


Hi Claus,

Thanks for the reply. Unfortunately, we already have that
CreateObject line in our code. We have this:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

and we also already checked the options and have it set to send
immediately when it's connected. So with the CreateObject line, it is
able to create the email message and even start to send it, but
Outlook will only release it if Outlook is already open when the macro
kicks off. Strange. Other things I've tried have been:

- selected in the Advanced options to 'Perform an automatic
send/receive when exiting', to no avail.
- Added .Display above .Send.

Nothing is forcing the email through.

Frank

VBA to send Outlook email - stuck in Outbox
 

One thing you need to understand about Outlook is that you cannot
create an instance of it if it's already running. ALWAYS check if it's
running 1st, then start it only if not running...

Dim appOL
Set appOL = GetObject("Outlook.Application")
If appOL Is Nothing Then _
Set appOL = CreateObject("Outlook.Application")

...and then process your automation!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

VBA to send Outlook email - stuck in Outbox
 

Hi Frank,

Am Tue, 21 Jun 2016 17:36:20 -0400 schrieb Phrank:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

step through your code with F8. After the line
Set OutApp = CreateObject("Outlook.Application")
you see the Outlook icon in the taskbar. Excel send the mail to the
outbox. With End Sub Outlook is nothing and it is closed. If you didn't
send immendiatly and the interval to send is longer than the macro needs
for running the mail remains in the outbox. Perhaps it tooks too long
time to sign in at the mail provider.
With stepping through the code check how long it last to send the mail
and let Excel wait this time.


Regards
Claus B.
--
Windows10
Office 2016

VBA to send Outlook email - stuck in Outbox
 

Hi Garry,

Am Tue, 21 Jun 2016 22:18:18 -0400 schrieb GS:

One thing you need to understand about Outlook is that you cannot
create an instance of it if it's already running. ALWAYS check if it's
running 1st, then start it only if not running...

things have changed. With Office 2016 you can run Outlook multiple
times.


Regards
Claus B.
--
Windows10
Office 2016

VBA to send Outlook email - stuck in Outbox
 

Hi Garry,

Am Tue, 21 Jun 2016 22:18:18 -0400 schrieb GS:

One thing you need to understand about Outlook is that you cannot
create an instance of it if it's already running. ALWAYS check if
it's running 1st, then start it only if not running...

things have changed. With Office 2016 you can run Outlook multiple
times.


Regards
Claus B.

Ok! Well.., it's about time!<g
Nice to know this...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

VBA to send Outlook email - stuck in Outbox
 

On Wed, 22 Jun 2016 08:02:40 +0200, Claus Busch
wrote:

Hi Frank,

Am Tue, 21 Jun 2016 17:36:20 -0400 schrieb Phrank:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

step through your code with F8. After the line
Set OutApp = CreateObject("Outlook.Application")
you see the Outlook icon in the taskbar. Excel send the mail to the
outbox. With End Sub Outlook is nothing and it is closed. If you didn't
send immendiatly and the interval to send is longer than the macro needs
for running the mail remains in the outbox. Perhaps it tooks too long
time to sign in at the mail provider.
With stepping through the code check how long it last to send the mail
and let Excel wait this time.


Regards
Claus B.

Ah, I was actually wondering about that after I sent my message
yesterday. I just tested it, and I'm not actually seeing the Outlook
icon show up in my taskbar. I have it pinned there, and when it's
opened, you can see it's highlighted, but it never highlights as if
it's open. The only time it highlights is when it gets to the
..Display line of code, and then only briefly. I delayed a long time
after hitting .Send, but the message remained in my Outbox.

I stepped through again, this time with the Locals window open, and
at the OutApp step, it's showing AnswerWizard in the Expression field,
and <operation failed in the Value field. And of course, under the
OutMail expression, there's Application, which shows a value of <The
operation failed. Which is telling me that it's not opening an
instance of Outlook, which is probably why the message is remaining in
the Outbox. Any thoughts on why and how to get that to not fail?

Thanks gentlemen!

Frank

VBA to send Outlook email - stuck in Outbox
 

Hi Frank,

Am Wed, 22 Jun 2016 08:12:35 -0400 schrieb Phrank:

Ah, I was actually wondering about that after I sent my message
yesterday. I just tested it, and I'm not actually seeing the Outlook
icon show up in my taskbar. I have it pinned there, and when it's
opened, you can see it's highlighted, but it never highlights as if
it's open. The only time it highlights is when it gets to the
.Display line of code, and then only briefly. I delayed a long time
after hitting .Send, but the message remained in my Outbox.

the Outlook icon is shown right in the info bar of the task bar with a
big orange dot on it. When you hoover over the icon you can read
"Another program is using Outlook".

I stepped through again, this time with the Locals window open, and
at the OutApp step, it's showing AnswerWizard in the Expression field,
and <operation failed in the Value field. And of course, under the
OutMail expression, there's Application, which shows a value of <The
operation failed. Which is telling me that it's not opening an
instance of Outlook, which is probably why the message is remaining in
the Outbox. Any thoughts on why and how to get that to not fail?

What Excel and what Outlook versions do you use? Are they different? If
you send from Outlook must you sign in or are you signed in
automatically? Do you always have an Internet connection?
I guess the failure is from Outlook and not from Excel.


Regards
Claus B.
--
Windows10
Office 2016

VBA to send Outlook email - stuck in Outbox
 

On Wed, 22 Jun 2016 15:44:29 +0200, Claus Busch
wrote:

Hi Frank,

Am Wed, 22 Jun 2016 08:12:35 -0400 schrieb Phrank:

Ah, I was actually wondering about that after I sent my message
yesterday. I just tested it, and I'm not actually seeing the Outlook
icon show up in my taskbar. I have it pinned there, and when it's
opened, you can see it's highlighted, but it never highlights as if
it's open. The only time it highlights is when it gets to the
.Display line of code, and then only briefly. I delayed a long time
after hitting .Send, but the message remained in my Outbox.

the Outlook icon is shown right in the info bar of the task bar with a
big orange dot on it. When you hoover over the icon you can read
"Another program is using Outlook".

I stepped through again, this time with the Locals window open, and
at the OutApp step, it's showing AnswerWizard in the Expression field,
and <operation failed in the Value field. And of course, under the
OutMail expression, there's Application, which shows a value of <The
operation failed. Which is telling me that it's not opening an
instance of Outlook, which is probably why the message is remaining in
the Outbox. Any thoughts on why and how to get that to not fail?

What Excel and what Outlook versions do you use? Are they different? If
you send from Outlook must you sign in or are you signed in
automatically? Do you always have an Internet connection?
I guess the failure is from Outlook and not from Excel.


Regards
Claus B.

Hi Claus. My program versions a

- Outlook: Outlook 2016, version 16.0.6741.2047
- Excel: Excel 2016, MSO (16.0.6925.1018) 32-bit (this is the latest
version)
So yes, they are slightly different, but still 2016. I don't need to
sign in to send from Outlook. I do need to sign into our company's
network, but I need to do that to use Excel too. Yes, I do always
have an internet connection. And I agree, the issue seems to generate
from Outlook, not Excel. It seems the macro is working just fine; the
email simply won't release from Outlook, no matter what we try. And
that seems to be the general theme of what I've read from other users
that have encountered the same issue, and there simply doesn't seem to
be a universal, clean solution to this issue.

As I mentioned in my original post, we are going to use a 'high tech'
solution of setting a Window's Task Scheduler event to open Outlook 1
minute before our main event kicks off, which will ensure Outlook is
open. Seems like a truly silly work around, but it should work.

Thanks for your help Claus.

Frank