Sending multiple reports via outlook

[Mr. Southern]

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:

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:

.Attachments.Add strAttach1

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

What am I missing?

 

[theDBguy]

Hi. Was there any error message to go with the error number? If so, what did it say?

 

[Mr. Southern]

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]

"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]

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

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]

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.

 

[theDBguy]

I added

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]

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]

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]

Can you please post the new code, so we can tell you if you put the delays in the right places? Thanks.

After changing the code, I do not even need a time delay for this.

 

[Mr. Southern]

Is there a way to have all 5 of the reports be in one excel file but separate sheets?

 

[June7]

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]

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]

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]

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

hmmm I haven't done that before. Do you have an example?

 

[theDBguy]

hmmm I haven't done that before. Do you have an example?

Check here...

 

[June7]

And for more ideas http://accessmvp.com/KDSnell/EXCEL_MainPage.htm

 

[Mr. Southern]

Check here...

Using

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]

Using

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?"

 

[Mr. Southern]

What do you mean by "original query?"

The query that I use to create the report.