Sending reports at scheduled times


I'm trying to set up automated emailing of a few reports for my company and have a few questions after doing some research into it.

So far I have the emailing portion finished and working of MS-Access where it will send emails containing two reports to whomever. I have also set up a .bat file to run the macro automatically and can use task scheduler in order to run the macro while I'm logged in and receive the emails.

The problem is I'm trying to set it up so that it will run overnight bi-weekly. When I test out the task scheduler with the option "Run whether logged on or not"(which since my account does not have admin privileges, I had to switch into an admin account and give my account log on as batch job rights) and try to run it while being logged out, unfortunately it doesn't work.

I have tried out running both Access directly through the task scheduler as well as through a .bat file. The results from the .bat file show that the operation completed successfully, but no emails will go through and as far as I know, the .bat program does not properly execute. When I attempt to run access directly through the task scheduler, the error shows that the operator or administrator has refused the request(0x800710E0).

Would I need to be running my account as a local administrator in order for this to work? Or should I try to write a batch file with AT commands to try to schedule this?

This is my first attempt at doing anything with Windows for automated scheduling as I've only ever used the crontab functions within linux. Any advice or help would be greatly appreciated, as well as any questions that might help my clarify what I'm trying to do here.

Code for the .bat file:

start "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\*User*\Documents\Asset List Backup 20191126.accdb" /x SendEmail

Edit: Code for the Email:

Public Function Sendemail1()
Dim crrntdate As String
Dim ol As Outlook.Application, msg As MailItem, atts As Attachments, strFilePath As String, strFilePath2 As String
Set ol = CreateObject("Outlook.Application")
crrntdate = Format(DateValue(Now), "mmddyyyy")
strFilePath = "C:\Users\USER\Documents\Auto Reports\Office Consumables Report " & crrntdate & ".pdf"
strFilePath2 = "C:\Users\USER\Documents\Auto Reports\Field Consumables Report " & crrntdate & ".pdf"
DoCmd.OutputTo acOutputReport, "AutoCountReport", acFormatPDF, strFilePath, False
DoCmd.OutputTo acOutputReport, "AutoCountFieldReport", acFormatPDF, strFilePath2, False

Set msg = ol.CreateItem(olMailItem)
Set atts = msg.Attachments
With msg
    .Subject = "Automatic Report for Office Consumables " & crrntdate
    .Body = "Body text" 'Edited
    .To = "Email" 'Edited
    .CC = ""
    atts.Add strFilePath
    atts.Add strFilePath2
End With

End Function
asked on Stack Overflow Dec 6, 2019 by Mochida • edited Dec 7, 2019 by Compo

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0