Trying to email several reports but it takes too long

I'm trying to email several reports but it takes too long probably because each report appears on the screen and I really don't need it to do that.  Here is my code presently:  (Is there any way to speed up the process?)  Note, I've tried acViewReport but it still appears first which takes too long.

'Email Reports...
fileNameIncomeStatement = Application.CurrentProject.Path & "\Income Statement_" & todayDateIncomeStatement & ".pdf"
DoCmd.OpenReport "Income Statement", acViewPreview, , "[Div]='" & strDivNumber & "'", acHidden
DoCmd.OutputTo acOutputReport, "Income Statement", acFormatPDF, fileNameIncomeStatement
DoCmd.Close acReport, "Income Statement", acSaveNo
fileNameSalesAnalysis = Application.CurrentProject.Path & "\Sales Analysis_" & todayDateSalesAnalysis & ".pdf"
DoCmd.OpenReport "Sales Analysis", acViewPreview, , "[Div]='" & strDivNumber & "'", acHidden
DoCmd.OutputTo acOutputReport, "Sales Analysis", acFormatPDF, fileNameSalesAnalysis
DoCmd.Close acReport, "Sales Analysis", acSaveNo

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not really. You have to Open the report in order to Output the report as PDF.

Define what you mean by "too long"? 15 seconds? 2 minutes?
Your report has query as a data source. You can modify this query with proper parameter.
Look at simple sample (Table1 -data table, Query1 - source of report, Report1 - report name):
Sub tst()
Dim SQL As String
Dim strDivNumber As String
Dim fileNameIncomeStatement as string
fileNameIncomeStatement = Application.CurrentProject.Path & "\Income Statement_" &  ".pdf"
SQL = "SELECT ID, Dt, ClassDate,Cancelled_Indicator, Div FROM Table1"
strDivNumber = "A"
SQL = SQL & " WHERE [Div]=" & Chr(34) & strDivNumber & Chr(34)
DoCmd.DeleteObject acQuery, "Query1"
CurrentDb.CreateQueryDef "Query1", SQL
DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, fileNameIncomeStatement
End Sub

Open in new window

Look at Sub1 in module
Something that *might* speed things up, assuming Access 2007 or higher is to use TempVars for your criteria (but if the queries take a long time to run, there may not be much perceptible difference).

For example, for DivNumber, add a variable called tvDivNumber to the TempVars collection.  Modify your query to use this variable as the criteria:
SELECT Something FROM YourTable WHERE DivNumber = TempVars!tvDivNumber

Open in new window

Modify your VBA to send the report (since the criteria is set with TempVars, you don't need to open it first)

' Add/set TempVars:
Tempvars.Add "tvDivNumber", strDivNumber 
' Send the report
Docmd.SendObject ObjectType:=acSendReport, _
                    ObjectName:="YourReport", _
                    ObjectFormat:= acFormatPDF, _

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

The method you are using runs the reports TWICE. Once to open them and the second time to output them to the pdf.  

You can go directly to pdf and eliminate the OpenReport step if you change the way your report gets its parameters.  The OutputTo option does not provide a way to pass in the arguments directly as does the OpenReport.  What I do is to use either a form control or a tempvar in my query.  If the report is only run from one form, I use the form control directly.  If the report can be run from multiple forms, you will need to use a tempvar.

So the report's criteria becomes:

WHERE [Div]= Forms!yourform!Div
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm curious how going directly to PDF would allow the user to email the report? You could certainly open the report in the PDF format, but that leaves you with ... a report that's loaded in the Access interface as a PDF. You still have to output it in some manner in order to attach it to an email.
SteveL13Author Commented:
Still working on this issue.  Am going to try experts recommendations by end of day tomorrow.  Note:  The reason this take so long is that the code loops through records and produces over 100 emails.
The outputto command saves the report to a .pdf which is then emailed.  I don't know of any way to create the pdf and email it without first saving the pdf.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You selected several different answers.  Did any of them actually solve the problem?
SteveL13Author Commented:
Long story but to make as short as possible, the conclusion I came to was that there were 4 linked tables.  For whatever reason, and I'm no network expert, the fact that they were linked was slowing to process down considerably.  I ended up replacing them with make table queries and all was fine.  I awarded points because of the efforts of the members to help me but also because all of the suggestions I felt were good for future.
Thanks for the explanation.  Personally, I prefer to not be awarded participation points.  I can't speak for the others.  But a large part of the value of the questions in this forum is knowing which answer actually solved the problem.  So, if one answer solved the problem selecting that one is sufficient.  But if you elect to select multiple answers, it would be best to include an explanation of what answer actually did work or you can even accept your own answer and not award points at all.  No expert will be offended at not being awarded participation points.  It is only the trolls who will care.
SteveL13Author Commented:
Pat.  Ok.  Thank you for the insight.  I appreciate it and will be careful to do it that way next time.
You're welcome.  The experts are continually having conversations regarding how to get questions closed accurately and without rewarding trolls.  The best solution is for the poster to close his own questions (we can close abandoned questions) because only he knows if any answers solved the problem. The asker of course can award whatever points he wants to whomever he wants but it isn't really fair to spread them around just because people participated.  Doing that diminishes the value of actual answers.  Think of it this way - if your teacher gave a test and you were the best student in the class, would you be happy if he always averaged the scores and you got what everyone else got?  How does that policy affect the best student and how does it affect the worst student?  Luckily the Access forum doesn't have many trolls but some of the forums do and it is much more of a problem in those areas.
SteveL13Author Commented:
I understand.  Thank you again for your insight and all of your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.