Sending multiple reports via outlook (1 Viewer)

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90
Hello,

I have 5 reports that I want to send via outlook at the same time from a push of a button on an access form.

I have currently have:

Code:
Option Compare Database

Function sndrpt()

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String
Dim strAttach2 As String
Dim strAttach3 As String
Dim strAttach4 As String
Dim strAttach5 As String

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'Output Reports

DoCmd.OutputTo acOutputReport, "qry_SoilB", acFormatXLS, "F:\WH\Ft\Soil Report.xls", False
DoCmd.OutputTo acOutputReport, "qry_SoilM", acFormatXLS, "F:\WH\Ft\Soil Report.xls", False
DoCmd.OutputTo acOutputReport, "qry_SoilR", acFormatXLS, "F:\WH\Ft\Soil Report.xls", False
DoCmd.OutputTo acOutputReport, "qry_SoilS", acFormatXLS, "F:\WH\Ft\Soil Report.xls", False
DoCmd.OutputTo acOutputReport, "qry_SoilSt", acFormatXLS, "F:\WH\Ft\Soil Report.xls", False

'Set Attachments
strAttach1 = "F:\WH\Ft\Soil Report\qry_SoilB.xls"
strAttach2 = "F:\WH\Ft\Soil Report\qry_SoilM.xls"
strAttach3 = "F:\WH\Ft\Soil Report\qry_SoilR.xls"
strAttach4 = "F:\WH\Ft\Soil Report\qry_SoilS.xls"
strAttach5 = "F:\WH\Ft\Soil Report\qry_SoilSt.xls"

'Generate email
With objEmail
.To = "Test@Test.com"
.Subject = "Reports"
.Body = "Report for Current Day"
.Display
.Attachments.Add strAttach1
.Attachments.Add strAttach2
.Attachments.Add strAttach3
.Attachments.Add strAttach4
.Attachments.Add strAttach5
End With

'Remove attachments from drive
Kill strAttach1
Kill strAttach2
Kill strAttach3
Kill strAttach4
Kill strAttach5

End Function

After I compile and run it gets a runtime error '-2147024894(80070002)'.

If I debug it shows:
Code:
.Attachments.Add strAttach1

It does open outlook and inserts email, subject, and body but no attachments.

What am I missing?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
Hi. Was there any error message to go with the error number? If so, what did it say?
 

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90
Hi. Was there any error message to go with the error number? If so, what did it say?

"Cannot find this file. Verify the path and file name are correct."
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
"Cannot find this file. Verify the path and file name are correct."
Okay, it might be that there's a timing issue where Access is not yet done making the file by the time it tries to attach it. To check that, try inserting some delays between making the file and using it.
 

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90
Okay, it might be that there's a timing issue where Access is not yet done making the file by the time it tries to attach it. To check that, try inserting some delays between making the file and using it.

I added
Code:
TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop

Right after 'Set attachments but I am still getting the same error.
 

June7

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,423
You are outputting 5 reports to same filename "Soil Report.xls" - each overwrites previous. Then you reference file path\names that don't exist for attaching.

Need to include each report name in output filename:

DoCmd.OutputTo acOutputReport, "qry_SoilB", acFormatXLS, "F:\WH\Ft\Soil Report\qry_SoilB.xls", False

This assumes folder Soil Report already exists.

Did you really name reports same as query name? Suggest you change report names to something like rpt_SoilB.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
I added
Code:
TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop
Right after 'Set attachments but I am still getting the same error.
Can you please post the new code, so we can tell you if you put the delays in the right places? Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
You are outputting 5 reports to same filename "Soil Report.xls". Then you reference file path\names that don't exist for attaching.

Need to include each report name in output filename:

DoCmd.OutputTo acOutputReport, "qry_SoilB", acFormatXLS, "F:\WH\Ft\Soil Report\qry_SoilB.xls", False
Oops, sorry, I didn't notice this. This is a very good point!
 

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90
You are outputting 5 reports to same filename "Soil Report.xls" - each overwrites previous. Then you reference file path\names that don't exist for attaching.

Need to include each report name in output filename:

DoCmd.OutputTo acOutputReport, "qry_SoilB", acFormatXLS, "F:\WH\Ft\Soil Report\qry_SoilB.xls", False

Did you really name reports same as query name? Suggest you change report names to something like rpt_SoilB.

Wow.. I knew I was missing something that simple. For some reason, I thought I just needed the location folder and not the file name for that.

I will be changing the report names once it is complete.

Much appreciated.
 

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90
Is there a way to have all 5 of the reports be in one excel file but separate sheets?
 

June7

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,423
I think that would require Excel automation code. But I've only seen this done with queries, not reports. Why not export queries?

Have you considered building a report with 5 subreports?

Why reports to Excel? Often reports don't export to Excel nicely. Would PDF be adequate?
 

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90
I think that would require Excel automation code. But I've only seen this done with queries, not reports. Why not export queries?

Have you considered building a report with 5 subreports?

Why reports to Excel? Often reports don't export to Excel nicely. Would PDF be adequate?

I initially wanted reports because I was creating additional calculations(Sums, Averages and %'s) at the end but then I found out that when you export reports those additional calculations won't follow. I'm not sure what the best option would be at this point.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
Is there a way to have all 5 of the reports be in one excel file but separate sheets?

Hi. Instead of OutputTo, try using TransferSpreadsheet and specify the name of the sheet using the Range argument.
 

Mr. Southern

Registered User.
Local time
Today, 09:40
Joined
Aug 29, 2019
Messages
90

Using
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_SoilB", "F:\WH\Ft\Soil Report\qry_SoilB.xls", True, "Test1"

I get Runtime Error '3011' " Microsoft Access database engine could not find the object qry_SoilB. Make sure the object exists and that you spell its name and path name correctly....

If I change it to search for the original query it works.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
Using
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_SoilB", "F:\WH\Ft\Soil Report\qry_SoilB.xls", True, "Test1"
I get Runtime Error '3011' " Microsoft Access database engine could not find the object qry_SoilB. Make sure the object exists and that you spell its name and path name correctly....

If I change it to search for the original query it works.
What do you mean by "original query?"
 

Users who are viewing this thread

Top Bottom