Access 2013 - Send an email automatically with outlook and Windows Task Scheduler

Question

I have an access macro that runs a set of Netezza queries and uploads the results to a database. It then opens and refreshes an Excel file that utilizes this data and saves the file in a couple of locations. Finally it composes an automated email and sends it to a distribution list. When I manually run the macro, everything works 100% perfectly.

In order to make my life a bit easier, I am using Windows Task Scheduler (Windows 10) to automatically fire the macro once a day, and this is where my issue lies. Task Scheduler fires the macro off without a hitch, all of the queries refresh, the excel files are saved, but the e-mail is not sent.

Here is the code SendOutlookEmail code that I'm using

Sub sendOutlookEmail()

Dim oApp As Outlook.Application
Dim oMail As MailItem
Dim SpDate As String
Dim Signature As String
Dim StrPath As String
Dim StrFilter As String
Dim StrFile As String

SpDate = Format(Now() - 1, "yyyy-mm-dd")


Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .Display
End With

    Signature = oMail.HTMLBody

With oMail

    .SentOnBehalfOfName = "My Email"
    .To = "CCO Reporting"
    .Subject = "AHT - ACW Dashboard - " & SpDate
    .HTMLBody = "<span LANG=EN>" _
                & "<font FACE=SegoeUI SIZE = 3>" _
                & "The IB/OB AHT - ACW reports have been updated and placed in the following folder:" _
                & "<br><br>" _
                & "<a href='File Location'>File Location</a>" & "<br><br><br></font></span>" _
                & Signature
    '.Attachments.Add (StrPath & StrFile)
    '.Display
    .Send

End With

On Error GoTo 0

Set oMail = Nothing
Set oApp = Nothing
End Sub

Here is the task scheduler settings Task Scheduler

Erik A · Answer 1 · 2017-07-31T17:41:37.580

Possibly Outlook just doesn't have enough time to send the message, as it instantly gets closed after the message is moved to the outbox (.send doesn't send the message as far as I know, but just moves it to the outbox and triggers a send for all items in there).

Try to manually add a send/receive, to make Access wait for Outlook to actually send the mails (add this to your vba before the Set oApp = Nothing):

' Synchronizes (ie sends/receives) OL folders.
' Ref: http://msdn.microsoft.com/en-us/library/ff863925.aspx

Dim objNsp As Outlook.NameSpace
Dim colSyc As Outlook.SyncObjects
Dim objSyc As Outlook.SyncObject
Dim i As Integer

On Error GoTo SyncOL_Err



Set objNsp = oApp.Application.GetNamespace("MAPI")
Set colSyc = objNsp.SyncObjects

For i = 1 To colSyc.Count
    Set objSyc = colSyc.Item(i)       
    Debug.Print objSyc.Name
    objSyc.start
Next



Set objNsp = Nothing: Set colSyc = Nothing: Set objSyc = Nothing

hmm, that doesn't seem to fix it, when I run it through the task scheduler, it now just hangs up on the loop, no issue running it through the regular macro though — Tom Drewnowski, Jul 31 '17 at 17:53

Access 2013 - Send an email automatically with outlook and Windows Task Scheduler

1 Answers1