Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

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

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
Jack Marley
Asked:
Jack Marley
  • 3
  • 2
1 Solution
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
Jack MarleyAuthor Commented:
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
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
Check for The filename above See My example
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
Jack MarleyAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now