VBA output report to PDF and email (1 Viewer)

M

Morphies

Member
Local time
Today, 10:12
Joined
Dec 8, 2021
Messages
34
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:

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?
 
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
 
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
 
That will be the report caption?
Plus when you send the report, you do not have any filter on it?
 
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:

Code: 
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"
 
Last edited:
Morphies said:
Your diamond. FUR001-13545 was in the report caption

The query that builds the report is filtering on: [Forms]![frmQuote]![QuoteRef]
Click to expand...
So why the need of
Code: 
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.
 
Last edited:
I cant answer that :LOL:. I have insufficient knowledge :(

The report isn't always open, it's launched as required from the form controls.
 
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.
 
olxx said:
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.
Click to expand...
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.
 
Try moving the line to change the caption below the line that opens the report.
 
Gasman said:
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.
Click to expand...
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.
 
I cannot get this to output with the filename how I want it, I've even tried moving to saving then attaching
theDBguy said:
Try moving the line to change the caption below the line that opens the report.
Click to expand...

like this?
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
    
    
    ' 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
 
Morphies said:
I cannot get this to output with the filename how I want it, I've even tried moving to saving then attaching


like this?
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
  
  
    ' 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
Click to expand...
Why not just set the caption of the report to those values in the report and be done with it?
 
Oddly enough I already have the following in the onload event for the rptQuote report:

Code: 
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.
 
Works for me?
1706890051783.png

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

Try using Me.
 
Morphies said:
When I preview the report, the caption is correct, however when emailing it, the attachment is the report name, rptQuote and not the caption.
Click to expand...
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.
 
hmm, this is highly annoying!

Current "send email button" code is:
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

Report rptQuote onload code is now:

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

End Sub

Still calls the attachment rptQuote :LOL:

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..
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
361
oxicottin
oxicottin
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
830
The_Doc_Man
The_Doc_Man
N
Solved I want to be able to send a few emails in access 2016 using gmail but am having challenges
Replies
7
Views
273
nector
N
C
Invalid Use of Null
2
Replies
20
Views
1,257
chuckcoleman
C
N
Solved How to remove this error "the execution this software stopped due to run time" error in MS Access VBA
Replies
11
Views
483
Minty
Minty

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom