PDF email from Access

Question

I am trying to email a submission form based on the click of a command button. I have created the code to filter the form based on the 4 primary keys. But when I run the code the FleetID portion is pulling up as blank in the Immediate Pane. The FleetID portion is provided in a combobox. Can somebody help me?

Thanks

On Error GoTo errhandle
    Me.Filter = "CurrentDate= #" & Format(Me!CurrentDate, "yyyy\-mm\-dd") & "# and DiscoverTime= '" & Me!DiscoverTime & "' And TailNumber= '" & Me!TailNumber & "' And FleetID= '" & Me!FleetID & "'"
    Debug.Print Me.Filter
    Me.FilterOn = True
    DoCmd.SendObject acSendForm, "frmETIC",  acFormatPDF, "EMAIL", "", "", "Recovery Report", "Attached is the submitted Recovery Report"

exiterr:
    Exit Sub
errhandle:
    If Err.Number <> 2501 Then
        MsgBox ("Email cancelled!")
    End If
Resume exiterr

Answer 1

Apart from my suggestions in the comment section. I would personally create a query and a desired report from that query. Reports gives you a very neat and professional look unlike forms with their extra controls.

• First create a query same like your forms datasource
• Create report out of that query. Design your report with your logo footer and all other stuffs you would like to have also the printing margin.
• Generate your report with where condition and use the docmd.sendobject

Alternatively

• Generate your report hidden with your where condition
• Save the report as PDF file using docmd.outputTo
• create new outlook email object and attach the PDF file

both ways have their own advantages. i personally use the second one because its much easier for me to customize the email content/template.

Here is a function to create emails:

Function SEND_EMAIL_MESSAGE(mTo As String, mCC As String, mBC As String, mSubject As String, mBody As String, Optional useOwnSignature As Boolean = False, Optional DisplayMsg As Boolean = False, Optional isHTML As Boolean = False, Optional AttachmentPath = "") As Boolean
' Please check the reference for Microsoft Outlook 14.0 object library for outlook 2010.

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookAttach As Outlook.Attachment
    Dim mSignature As String

    On Error GoTo ERROR_EMAIL
    ' Create the Outlook session.
    Set objOutlook = New Outlook.Application

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
        ' Add the To recipient(s) to the message.
        .To = mTo
        .CC = mCC
        .BCC = mBC
        .Subject = mSubject

        If useOwnSignature Then .BodyFormat = olFormatHTML
        .Display

        If useOwnSignature Then
            If isHTML Then

                mSignature = .HTMLBody
                .HTMLBody = mBody & mSignature
            Else
                mSignature = .Body
                .Body = mBody & mSignature
            End If
        Else
            .Body = mBody
        End If

        ' Add attachments to the message.
        If Not IsMissing(AttachmentPath) Then
            Dim mFiles() As String
            If (VBA.Right(AttachmentPath, 1)) <> ";" Then AttachmentPath = AttachmentPath & ";"
            mFiles = VBA.Split(AttachmentPath, ";")
            Dim i As Integer
            For i = 0 To UBound(mFiles) - 1
                If Not mFiles(i) = "" Then Set objOutlookAttach = .Attachments.Add(mFiles(i))
            Next i

        End If

        ' Should we display the message before sending?
        If DisplayMsg Then
            .Display
        Else
            .Send
        End If
    End With

    SEND_EMAIL_MESSAGE = True
EXIT_ROUTINE:
    On Error GoTo 0
    Set objOutlook = Nothing
    Set objOutlookMsg = Nothing
    Exit Function

ERROR_EMAIL:
    SEND_EMAIL_MESSAGE = False
    GoTo EXIT_ROUTINE
End Function

and you here some code you can generate the report and send to email:

strReportName = "rpt_incident_view_single"

        DoCmd.OpenReport strReportName, acViewPreview, , strCriteria, acHidden

        Dim tmpPath As String
        tmpPath = VBA.Environ("temp")
        strMyPath = tmpPath
        If VBA.Right(strMyPath, 1) = "\" Then
            strMyPath = strMyPath & "_" & incident_id & "_" & VBA.Format(Now, "yyyy-dd-mm") & ".pdf"
        Else
            strMyPath = strMyPath & "\" & "_" & incident_id & "_" & VBA.Format(Now, "dd-mm-yyyy") & ".pdf"
        End If

        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strMyPath, False

after saving the report just send it to the email function which will create new email and show it to the user:

SEND_EMAIL_MESSAGE mTo, mCC, mBcc, mSubject, mBody,,,, strMyPath
DoCmd.Close acReport, strReportName
on error resume next
VBA.Kill strMyPath

just modify your code as per your needs. good luck :)