VBA output report to PDF and email (1 Viewer)

[Morphies]

Good morning all,

I'm battling with some code that takes a report and sends it out to a client, with a filename built from the [Customer ID] and [QuoteRef] fields.

This is my current code:

Private Sub Command22_Click()
    On Error Resume Next ' Suppress error messages temporarily
    
    ' Open the report in preview mode with a filter based on [QuoteRef]
    DoCmd.OpenReport "rptQuote", acViewPreview, , "[QuoteRef] = '" & Me.[QuoteRef] & "'", acWindowNormal
    
    ' Check if the report is open before attempting to send the email
    If CurrentData.AllReports("rptQuote").IsLoaded Then
        ' Extract the first word from the field Rep_ID to use as the recipient's first name
        Dim recipientFirstName As String
        recipientFirstName = StrConv(Split(Me.[Rep_ID], " ")(0), vbProperCase) ' Convert to proper case
        
        ' Construct a more personalized email body
        Dim emailBody As String
        emailBody = "Dear " & recipientFirstName & "," & vbCrLf & vbCrLf & _
                    "I trust this email finds you well. Attached, please find the quotation against your reference No: " & Me.[CustomerRef] & "." & vbCrLf & vbCrLf & _
                    "Please review the attached document, and do not hesitate to contact us if you have any questions or require further clarification." & vbCrLf & vbCrLf & _
                    "For any orders that may arise from this quotation, kindly send them to orders@clarityndt.co.uk." & vbCrLf & vbCrLf & _
                    "Thank you for considering Clarity NDT for your requirements." & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & _
                    "Clarity NDT Limited"
        
        ' Send email with the report attached as a PDF and the personalized email body
        DoCmd.SendObject acSendReport, "rptQuote", acFormatPDF, , "", "", _
            "Quotation against your reference No: " & Me.[CustomerRef], emailBody, True
        
        ' Close the report after sending the email
        DoCmd.Close acReport, "rptQuote"
    Else
        MsgBox "The report 'rptQuote' is not open.", vbExclamation
    End If
    
    On Error GoTo 0 ' Reset error handling to default behavior
End Sub

When I preview the report, the report has the correct name at the top, but for some unknown reason when I use the "email" button, which runs the above code, no matter which record I am on the attached pdf is AALWAYS named FUR001-13545.

When I open the file, the file is correct.

For example if i raise a quote for client SPE001 with reference 16097, the attachment is called FUR001-13545 but the reference and internals of the file match those of SPE001-16097

Any ideas where this is going wrong?

 

[arnelgp]

maybe you are filling the Bcc, rather than the Subject, change to:

DoCmd.SendObject acSendReport, "rptQuote", acFormatPDF, , "", "", "", _
"Quotation against your reference No: " & Me.[CustomerRef], emailBody, True

 

[Morphies]

Fairly confident that is the subject section of the code.. Adding in the additional ,"" is highlighted "subject" then causes the button to do nothing atall.

The button works as expected, creates an email with the PDF attachment, it's just the PDF attachment name is collecting FUR001-13545 from somewhere, no matter what record is used to create it.

When I preview the file, the RptQuote correctly comes up as it should, for example SPU001-16047.

When I click the email button, to run the code to email it, it uses the correct record to create the file, and within the pdf the details are correct, including references to SPU001-16047 but the filename is FUR001-13545

 

[Gasman]

That will be the report caption?
Plus when you send the report, you do not have any filter on it?

 

[Morphies]

Your diamond. FUR001-13545 was in the report caption

The query that builds the report is filtering on: [Forms]![frmQuote]![QuoteRef]

I've removed the caption from the report itself, now I'm trying to get that loaded in during the VBA:

