locked
Automatically export PDF to email RRS feed

  • Question

  • We use an old Access system that no one really understands at my company (no one knows Access very well either, I have a basic understanding). We currently use the export to email on the print preview page about 20 times per day for reports. Is there a way to automatically fill out who the email goes to? (goes to the same person every time). Can the subject of the email be populated using using objects on the report (machine #, item #, part name?).

    Thanks!

    -Zach

    Thursday, December 27, 2018 5:16 PM

All replies

  • Hi Zach,

    Unfortunately, to do any sort of automation would mean programming or writing some code, which means someone has to learn a little bit more about Access to do it. In your case, it sounds like you need a custom ribbon to have a separate (or modify the existing) export to PDF button, where those specific data are automatically populated in the email for you.

    Thursday, December 27, 2018 5:42 PM
  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes, amongst other things, an option to email the current invoice to the current customer by means of the SendObject method of the DoCmd object.  The invoice report is attached to the email as a PDF document.  To restrict the report to the current invoice the report's RecordSource query references the InvoiceNumber control on the form in which the button to email the invoice is located.  The code in the button's Click event procedure is:

    Private Sub cmdEmail_Click()

        Dim strTo As String
        Dim strSubject As String
        Dim strMessageText As String
        
        Me.Dirty = False
        
        strTo = Me.Email
        strSubject = "Invoice Number " & Me.InvoiceNumber
        strMessageText = Me.Customer.Column(1) & ":" & _
            vbNewLine & vbNewLine & _
            "Your latest invoice is attached." & _
            vbNewLine & vbNewLine & _
            "Customer Accounts Department, Widget Supply Company"


        DoCmd.SendObject ObjectType:=acSendReport, _
            ObjectName:="rptInvoice", _
            OutputFormat:=acFormatPDF, _
            To:=strTo, _
            Subject:=strSubject, _
            MESSAGETEXT:=strMessageText, _
            EditMessage:=True

    End Sub

    Unlike your current method, the report does not have to be opened in print preview.  The 'Open Current Invoice' button on the form does allow the report to be previewed, however.

    Ken Sheridan, Stafford, England

    Thursday, December 27, 2018 6:26 PM
  • I'll give it a shot! Thanks!
    Friday, December 28, 2018 6:58 PM
  • Good luck! Let us know how it goes.
    Friday, December 28, 2018 7:09 PM