How to send an PDF Access report in an outlook message and add a field from the form as unique identifier to the report name.

Question

Hello, I've created a database and with the help of this forum been able to add some great functionality however am quite stuck now and hoping someone can help as I've tried for several hours to figure this out myself with no luck.

I can generate a report as a PDF no problem and can also get the outlook dialogue box to open with the PDF already attached. I can also get the receivers name automatically populated from my Email field on the form but I cannot get the PDF to have an individual name.

With the help of a previous post from  a member on this site https://social.msdn.microsoft.com/profile/ken%20sheridan/?ws=usercard-mini I have been able to get a way to automatically save PDF's to a chosen location and saved as Customer and then Invoice Number as the report name but I cannot find any way to extend this functionality so that I can then mail the generated report to my customer. 

Here is the code that Ken had that works beautifully, is there a way to extend this code so that I can append EmailDatabaseObject type functionality as an Event Procedure as the next step once the report is saved?

If not then a way for me to simply EmailDatabaseObject report name & field name.

Here is the code that generates an individually named file and puts it where I've previously chosen:

Private Sub cmdPDF_Click()

  On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current invoice."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varFolder As Variant
    
    If Not IsNull(Me.InvoiceNumber) Then
        ' build path to save PDF file
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
            varFolder = varFolder & "\" & Me.Customer.Column(1)
            MkDir varFolder
            strFullPath = varFolder & "\" & Me.Customer.Column(1) & " " & Me.InvoiceNumber & ".pdf"
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFullPath, True
        End If
    Else
        MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
    End If

Exit_Here:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
        Case FOLDER_EXISTS
        Resume Next
        Case Else
        MsgBox Err.Description
        Resume Exit_Here
    End Select

End Sub

Thank you.

Answer 1

Hi,

Not sure all this code is necessary but if you're using Outlook as your email client, then you could try to use Outlook Automation to attach the PDF file to an email. For example:

Dim olApp As Object
Dim olMail As Object

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)

With olMail
    .Attachments.Add "FullPathToPDF.pdf"
    .Display

End With

Set olMail = Nothing
Set olApp = Nothing

Hope it helps...

Answer 2

Hello, thanks for replying .theDBguy.

Where would I put this code please, I didn't think it was to append to the existing code but I tried anyway just in case.

Many thanks

Answer 3

Hi,

You would use it (please remember, it's just an example and not a complete solution) where you currently have the code for sending the email.

Hope it helps...

Answer 4

Thank you, I'm afraid I couldn't get that to work.

Putting aside the Event Procedure in my initial post. If I have a form (Quote form) with a command button on it to access a report and use the EMailDatabaseObject macro with the Object Type Report, Object Name: ReportName, Output Format: PDF,

is there any New Action I can add here (perhaps using & "") that will allow me to append the field value of 'QuoteNumber' onto the report name?

Answer 5

Well, I don't use macro but there are usually equivalent macro actions for VBA methods. In VBA, I would open the report in Preview mode but hidden (not visible) to the user and then modify the Caption property to whatever value you want to assign (it could be from a field in the current record) for the file's name. Then, you can use the EmailDatabaseObject action against the same report to have the correct name you want. After the email is sent, you can close the report, using whatever macro action is used for it.

Hope it helps...

Answer 6

Hi Ash247247,

I don't know how you try to implement the code suggested by .theDBguy.

but you can directly add that code where you used "DoCmd.OutputTo acOutputReport" method in your code.

then you can set the desire name from the form.

you can also try to create one function and then try to call that function at that same place.

did you get any error when you try to implement that code in your code?

if you did not try that then you can try to test that.

I think that it is the simplest solution for your issue.

then if you get any error then we can try to suggest you further to solve the issue.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

Ah thanks Deepak, so close now putting the code where you suggest.

As the file path is variable though it's not quite right. The code in my initial post creates a new folder based on the Company Name and adds any PDF quotes that are created for the company directly into the same folder this means the file path then becomes variable. I'll play around with it and see if I can get it to work.

Answer 8

Thank you .theDBguy, though I'll try when possible to use Event Procedures in this case setting the macro up in this way has done the job.

Thank you for your guidance.

Answer 9

Hi Ash,

You're welcome. Glad to hear you got it to work using macros. Good luck with your project.