Solved

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

Posted on 2016-09-27
5
52 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 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