Sending Outlook Email Using Access 2010 and Windows Task Scheduler

[iojbr]

I'm using the following code to loop through records in a test table and output the results to an outlook email (see below). When I run the code as a Private Sub from Access based on a button click event, it works correctly (opens up outlook and sends the email). But when I try to use task scheduler to run the code through a Macro, it doesn't work. It looks like the task scheduler opened an instance of the database, but it's locked, so I can't close it (It won't let me delete the .laccdb file). The error message when I try to delete is: "This action can't be completed because the file is open in MSACCESS.EXE".

Public Function output_order_list()
Dim olApp As New Outlook.Application
Dim olMailItem As Outlook.MailItem
Dim rstOpenTrip As Recordset
Dim strSubject, strBody, strAddresses As String
Dim db As Database
Dim strSQL As String
Dim blnNewInstance As Boolean
Set olMailItem = olApp.CreateItem(0)
Set db = CurrentDb()
strSQL = "SELECT * FROM Table1;"
Set rstOpenTrip = db.OpenRecordset(strSQL, dbOpenDynaset)
strAddresses = "someone@gmail.com"
If Not rstOpenTrip.EOF Then
strBody = "blah blah blah" & vbCrLf & vbCrLf
strBody = strBody & "Field1 Field2" & vbCrLf
strBody = strBody & "==================================" & vbCrLf
rstOpenTrip.MoveFirst
Do While Not rstOpenTrip.EOF
strBody = strBody & rstOpenTrip("Field1") & Chr(9) & _
rstOpenTrip("Field2") & Chr(9) & vbCrLf
rstOpenTrip.MoveNext
Loop
End If
With olMailItem
.To = strAddresses
.Subject = "Here's my report"
.Body = strBody
.Display
.Send
End With
Set olMailItem = Nothing
Set olApp = Nothing
Application.Quit
End Function

 

[lameid]

I expect the code you wrote to pop-up a message box to allow sending the e-mail. If you found a way around that, I'd be interested out of curiosity.

What I am expecting is happening is whatever account you are using in Task Scheduler is different than your test account and does not have the same permission or setting to bypass the security warning.

 

[mp9]

Good code for sending email from Access here:

http://accessrelic.blogspot.co.uk/2015/10/sending-e-mail-from-access.html

It also discusses how to get around the pop-up dialogue referred to by lameid, using something called Outlook Redemption.

Also, for scheduling Access tasks, there's always the Handy Access Launcher as an alternative to Task Scheduler:

http://accessrelic.blogspot.co.uk/p/hal-handy-access-launcher.html

Hope this helps.

http://accessrelic.blogspot.com/

 

[1DMF]

If the machine running the scheduled task has Outlook, the access app has been added to the trusted locations and the machine also has up-to-date anti-virus, then the OOMG shouldn't be triggered so no need for Redemption.

Worse case scenario, there is a program called 'Express Click Yes', which will auto-click the warning pop-ups so no need for user interaction, but I would discourage its use due to obvious security implication.

I notice you have a .display followed by a .send , there is no need to display the email first, you can just issue the .send command if no-one needs to physically see the email.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music