Solved

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

Posted on 2016-09-27
5
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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