Results 1 to 8 of 8
  1. #1
    DaleG is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    10

    Save Report and Email Report

    I am sure this is a very elementary question. I am not a programmer. I am muddling my way through some stuff to make my job easier. Long story - we "transitioned" to SalesForce, and it won't work will all of our stuff. So we are still in the old system as well. I manually do excel reports daily because some information is in 1 place, some in the other. I decided to create reports in SalesForce and reports in the other application. I can refresh the reports and export to excel files that are linked to an access database. I have created several of my routine reports using queries and reports and such. I have figured out how to have a button email the reports to who they go to. That all works great.



    I have a table that contains some information from the reports and other fields that I enter. This table is used to create maintenance contracts. It works great. I have a form that shows me all of the records of outstanding projects, who the service provider will be, install date, etc. - some info comes from a linked table through a series of queries, some is information I have stored. Each record in the form has a button that when I push it, creates a report with the information specific to that record, using a primary key to make sure it only gives me items, pricing, customer, etc that is related to that particular customer sale/ imp[lementation record.

    Currently what I do then is to print it to a pdf, then mail it to the service provider.

    I have found how to place a button on the report itself and it will save it to a pdf for me, complete with a pre-configured file name based on information in the report (i.e . me.CustName & me.ServiceProv & me.ImplementationNum ).

    I have my other standard reports that I email automatically, but it wouldn't really work the way I know how because it relies on just the report as it is, whereas this report changes based on the ImplementationNum of the record in the driving form.

    What I want to do is to save the report and email it, so that I have a retained copy of it in a specified folder rather than just in my sent mail.

    (disclaimer - I didn't write this, I found it on the internet and modified a little to make it work for me. I am not this smart.... so be kind... )

    Code:
    Function FileExist(FileFullPath As String) As Boolean
      Dim value As Boolean
      value = False
      If Dir(FileFullPath) <> "" Then
        value = True
      End If
      FileExist = value
    End Function
    
    Private Sub SendMO_Click()
    
    
      Dim fileName As String, fldrPath As String, filePath As String
      Dim answer As Integer
    
      fileName = Me.CustomerName & " - " & Me.SystemNum & " - " & Me.ServiceProvider & " - " & Me.ImplementationNum
    
    
      'filename for PDF file*?:
      fldrPath = "C:\Users\daleg\Documents\3pContracts" 'folder path where pdf file will be saved *
    
      filePath = fldrPath & "\" & fileName & ".pdf"
    
      'check if file already exists
      If FileExist(filePath) Then
        answer = MsgBox(prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
          "Would you like to replace existing file?", buttons:=vbYesNo, Title:="Existing PDF File")
        If answer = vbNo Then Exit Sub
      End If
    
    On Error GoTo invalidFolderPath
      DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=filePath
    
      MsgBox prompt:="PDF File exported to: " & vbNewLine & filePath, buttons:=vbInformation, Title:="Report Exported as PDF"
      Exit Sub
    
    invalidFolderPath:
      MsgBox prompt:="Error: Invalid folder path. Please update code.", buttons:=vbCritical
    
    
    
    End Sub
    
    This is what I am using to email the other static reports:

    Code:
    Private EmailRMAReport_Click()
    On Error GoTo ErrorHandler
    Me.Dirty = False
    
    
    
    
    Dim sExistingReportName As String
        Dim sAttachmentName As String
     
        'Input variables
        sExistingReportName = "RMA Select 3p Report"    'Name of the Access report Object to send
        sAttachmentName = "RMA's Still in Field -" & Date     'Name to be used for the attachment in the e-mail
     
        'The code to make it happen
        DoCmd.openReport sExistingReportName, acViewPreview, , , acHidden
        Reports(sExistingReportName).Caption = sAttachmentName    'by changing the report caption
                                                            'you effectively change the name
                                                            'used for the attachment in the
                                                            '.SendObject method
       DoCmd.Save
                                                            
        DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, _
        , , , "RMAs Still In Field Report", _
        "Attached is the current rma report.  Please let me know if you have any questions." & Chr(10) & Chr(10) & Chr(10) & "Thank you," & Chr(10)  & "The information contained in this correspondence and in the attachments is confidential and intended for the exclusive use of the individuals named above. Unauthorized reproduction and/or distribution is prohibited.", , False
    
    
                                         
                         
        If Err.Number = 2501 Then
        
        Exit Sub
        
        Else
                         
        DoCmd.Close acReport, sExistingReportName
        
    Cleanup:
      Exit Sub
    
    
    ErrorHandler:
      Select Case Err.Number
        Case 2501
          Resume Next
        Case Else
          MsgBox Err.Number & ": " & Err.Description
      End Select
      Resume Cleanup
        
        End If
    End Sub


    How can I save AND send the dynamic report generated from the from data?
    Last edited by DaleG; 02-25-2020 at 09:06 AM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Use OutputTo method to save a PDF.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DaleG is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    10
    Quote Originally Posted by June7 View Post
    Use OutputTo method to save a PDF.

    Thank you, but creating a pdf isn't my problem. I want to create/save a pdf AND email it. I can do either one of these actions, I am just having a hard time making them both happen together.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I use Outlook automation. See if this gets you going:

    https://www.devhut.net/2010/09/03/vb...ok-automation/

    you'd pass the function the path to the file just created.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    DaleG is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    I use Outlook automation. See if this gets you going:

    https://www.devhut.net/2010/09/03/vb...ok-automation/

    you'd pass the function the path to the file just created.
    I think I just went a little cross-eyed.

    Thanks for the help. I played around with it and was able to get it working. Here is what I did :

    On my driving form, I added a text box. That text box source is a concatenated field of customer name, system number, service provider, and implementation number for the particular record I am in and named that text box FileName.

    Then when I click to create the report from that form data, during the Open event I added Me.Caption = Forms!frmmainorder!FileName. ( I learned that if you let access name the file, its going to name it the caption - so this makes everyone I do a unique file.)

    I kept my code for emailing the record the same, but before the docmd.close, I added another docmd.outputto - DoCmd.OutputTo acOutputReport, "3pMaintOrder", acFormatPDF, "C:\Users\daleg\Documents\3pContracts" & Forms!frmmainorder!FileName & ".pdf", True.

    Since I am using the FileName from the driving form for naming both the email attachment and the saved pdf, i don't have to worry about saving the exact pdf I email. I just let it go ahead and write to the temp file and use the output to do a second file to my save directory.

    May not be the pretty way and definitely not the smart way, but it works.

    One day maybe I will get the hang of this stuff.

    Thanks again for the help!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you really want to specify name of email attachment, then use Outlook automation. But why would you need to use temp file? Run OutputTo to save PDF to desired folder then use that file path in the email attachment.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    DaleG is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    10
    Quote Originally Posted by June7 View Post
    If you really want to specify name of email attachment, then use Outlook automation. But why would you need to use temp file? Run OutputTo to save PDF to desired folder then use that file path in the email attachment.
    I don't need to use temp file. I just read that when you use the sendobject command, the file created is stored as temp file until the email is sent. I don't think I explain myself very well - especially on this stuff I am just muddling through. My goal was not to create an email with a specific filename, or create a saved pdf with a specific filename - but to do both with 1 button. One "send & save" action where the save file was put in a specific place for reference. Being able to base the name on concatenated fields was just because each time it creates the report/pdf, its specific to a project. So having it just be the same name every time didn't work well for document retention.

    The whole thing spiraled me down a rabbit hole where I didn't know which came first - the chicken or the egg. Do I email it and save the attachment, or do I save the attachment and then email it? The way I stumbled on to a working methodology has me doing it as emailing a document, then saving a document. Clumsy, but it works, and I don't understand this stuff well enough right now to redo it. Maybe later down the road. This is just for me anyway, just to make a very manual process a little easier and less manual. No one will ever need to use it but me.


    Thanks again!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So you basically used what I suggested.

    OpenForm

    OutputTo

    SendObject

    Close

    With this approach, it doesn't matter if OutputTo is before or after SendObject.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-15-2015, 08:35 PM
  2. Replies: 3
    Last Post: 06-23-2015, 12:23 PM
  3. Replies: 2
    Last Post: 04-21-2015, 11:33 AM
  4. Replies: 1
    Last Post: 06-26-2012, 08:19 PM
  5. Replies: 6
    Last Post: 12-12-2011, 09:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums