Automatically emailing a Access report once a week

[ande-s]

Hi Guys.

I have come up with an Access database with help from a few guys on here, which contains a couple of tables, and lots of reports etc. I can easily email the reports out using the command buttons placed on a form in my database, but I was wondering if it is possible to automatically open the Database and email off a certain report once a week from say a batch file?

Any help will be greatly appreciated as I cannot find much on the net to help.

Thanks.

 

[CBrighton]

Create a macro to email all the objects that you need then create a scheduled task in Windows to open the database and run a macro using the /x flag.

e.g.

msaccess.exe c:\Database.mdb /x mcrEmailReports

 

[ande-s]

Thanks for that information, the next question is though, how do I create the macro to email the report?

 

[CBrighton]

You currently have command buttons setup to send the reports, so I assume you have either a macro setup already or have the VBA code to do it.

If you have the code you can create a new VBA module, create a function and copy the code from the command buttons into there. Then the macro can do "RunCode" to call that function.

 

[ande-s]

Ok, so I have the following:

msaccess.exe E:\user\Documents\Project.accdb /x mcr_Email_Project_Overview

This works fine, however, when I run the batch file, I get an error with Outlook which I have attached as an image requiring me to manually click "Allow"

 

[CBrighton]

The functions required for Outlook email automation are in my post in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=216409

It involves adding functions to modules of both the access database and Outlook session and also using MS Office's selfcert program to certify the code in Outlook(google it, it should be in your office folder).

If you have any further questions let me know.

:edit:

Note, with these functions I output the object (overwriting the previous output) then use that export as an attachment in an email.

 

[ande-s]

Brilliant, thanks for your help

 

[ande-s]

Is there a different set of instructions for thunderbird?

 

[CBrighton]

You will need to google "VBA thunderbolt automation" to find the functions, etc for thunderbolt.

I only use Office here so have never automated thunderbolt.

 

[ande-s]

After a couple of days trying to get this to work, I decided that instead of emailing the report, I will fire off a script that exports it to a PDF and then use Python to email it out.

However, I am struggling to get this to automatically export to a PDF file via the command line.

I have tried firstly to create a marco to do this using the OutputTo call but kept getting an error stating that I couldn't call this embedded macro:

"The 'OutputTo' macro action cannot run with the specified 'Output File' argument either when in disabled mode or when called from an embedded macro

I then tried to create a macro which called the following function:

Function PrintToPDF()

MsgBox ("MSG")
On Error GoTo PrintToPDF_Err
'Function can be called from any report with this: "PrintToPDF(Screen.ActiveForm.Name)"

'SrcFile = name of report the function was called from, as generated by Screen.ActiveForm.Name
'DestPath = Destination path for PDF file
Dim DestPath As String
'DestFile = Destination file name for PDF file
Dim DestFile As String
'ShowPdf = launch acrobat and display saved PDF file
Dim ShowPdf As Boolean
Dim SrcFile As String


SrcFile = "qry_Projects"

'Saves the file to the desktop of the current user
DestPath = "e:\"
'Formats the file name like this: "YYYY-MM-DD-ReportNameHere.pdf"
DestFile = Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "-" & SrcFile

ShowPdf = False
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint

PrintToPDF_Exit:
Exit Function

PrintToPDF_Err:
MsgBox Error$
Resume PrintToPDF_Exit

End Function

Again, this works fine when running the code, but when I try to call the code from a macro I cannot seem to find the function.

Any ideas?

 

[ande-s]

Anyone have any thoughts?

 

[CBrighton]

It's been a while since I had to think about Windows file name rules, but is - allowed in a file name?

Try changing the -'s to _'s (I know _'s are allowed in file names).

I was going to suggest a different location as the comment says it saves to the desktop (which may be considered a system folder which might have caused a problem, or maybe not) but the actual code seems to export to e:\.

 

[ande-s]

The code works fine itself when executed from a command button. ( I took the "-" out) The problem I'm having is getting that code to execute from a macro, or by calling the macro from the command line

 

[CBrighton]

Where is the function?

Is it in a proper module or inside a form / report's module?

 

[ande-s]

Right, I have the code being called by the macro and run from the command line, however, the macro fails with error: 2950

 

[CBrighton]

Did you sign the code in outlook then close & reopen (and click enable macros on outlook)?

Here's the MS site for that error: http://support.microsoft.com/kb/931407

 

[ande-s]

I went down another route and now I just create the PDF files and save them to a location. But the macro that calls the code to do that is failing.

 

[ande-s]

Problem solved. It now exports to a PDF, but the application stays open afterwards.

Can it be closed from the macro?

 

[ande-s]

Actually, that is also solved. Things seem to be working as expected now so I will do some proper testing

 

[CBrighton]

Which application? If you mean Access then add Quit to the end of the macro (or DoCmd.Quitafter the loop if you are calling it via VBA rather than a macro).