Re: Automate *.pdf (multiple reports) to send from Access thru MS Outlook (1 Viewer)

[nobby]

Re: Automate *.pdf (multiple reports) to send from Access thru MS Outlook

After hours of search I found this excellent code from Trevor G and have used it on a new project. Its perfect for me with 1 exception.
I would like it to be able to filter the reports by user. currently it sends the same attachments to everyone I email
It would like something like
DoCmd.OutputTo acOutputReport, "rptinvoice", acFormatPDF,invoiceid = me. invoiceid "F:\destination\rptinvoice.pdf", False or
strAttach1 = "F:\destination\rptinvoice.pdf" &me.invoiceid =invoiceid
but whatever combination i use fails

the original thread was started by pnevilm

and the reply date was 10-11-2010, 10:23 AM

 

[PeterF]

Re: Automate *.pdf (multiple reports) to send from Access thru MS Outlook

Place of original thread.
Access doesn't like a Where parameter in the OutputTo command. To use it you have to open the report in front like:

Dim strRapName as String
Dim strWhere as String

strRapName = "rptinvoice",
strWhere = "invoiceid = me. invoiceid" 

   DoCmd.OpenReport strRapName , acPreview, , strWhere, acHidden
   DoCmd.OutputTo acOutputReport, strRapName, acFormatPDF
   DoCmd.Close acReport, strRapName

Note I put your Where string and your rapport name in a variable to make it easier to change things.

 

[nobby]

Re: Automate *.pdf (multiple reports) to send from Access thru MS Outlook

Hi Peter
Thanks for your prompt reply

Sorry, I should have submitted the full code to give you an idea of what I was trying to achieve

I need to email 2 attachments in pdf format to one email recipient. the attachments are reports generated for that particular client, currently the code below does the job well except that it sends unrelated reports to the client thats why I was looking to add something like me.invoiceid = invoiceid. Please see code below

Private Sub Command246_Click()
Dim strAttach1 As String
Dim strAttach2 As String
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
Rem Output Reports
Rem <!-- change "Reportx" to match the report names you wish to export. & _
rem IMPORTANT: Make sure the location you select to save your reports to exists, Access will & _
rem not create the folders for you. -->
Rem DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "rptinvoice", acFormatPDF, "F:\Work stuff\destination\rptinvoice.pdf", False
DoCmd.OutputTo acOutputReport, "rptlandlordreport", acFormatRTF, "F:\Work stuff\destination\rptlandlordreport.pdf", False
Rem Set Attachments
Rem <!-- make sure to correlate the attachments to each of the reports you wish to send -->
strAttach1 = "F:\Work stuff\destination\rptinvoice.pdf"
strAttach2 = "F:\Work stuff\\destination\rptlandlordreport.pdf"

Rem Generate email
With objEmail
.to = [AgentEmail1]
.Subject = "invoice number " & InvoiceID
.Body = "please find attached a Gas safety inspection report and my invoice "
.Display
.Attachments.Add strAttach1
.Attachments.Add strAttach2
End With
Rem Remove attachments from drive
Kill strAttach1
Kill strAttach2
End Sub

 

[PeterF]

Re: Automate *.pdf (multiple reports) to send from Access thru MS Outlook

Again You can't filter a report in the Docmd.OutputTo function. To filter your report you need to open it first in preview mode then export it, like I did in my previous answer.