locked
Emailing a report without opening the email window RRS feed

  • Question

  • Hi All,

    Me again, I am working on a database for myself that logs clients enquiries and emails them directly to the parties concerned, I have used the email report button but dont like the fact that it opens the email window and asks you to choose the format. I have found a piece of code online which predetermines the file type and sends the email to the recipient which I copied by hand to avoid any hidden code being taken accross but surprise surprise I am getting code errors coming up, I have checked and rechecked the code to make sure that it matches the original but it still keeps bringing up syntax errors, does the code below look right to you or is it again completely flawed.

    Private Sub Command299_Click()
    On Error GoTo CmdEmailPDF_Click_Err
    Dim Subject As String
    Dim MyFileName As String
    Subject = Me.CustomerID & "-" & "Company Branding UK Enquiry Details" & ".pdf"
    MyFileName = Me.CustomerID & "_" & "Company Branding UK Enquiry Details" & ".pdf"

    'Open Company Branding UK Enquiry Details in preview mode
    DoCmd.OpenForm "Company Branding UK Enquiry Details", acNormal, , "[CustomerID]='Send a PDF of the form to an email"

    DoCmd.SendObject acForm, "Company Branding UK Enquiry Details", "PDFFormat (*.pdf)","", richard.nock@companybrandinguk.co.uk, "", Subject, "Here is your latest customer enquiry.", True, ""
    'Close the form

    Do Cmd.Close acForm, "Company Branding UK Enquiry Details", acSaveNo

    CmdEmailPDF_Click_Exit:
    CmdEmailPDF_Click_Err:
    MsgBox Error$
    Resume CmdEmailPDF_Click_Exit

    End Sub

    Thanking you in advance.

    Richard

    Saturday, March 3, 2018 8:02 PM

Answers

  • 1) "[CustomerID]='Send a PDF of the form to an email" is not valid. Do you want to filter the form for a specific CustomerID?

    2) "PDFFormat (*.pdf)" is not valid, it should be "PDF Format (*.pdf)". Better use acFormatPDF.

    3) The argument True in the DoCmd.SendObject line will let you edit the email. If you want to send it directly, use False.

    4) There should be quotes around richard.nock@companybrandinguk.co.uk

    5) Do Cmd.Close should be DoCmd.Close

    6) You ALWAYS run MsgBox Error$. There should be a line

    Exit Sub

    between CmdEmailPDF_Click_Exit and CmdEmailPDF_Click_Err.

    Here is my best guess of what it should look like:

    Private Sub Command299_Click()
        Dim Subject As String
        Dim MyFileName As String

        On Error GoTo CmdEmailPDF_Click_Err

        Subject = Me.CustomerID & "-" & "Company Branding UK Enquiry Details" & ".pdf"
        MyFileName = Me.CustomerID & "_" & "Company Branding UK Enquiry Details" & ".pdf"

        'Open Company Branding UK Enquiry Details in preview mode
        DoCmd.OpenForm "Company Branding UK Enquiry Details", , , "[CustomerID]=" & Me.CustomerID

        'Send a PDF of the form to an email"
        DoCmd.SendObject acForm, "Company Branding UK Enquiry Details", acFormatPDF, , _
            "richard.nock@companybrandinguk.co.uk", , Subject, "Here is your latest customer enquiry.", False
        'Close the form
        DoCmd.Close acForm, "Company Branding UK Enquiry Details", acSaveNo
        Exit Sub

    CmdEmailPDF_Click_Err:
        MsgBox Error$
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, March 3, 2018 8:58 PM

All replies

  • 1) "[CustomerID]='Send a PDF of the form to an email" is not valid. Do you want to filter the form for a specific CustomerID?

    2) "PDFFormat (*.pdf)" is not valid, it should be "PDF Format (*.pdf)". Better use acFormatPDF.

    3) The argument True in the DoCmd.SendObject line will let you edit the email. If you want to send it directly, use False.

    4) There should be quotes around richard.nock@companybrandinguk.co.uk

    5) Do Cmd.Close should be DoCmd.Close

    6) You ALWAYS run MsgBox Error$. There should be a line

    Exit Sub

    between CmdEmailPDF_Click_Exit and CmdEmailPDF_Click_Err.

    Here is my best guess of what it should look like:

    Private Sub Command299_Click()
        Dim Subject As String
        Dim MyFileName As String

        On Error GoTo CmdEmailPDF_Click_Err

        Subject = Me.CustomerID & "-" & "Company Branding UK Enquiry Details" & ".pdf"
        MyFileName = Me.CustomerID & "_" & "Company Branding UK Enquiry Details" & ".pdf"

        'Open Company Branding UK Enquiry Details in preview mode
        DoCmd.OpenForm "Company Branding UK Enquiry Details", , , "[CustomerID]=" & Me.CustomerID

        'Send a PDF of the form to an email"
        DoCmd.SendObject acForm, "Company Branding UK Enquiry Details", acFormatPDF, , _
            "richard.nock@companybrandinguk.co.uk", , Subject, "Here is your latest customer enquiry.", False
        'Close the form
        DoCmd.Close acForm, "Company Branding UK Enquiry Details", acSaveNo
        Exit Sub

    CmdEmailPDF_Click_Err:
        MsgBox Error$
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, March 3, 2018 8:58 PM
  • Something else: I think you'd be better off sending a report than sending a form.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 3, 2018 9:07 PM
  • Thank you, I am trying to send a specific form for a customer enquiry and send the report for that specific entry to the recipient so they have almost immediate updates of customer enquires. Can I tweak the ID part to help achieve this?


    • Edited by R.Nock Saturday, March 3, 2018 9:11 PM spelling mistake
    Saturday, March 3, 2018 9:09 PM
  • Hi,

    It does send a report version of the form, I am just trying to minimize the steps it takes to do it so we can eliminate human error as much as possible.

    Saturday, March 3, 2018 9:16 PM
  • If you provide more detailed information we may be able to help you better.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 3, 2018 10:24 PM
  • Hi,

    to be honest I am happy with how it works now, thank you all for your help. I do have another question but I will set up a new string for this.

    Kind Regards

    Richard

    Monday, March 5, 2018 4:53 PM