Solved

MS Access - Email grouped report to Recipient listed in each report.

Posted on 2016-09-27
5
43 Views
Last Modified: 2016-09-28
With the generous help of this forum I currently have a 2010 access db which will export individual grouped reports as PDF.

I was wondering whether I could take this a step further and email each report to a recipient as well.

So if each report had say an 'EmailAddress' field included, is it possible to email the report as a PDF to the address listed in this field?

If that's not possible I don't know whether there could be another way to approach it with Powershell or Excel VBA maybe to match a code in the report file name to an external spreadsheet and email from there? I don't really know the limitations.

Any advice from the experts of how to begin to approach this would be very much appreciated.
Many thanks.

For reference, with the help I so far have:
Private Sub Command2_Click()
DoCmd.RunCommand acCmdSaveRecord
Forms!Form1.SetFocus
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account], [InvoiceNum] FROM [ContactTotals] WHERE (((Contracts.SelectedPrint)=True)) ORDER BY [Account];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[InvoiceNum] = " & Chr(34) & rst![InvoiceNum] & Chr(34)

    DoCmd.OutputTo acOutputReport, "InvTotal", acFormatPDF, "C:\Scripts" & "\" & rst![Account] & " - " & rst![InvoiceNum] & ".pdf"
    DoEvents
    rst.MoveNext
   
Loop

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
Comment
Question by:Jack Marley
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41818136
First you have to add the library Office Outlook to the reference

then just get the email address from the db and use a code like the following

Private Sub Command2_Click()

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim rst As DAO.Recordset
Dim fileName As String
DoCmd.RunCommand acCmdSaveRecord
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account], [InvoiceNum], [EmailAddress] FROM [InvoiceDetail Query1] ORDER BY [Account];", dbOpenSnapshot)
Do While Not rst.EOF
    strRptFilter = "[Account] = " & Chr(34) & rst![Account] & Chr(34)
    fileName = "C:" & "\" & rst![Account] & " - " & rst![InvoiceNum] & ".pdf"
    DoCmd.OutputTo acOutputReport, "InvTotal", acFormatPDF, fileName
    DoEvents
    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .Recipients.Add rst![EmailAddress]
        .Subject = "Some subject"
        .Body = "some body text"
        .Attachments.Add fileName
        .Send
    End With
    rst.MoveNext
   
Loop

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
 

Author Comment

by:Jack Marley
ID: 41818184
Thanks again for you're help.

I'm getting an error at this line:

 
DoCmd.OutputTo acOutputReport, "InvTotal", acFormatPDF, fileName

Open in new window



2501
The OuputTo action was canceled

Do you know what might cause that?

Thanks.
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41818200
Check for The filename above See My example
0
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 500 total points
ID: 41818855
Ok, sorry for my short comment but I was totally out of law (driving and writing to the phone in the same time)

Your code should be as follow (take care about the EmailAddress field. You should create such field in Customers and select it in your ContractTotals
Private Sub Command2_Click()
DoCmd.RunCommand acCmdSaveRecord
Forms!Form1.SetFocus
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account], [InvoiceNum], [EmailAddress] FROM [ContactTotals] WHERE (((Contracts.SelectedPrint)=True)) ORDER BY [Account];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[InvoiceNum] = " & Chr(34) & rst![InvoiceNum] & Chr(34)
    fileName =  "C:\Scripts" & "\" & rst![Account] & " - " & rst![InvoiceNum] & ".pdf"
    DoCmd.OutputTo acOutputReport, "InvTotal", acFormatPDF, fileName
    DoEvents
   Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .Recipients.Add rst![EmailAddress]
        .Subject = "Some subject"
        .Body = "some body text"
        .Attachments.Add fileName
        .Send
    End With
    Set oEmail = nothing
    rst.MoveNext
   
Loop

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
 

Author Comment

by:Jack Marley
ID: 41819360
That's okay you were pointing me in the right direction :)

It works perfectly thank you very much, it's saved me a clumsy powershell script!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now