Private Sub Command22_Click()
    On Error Resume Next ' Suppress error messages temporarily
    
    ' Set the caption of rptQuote to [Customer ID]-[QuoteRef]
    Reports("rptQuote").Caption = Me.[Customer ID] & "-" & Me.[QuoteRef]
    
    ' Open the report in preview mode with a filter based on [QuoteRef]
    DoCmd.OpenReport "rptQuote", acViewPreview, , "[QuoteRef] = '" & Me.[QuoteRef] & "'", acWindowNormal
    
    ' Check if the report is open before attempting to send the email
    If CurrentData.AllReports("rptQuote").IsLoaded Then
        ' Extract the first word from the field Rep_ID to use as the recipient's first name
        Dim recipientFirstName As String
        recipientFirstName = StrConv(Split(Me.[Rep_ID], " ")(0), vbProperCase) ' Convert to proper case
        
        ' Construct a more personalized email body
        Dim emailBody As String
        emailBody = "Dear " & recipientFirstName & "," & vbCrLf & vbCrLf & _
                    "I trust this email finds you well. Attached, please find the quotation against your reference No: " & Me.[CustomerRef] & "." & vbCrLf & vbCrLf & _
                    "Please review the attached document, and do not hesitate to contact us if you have any questions or require further clarification." & vbCrLf & vbCrLf & _
                    "For any orders that may arise from this quotation, kindly send them to orders@clarityndt.co.uk." & vbCrLf & vbCrLf & _
                    "Thank you for considering Clarity NDT for your requirements." & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & _
                    "Clarity NDT Limited"
        
        ' Send email with the report attached as a PDF and the personalized email body
        DoCmd.SendObject acSendReport, "rptQuote", acFormatPDF, , "", "", _
            "Quotation against your reference No: " & Me.[CustomerRef], emailBody, True
        
        ' Close the report after sending the email
        DoCmd.Close acReport, "rptQuote"
    Else
        MsgBox "The report 'rptQuote' is not open.", vbExclamation
    End If
    
    On Error GoTo 0 ' Reset error handling to default behavior
End Sub

This is just sending the file as "RptQuote.pdf"

 

[Gasman]

Your diamond. FUR001-13545 was in the report caption

The query that builds the report is filtering on: [Forms]![frmQuote]![QuoteRef]

So why the need of

DoCmd.OpenReport "rptQuote", acViewPreview, , "[QuoteRef] = '" & Me.[QuoteRef] & "'", acWindowNormal

if the report is always going to refer to the Form QuoteRef?

Or is it because the SendObject just uses the report as it is already open, instead of opening a new copy?

Edit: Just tested and that is why.

 

[Morphies]

I cant answer that . I have insufficient knowledge

The report isn't always open, it's launched as required from the form controls.

 

[olxx]

You have a weird approach. You don' t actually need to open report.
Try it that way:
Grab the data from the form to construct file name (quoteref etc.).
Use DoCmd.OutputTo acOutputReport method to save the pdf file.
Open email, fill the fields and attach the pdf file.

 

[Gasman]

You have a weird approach. You don' t actually need to open report.
Try it that way:
Grab the data from the form to construct file name (quoteref etc.).
Use DoCmd.OutputTo acOutputReport method to save the pdf file.
Open email, fill the fields and attach the pdf file.

If you do not open the report, how do you filter it for the correct record?
This way you do not need to create a pdf file and save it.

 

[theDBguy]

Try moving the line to change the caption below the line that opens the report.

 

[olxx]

If you do not open the report, how do you filter it for the correct record?
This way you do not need to create a pdf file and save it.

