?
Solved

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

Posted on 2016-09-27
5
Medium Priority
?
66 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 23

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 23

Expert Comment

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

Accepted Solution

by:
Ferruccio Accalai earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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