Email sent with VBA using Task Scheduler gets stuck in Outbox

Question

I have some macros and Task Scheduler to launch Excel at a specified time, update some tables, create PDF documents from those tables and then email those PDF documents to select individuals.

Sometimes the email gets stuck in the Outbox and does not send until I open up Outlook.

Here is the code for sending the email:

Option Explicit

Public strFileName As String

Sub EmailPDFAsAttachment()
'This macro grabs the file path and stores as a concatenation/variable. Then it emails the file to whomever you specify.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .

    Dim OutApp As Object
    Dim OutMail As Object
    Dim FilePath As String

    'This part is setting the strings and objects to be files to grab with their associated filepath. (e.g. FilePath is setting itself equal to the text where we plan to set up each report)

    FilePath = "\\"ServerNameHere"\UserFolders\_AutoRep\DA\PDFs\SealantsVS1SurfaceRestore\" _
    & strFileName & ".pdf"

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
  '  End With

    'Below is where it creats the actual email and opens up outlook.
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
   ' ******Make sure to set the .To to only recipients that are required to view it. Separate email addresses with a semicolon (;).
   ' Current distribution list:
   ' 

    With OutMail
        .To = "example@Example.com"
        .CC = ""
        .BCC = ""
        .Subject = strFileName

        .HTMLBody = "Hello all!" & "<br>" & _
        "Here is this month's report for the Sealants vs Surface Restore. It goes as granular as to by show results by provider." & "<br>" & _
         "Let me know what you think or any comments or questions you have!" & "<br>" & _
         vbNewLine & .HTMLBody
         'Here it attached the file, saves the email as a draft, and then sends the file if everything checks out.
        .Attachments.Add FilePath
        .Send

    End With
    On Error GoTo 0

   ' With Application
   '    .EnableEvents = True
   '   .ScreenUpdating = True
    End With
'This closes out the Outlook application.
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

After this completes, the Private sub jumps back to the macros in this workbook and quits MS Excel with the CloseWorkbook Application.

My tools reference library in Outlook's VBA settings:

My Trust Settings:

Macro Settings:

"Enable all macros" selected

"Apply macro security settings to installed add-ins" selected

The idea is to have this program run in the early morning and have these emails in the inbox of select individuals by the time they come in to work.

`does not send until I open up Outlook` does [this](http://stackoverflow.com/questions/28936757/excel-vba-to-detect-if-outlook-is-open-if-its-not-then-open-it) help? — findwindow, Jun 06 '16 at 22:20
Not sure what you mean.....but the idea is to have it send without having to open up Outlook. I have a task scheduler opening the Excel File, the file gets triggered to run, it creates the PDF file, creates the email, but the email gets trapped in the outbox folder until I open excel and then Outlook sends anything in the Outbox folder I want it to send the file without me personally opening Outlook or without opening Outlook with Task Scheduler. — Saige Paget, Jun 06 '16 at 23:04
`have it send without having to open up Outlook` that's like wanting to drive a car without starting it? — findwindow, Jun 07 '16 at 15:11
I have a Private Sub set to run "EmailPDFAsAttachment" at a certain time. The Private Sub runs when I open the file either manually or via Task Scheduler. This email sends every time I am physically logged in, whether I trigger the macro by opening the file manually or via Task Scheduler. Also, I have the box checked in Task Scheduler to allow the task to run whether or not I am logged in. So there is either something wrong with my code or my permissions because it runs perfectly when I am logged in. Since it runs when I am logged in, I shouldn't need to additional code should it? — Saige Paget, Jun 07 '16 at 16:32

Dmitry Streblechenko · Answer 1 · 2019-01-18T18:07:04.683

0

Outlook, just like any other Office app, cannot run in a service(such as the Scheduler). That being said, you need to force Outlook to perform SendReceive and wait for it to complete. Call Namespace.SendAndReceive or retrieve the first SyncObject object from the Namespace.SyncObjects collection, call SyncObject.Start and wait fro the SyncObject.SyncEnd event to fire.

edited Jan 18 '19 at 18:07

answered Jun 08 '16 at 04:15

Dmitry Streblechenko

62,942
4
53
78

How would this work within Excel VBA or does this need to be written in Outlook VBA and if so how? I'm not quite sure how to write this. Also, not sure if this matters, but I am using an IMAP email account (not MAPI). – Saige Paget Jun 08 '16 at 19:25
1

You can do that in Excel VBA, you just need to make sure the Outlooks's Application and SyncObject variables are declared on the global scope so that they don't go out of scope and get released. – Dmitry Streblechenko Jun 09 '16 at 20:22
So I found this code that pushes Outlook to Stay open or reopen which in turn allows whatever is in the Outbox to send: http://www.rondebruin.nl/win/s1/outlook/openclose.htm However, now I am having the following problem: the task runs perfectly when I manually open it (with all macros enabled), but via task scheduler, the code stalls. Anybody know of a solution? – Saige Paget Jun 09 '16 at 23:16
Once again, no Office app can be used in a service. – Dmitry Streblechenko Jun 10 '16 at 07:08

score 0 · Answer 2 · answered Jan 18 '19 at 12:23

If anyone is still looking for an answer; this allows to actually send an email without opening outlook app.

Dim mySyncObjects As Outlook.SyncObjects
Dim syc As Outlook.SyncObject
Set mySyncObjects = Outlook.Application.GetNamespace("MAPI").SyncObjects
Set syc = mySyncObjects(1)
syc.start

Email sent with VBA using Task Scheduler gets stuck in Outbox

2 Answers2