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?