Slight change to convert report to pdf and email needed

[Manc]

Slight change to convert report to pdf and email needed - SOLVED

Dear all

I found this code on another thread and have modified it very slightly

Public Sub SendEmail()
 
    Dim appOutLook As Object
    Dim MailOutLook As Object
 
 
    DoCmd.OutputTo acOutputReport, "rptQuote", acFormatPDF, "c:\Test\Quotation.pdf", False
 
 
    'assign our object references
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
    With MailOutLook
        'set the recipient list
        .To = "someone@somewhere.com"
 
        'set the subject
        .Subject = "My Reports"
 
        'set the body text
        .body = "Here are the reports in pdf format"
 
        'add the reports we created
        .attachments.Add "c:\Test\Quotation.pdf"
 
        .Display
    End With
 
    'tidy up..
 
    'get rid of our object references
    Set appOutLook = Nothing
    Set MailOutLook = Nothing
 
    'delete our temporary files
    Kill "c:\Test\Quotation.pdf"
 
End Sub

Whilst it works great I need to modify it further by doing the following:

1. Adding

[Forms]![frmQuote]![QuoteNo]=[qryQuote]![QuoteNo]

somewhere so it converts and attaches only the record I am currently viewing to the email. I'm guessing it goes on the DoCmd line but not sure where or how.

Any suggestions greatly anticipated
King regards
manc

 

[penguino29]

Hi Manc,

The code you quoted,

DoCmd.OutputTo acOutputReport, "rptQuote", acFormatPDF, "c:\Test\Quotation.pdf", False

opens a report "rptQuote".

Can I assume that this report is based on a query to draw its data from?

Also can I assume that somewhere in that query, there should be a field [QuoteNo]?


If so, then you should open and edit the report, edit it's underlying query, and in the [QuoteNo] column add the following as criteria:

[Forms]![frmQuote]![QuoteNo]

So the report's data is restricted to the Form's Quote number.

HINT: You might like to copy the report rptQuote and its underlying query and modify the copy instead of the original. This way you have a separate report "rptQuotePDF" and "qryQuotePDF" which are specifically used for the Form based PDF generation, while your original rptQuote could be a general purpose report.

 

[Manc]

Hi penguino29

Thanks for your response.

The problem I had was not the actual restricting of the data to display only the record I was viewing, but a problem with the actual code. There didn't seem to be an expression to restrict the data and I found that the report was converting all the records to .pdf.

However, I found my solution within a macro. Here's what I've used:

The macro starts by displaying the report in Print Preview, filtered as an individual record. It then converts it to .pdf and attaches it to an email.

On my form I have a client contact combox box attached to a query that lists the contacts that work for the chosen company. The query lists contactID, clientcontactID, first name, report name (formal name i.e. Mr Smith) and email address.
Two invisible text boxes on the form are then populated with the first name and email address of the contact chosen in the combobox using the afterupdate() on the combox box.

Code used is

Private Sub Nameofcombobox_AfterUpdate()
    
    Me.NameofTextbox1 = Me.Nameofcombobox.Column(3)
    Me.NameofTextbox2 = Me.Nameofcombobox.Column(4)
End Sub

Column(3) in my query is contact first name
Column(4) is my email address

NameofTextbox1 and 2 were also added as fields to my table so the data was stored.

Here is the macro

Open Report
Report Name : rptQuote
View : Print Preview
Filter Name :
Where Condition: = [Forms]![frmQuote]![ClientID] = [tblQuote]![ClientID]
Window Mode : Normal
 
EMailDatabaseObject
Object Type : Report
Object Name : rptQuote
Output Format: PDF Format (*.pdf)
To : =[QuoteContactEmail]
Cc :
Bcc :
Subject : ="Quotation to " & [QuoteDestination]
Message Text : = "Dear" & [QuoteContactFirstName] & Chr(13) & Chr (10) & Chr (13) & Chr (10) & "Please find attached your quotation as requested."
Edit Message : Yes
Template File :

I realise this may not be the best way of doing it, but so far it works for me and if it helps anyone else then albeit it.

Kind regards
manc