Button on form to create PDF and email based on record shown - VBA help

[Allie12380]

I have the following VBA Code which creates my email correctly. I would like to add code that will also attach the PDF of report for the record showing on the form. Just not sure what, where, or how to add the right VBA code to Command0141_Click().

Private Sub Command141_Click()
Dim Msg As String
Msg = "Please review the interpreter request for " & [APPT DATE] & " at " & Format([APPT TIME], "hh:mm AM/PM") & " and accept or decline."
Dim O As Outlook.Application
Dim M As Outlook.MailItem
Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)
With M
.BodyFormat = olFormatHTML
.HTMLBody = Msg
.To = InterpreterEmail
'.cc = mbojo@uab.edu; next
.Subject = "Interpreter request for " & [APPT DATE] & " " & Format([APPT TIME], "hh:mm AM/PM")
.Display
'.send -- sends without any preview...display previews it which is what team must have
End With


Set M = Nothing
Set O = Nothing
End Sub

Below additional information about report and other VBA tried:

Report Information:
Currently have a button on form that on click creates the PDF preview report correctly.
Report name: rptInterpreterClinicConfirmationForm
Filter that works to pull right record to run report: [qryInterpreterClinicConfirmation]![ID]=[Forms]![frmAppointments]![ID]

Other VBA line tried:
From research, thought would add something like below but can't get it to work with the VBA for Command141:
NOTE: when I use this as a separate button (Command143) it works and creates the PDF and attaches to email but it is for all records not just one shown and email is blank not populating anything but subject line. So not sure if I can edit this somehow to get the rest of the information or edit above.

Private Sub Command143_Click()
'DoCmd.SendObject acSendReport, "rptAppointmentConfirmation", acFormatPDF, Forms!frmAppointments!InterpreterEmail, , , _
'"Please review the interpreter request for " & [APPT DATE] & " at " & Format([APPT TIME], "hh:mm AM/PM") & " and accept or decline."
End Sub

Any VBA help would be greatly appreciated! Would love to have one button that creates the email and attaches the specific record PDF.

[Welshgasman]

Do yourself a big favour.Give your controls meaningful names. Command nnn is not going to mean anything to anyone, including you 6 months down the road.Please also post code within code tags to retain indentation which I hope you are using.
From chatgpt.com

Sub ExportReportToPDFAndEmail()
    Dim objOutlook As Object
    Dim objEmail As Object
    Dim strReportName As String
    Dim strPDFPath As String
    Dim strFileName As String
    Dim strEmailTo As String
    Dim strCriteria As String
    Dim InvoiceID As Long
    
    ' Set your report name
    strReportName = "rptInvoice"  ' Change to your report name


    ' Get the Invoice ID (You can change how you pass the record)
    InvoiceID = Forms!frmInvoices!InvoiceID  ' Adjust form & field names


    ' Define the PDF file name and path
    strFileName = "Invoice_" & InvoiceID & ".pdf"
    strPDFPath = CurrentProject.Path & "\" & strFileName


    ' Export the report to PDF for the specific record
    strCriteria = "[InvoiceID] = " & InvoiceID  ' Change field name if needed


    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strPDFPath
    DoCmd.Close acReport, strReportName


    ' Create Outlook email
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(0)


    ' Define recipient email (Adjust as needed)
    strEmailTo = Forms!frmInvoices!CustomerEmail  ' Change field name


    ' Configure email details
    With objEmail
        .To = strEmailTo
        .Subject = "Invoice #" & InvoiceID
        .Body = "Dear Customer," & vbCrLf & vbCrLf & _
                "Please find attached your invoice." & vbCrLf & vbCrLf & _
                "Best Regards," & vbCrLf & "Your Company Name"
        .Attachments.Add strPDFPath  ' Attach the PDF
        .Display  ' Change to .Send to send automatically

[June7]

Open filtered report then SendObject or Outlook automation. One button, not two.

If you don't need to save a PDF file, can just use SendObject.

Do you have that filter criteria in report Filter property or in query the report is based on? Don't even have to open the report object.

[Allie12380]

Trying this today. Will follow up later.
I do have indentions but when copied it out, it broke it up crazy. next time will clean up to show the indentions.
Agree - the command (xx) not helpful. Been working to rename all these in the inherited database.

[Allie12380]

One button is the goal. Don't need to save PDF. Filter criteria is in query.

[Micron]

I do have indentions but when copied it out, it broke it up crazy.

Then you didn't post code between code tags as requested (hashtag button on posting toolbar). Don't, and the site will left justify everything. It will also insert spaces after each unbroken string of 50 characters in a line, which confuses people as they focus on a problem that isn't really there.

[June7]

Also, will convert some character strings to emojis, adding to confusion.

[Allie12380]

This was such a help! Thank you. This code worked perfectly! Appreciate your kindness in providing help.

Do yourself a big favour.Give your controls meaningful names. Command nnn is not going to mean anything to anyone, including you 6 months down the road.Please also post code within code tags to retain indentation which I hope you are using.
From chatgpt.com

Sub ExportReportToPDFAndEmail()
    Dim objOutlook As Object
    Dim objEmail As Object
    Dim strReportName As String
    Dim strPDFPath As String
    Dim strFileName As String
    Dim strEmailTo As String
    Dim strCriteria As String
    Dim InvoiceID As Long
    
    ' Set your report name
    strReportName = "rptInvoice"  ' Change to your report name


    ' Get the Invoice ID (You can change how you pass the record)
    InvoiceID = Forms!frmInvoices!InvoiceID  ' Adjust form & field names


    ' Define the PDF file name and path
    strFileName = "Invoice_" & InvoiceID & ".pdf"
    strPDFPath = CurrentProject.Path & "\" & strFileName


    ' Export the report to PDF for the specific record
    strCriteria = "[InvoiceID] = " & InvoiceID  ' Change field name if needed


    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strPDFPath
    DoCmd.Close acReport, strReportName


    ' Create Outlook email
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(0)


    ' Define recipient email (Adjust as needed)
    strEmailTo = Forms!frmInvoices!CustomerEmail  ' Change field name


    ' Configure email details
    With objEmail
        .To = strEmailTo
        .Subject = "Invoice #" & InvoiceID
        .Body = "Dear Customer," & vbCrLf & vbCrLf & _
                "Please find attached your invoice." & vbCrLf & vbCrLf & _
                "Best Regards," & vbCrLf & "Your Company Name"
        .Attachments.Add strPDFPath  ' Attach the PDF
        .Display  ' Change to .Send to send automatically