none
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. RRS feed

  • 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.


    Thursday, April 13, 2017 5:28 PM

Answers

  • 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...

    • Marked as answer by Ash247247 Saturday, April 15, 2017 6:15 AM
    Thursday, April 13, 2017 10:09 PM

All replies

  • 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...

    Thursday, April 13, 2017 5:42 PM
  • 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

    Thursday, April 13, 2017 5:51 PM
  • 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...

    Thursday, April 13, 2017 5:59 PM
  • 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?



    • Edited by Ash247247 Thursday, April 13, 2017 10:04 PM
    Thursday, April 13, 2017 9:51 PM
  • 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...

    • Marked as answer by Ash247247 Saturday, April 15, 2017 6:15 AM
    Thursday, April 13, 2017 10:09 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, April 14, 2017 8:02 AM
    Moderator
  • 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.

    Friday, April 14, 2017 9:36 AM
  • 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.

    Saturday, April 15, 2017 6:16 AM
  • Hi Ash,

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

    Saturday, April 15, 2017 5:45 PM