In my case, report' s recordsource is the same query that is used in form that is creating the report. No filtering is needed as it is already filtered.
Pdf file gets saved in both cases, but in my approach you can choose where it is saved (for let's say archiving purposes). And if you don' t need it you can always delete it afterwards. I find DoCmd.OutputTo method more flexible.

 

[Morphies]

I cannot get this to output with the filename how I want it, I've even tried moving to saving then attaching

Try moving the line to change the caption below the line that opens the report.

like this?

Private Sub Command22_Click()
    On Error Resume Next ' Suppress error messages temporarily
    
    ' Open the report in preview mode with a filter based on [QuoteRef]
    DoCmd.OpenReport "rptQuote", acViewPreview, , "[QuoteRef] = '" & Me.[QuoteRef] & "'", acWindowNormal
    
    
    ' Set the caption of rptQuote to [Customer ID]-[QuoteRef]
    Reports("rptQuote").Caption = Me.[Customer ID] & "-" & Me.[QuoteRef]
        
    ' Check if the report is open before attempting to send the email
    If CurrentData.AllReports("rptQuote").IsLoaded Then
        ' Extract the first word from the field Rep_ID to use as the recipient's first name
        Dim recipientFirstName As String
        recipientFirstName = StrConv(Split(Me.[Rep_ID], " ")(0), vbProperCase) ' Convert to proper case
        
        ' Construct a more personalized email body
        Dim emailBody As String
        emailBody = "Dear " & recipientFirstName & "," & vbCrLf & vbCrLf & _
                    "I trust this email finds you well. Attached, please find the quotation against your reference No: " & Me.[CustomerRef] & "." & vbCrLf & vbCrLf & _
                    "Please review the attached document, and do not hesitate to contact us if you have any questions or require further clarification." & vbCrLf & vbCrLf & _
                    "For any orders that may arise from this quotation, kindly send them to orders@clarityndt.co.uk." & vbCrLf & vbCrLf & _
                    "Thank you for considering Clarity NDT for your requirements." & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & _
                    "Clarity NDT Limited"
        
        ' Send email with the report attached as a PDF and the personalized email body
        DoCmd.SendObject acSendReport, "rptQuote", acFormatPDF, , "", "", _
            "Quotation against your reference No: " & Me.[CustomerRef], emailBody, True
                
        ' Close the report after sending the email
        DoCmd.Close acReport, "rptQuote"
    Else
        MsgBox "The report 'rptQuote' is not open.", vbExclamation
    End If
    
    On Error GoTo 0 ' Reset error handling to default behavior
End Sub

This still outputs the attachment as rptQuote.pdf

 

[theDBguy]

This still outputs the attachment as rptQuote.pdf

Comment out your On Error Resume Next line to see what may be happening.

 

[Gasman]

I cannot get this to output with the filename how I want it, I've even tried moving to saving then attaching


like this?

Private Sub Command22_Click()
    On Error Resume Next ' Suppress error messages temporarily
  
    ' Open the report in preview mode with a filter based on [QuoteRef]
    DoCmd.OpenReport "rptQuote", acViewPreview, , "[QuoteRef] = '" & Me.[QuoteRef] & "'", acWindowNormal
  
  
    ' Set the caption of rptQuote to [Customer ID]-[QuoteRef]
    Reports("rptQuote").Caption = Me.[Customer ID] & "-" & Me.[QuoteRef]
      
    ' Check if the report is open before attempting to send the email
    If CurrentData.AllReports("rptQuote").IsLoaded Then
        ' Extract the first word from the field Rep_ID to use as the recipient's first name
        Dim recipientFirstName As String
        recipientFirstName = StrConv(Split(Me.[Rep_ID], " ")(0), vbProperCase) ' Convert to proper case
      
        ' Construct a more personalized email body
        Dim emailBody As String
        emailBody = "Dear " & recipientFirstName & "," & vbCrLf & vbCrLf & _
                    "I trust this email finds you well. Attached, please find the quotation against your reference No: " & Me.[CustomerRef] & "." & vbCrLf & vbCrLf & _
                    "Please review the attached document, and do not hesitate to contact us if you have any questions or require further clarification." & vbCrLf & vbCrLf & _
                    "For any orders that may arise from this quotation, kindly send them to orders@clarityndt.co.uk." & vbCrLf & vbCrLf & _
                    "Thank you for considering Clarity NDT for your requirements." & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & _
                    "Clarity NDT Limited"
      
        ' Send email with the report attached as a PDF and the personalized email body
        DoCmd.SendObject acSendReport, "rptQuote", acFormatPDF, , "", "", _
            "Quotation against your reference No: " & Me.[CustomerRef], emailBody, True
              
        ' Close the report after sending the email
        DoCmd.Close acReport, "rptQuote"
    Else
        MsgBox "The report 'rptQuote' is not open.", vbExclamation
    End If
  
    On Error GoTo 0 ' Reset error handling to default behavior
End Sub

This still outputs the attachment as rptQuote.pdf

Why not just set the caption of the report to those values in the report and be done with it?

 

[Morphies]

Oddly enough I already have the following in the onload event for the rptQuote report:

Private Sub Report_Load()
Me.Caption = [Forms]![FrmQuote]![lookupCustomers] & "-" & [Forms]![FrmQuote]![QuoteRef]
End Sub

When I preview the report, the caption is correct, however when emailing it, the attachment is the report name, rptQuote and not the caption.

 

[Gasman]

Works for me?

Private Sub Report_Load()
Me.Caption = Me.Account
End Sub

Try using Me.

 

[Pat Hartman]

When I preview the report, the caption is correct, however when emailing it, the attachment is the report name, rptQuote and not the caption.

That is because you are not saving the report yourself. You are forcing Access to save it and Access is using the only name it knows which is the query name.

1. No need to open the report first. Use a query with criteria instead. We'll tell you how if you can't figure it out. Opening and closing the report is very inefficient when you are doing this inside a loop.
2. Save the PDF and give it whatever name you want.
3. attach the saved PDF to the email.

 

[Morphies]

hmm, this is highly annoying!

Current "send email button" code is:

Private Sub Command22_Click()
    On Error Resume Next ' Suppress error messages temporarily
    
    ' Open the report in preview mode with a filter based on [QuoteRef]
    DoCmd.OpenReport "rptQuote", acViewPreview, , "[QuoteRef] = '" & Me.[QuoteRef] & "'", acWindowNormal
    
        
    ' Check if the report is open before attempting to send the email
    If CurrentData.AllReports("rptQuote").IsLoaded Then
        ' Extract the first word from the field Rep_ID to use as the recipient's first name
        Dim recipientFirstName As String
        recipientFirstName = StrConv(Split(Me.[Rep_ID], " ")(0), vbProperCase) ' Convert to proper case
        
        ' Construct a more personalized email body
        Dim emailBody As String
        emailBody = "Dear " & recipientFirstName & "," & vbCrLf & vbCrLf & _
                    "I trust this email finds you well. Attached, please find the quotation against your reference No: " & Me.[CustomerRef] & "." & vbCrLf & vbCrLf & _
                    "Please review the attached document, and do not hesitate to contact us if you have any questions or require further clarification." & vbCrLf & vbCrLf & _
                    "For any orders that may arise from this quotation, kindly send them to orders@clarityndt.co.uk." & vbCrLf & vbCrLf & _
                    "Thank you for considering Clarity NDT for your requirements." & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & _
                    "Clarity NDT Limited"
        
        ' Send email with the report attached as a PDF and the personalized email body
        DoCmd.SendObject acSendReport, "rptQuote", acFormatPDF, , "", "", _
            "Quotation against your reference No: " & Me.[CustomerRef], emailBody, True
                
        ' Close the report after sending the email
        DoCmd.Close acReport, "rptQuote"
    Else
        MsgBox "The report 'rptQuote' is not open.", vbExclamation
    End If
    
    On Error GoTo 0 ' Reset error handling to default behavior
End Sub

Report rptQuote onload code is now:

Private Sub Report_Load()
Me.Caption = Me.[Customer ID] & "-" & Me.QuoteRef

End Sub

Still calls the attachment rptQuote

Not sure if this makes a difference, but the report is actually called RPTQuote and not rptQuote (note capitalisation) yet the attachment is using the capitalisation in the email button code above..

 

[Pat Hartman]

Maybe you should read my answer again.