gojets1721
Registered User.
- Local time
- Today, 06:04
- Joined
- Jun 11, 2019
- Messages
- 430
Full disclosure...I'm very new to VBA so bear with me.
I have 3 commands on a report that quickly allows the user to export the report to pdf, email the report, or print it. It works great. Here's my exact code:
The strFileName and strSubject lines are what I need help with. With exporting and emailing, I have to specify a file name, subject line, etc in the VBA. These are always the same name across the board. I was hoping I could just declare this name once and then reference it throughout the VBA. That way, if i want to change it, I don't have to change it in three different places. I'm not sure how to do that though across three private subs
Any suggestions?
I have 3 commands on a report that quickly allows the user to export the report to pdf, email the report, or print it. It works great. Here's my exact code:
Code:
Private Sub btnExportReport_Click()
On Error GoTo btnExportReport_Click_Err
Dim objFileDialog As Object
Dim strFileName As String
Set objFileDialog = Application.FileDialog(2)
strFileName = "Complaint Report" & ".pdf"
With objFileDialog
.Title = "Save to PDF"
.InitialFileName = "\Documents\" & strFileName
If .Show = -1 Then
strFileName = objFileDialog.SelectedItems(1)
If Right(strFileName, 4) <> ".pdf" Then
strFileName = strFileName & ".pdf"
End If
DoCmd.OutputTo acOutputReport, Me.Name, acFormatPDF, strFileName
MsgBox "Report saved to " & strFileName
End If
End With
btnExportReport_Click_Exit:
Set objFileDialog = Nothing
Exit Sub
btnExportReport_Click_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description, vbExclamation, "Error Message"
Resume btnExportReport_Click_Exit
End Sub
Private Sub btnEmailReport_Click()
On Error GoTo btnEmailReport_Click_Err
Dim strReportName As String
Dim strCriteria As String
Dim strFileName As String
Dim strSubject As String
Dim strMessage As String
strReportName = Me.Name
strCriteria = ""
strFileName = "Complaint Report" & ".pdf"
strSubject = "Complaint Report"
strMessage = "Please see the attached complaint."
Call EmailReport(strReportName, strCriteria, strFileName, strSubject, strMessage)
btnEmailReport_Click_Exit:
Exit Sub
btnEmailReport_Click_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "Error Message"
Resume btnEmailReport_Click_Exit
End Sub
Private Sub btnPrintReport_Click()
On Error GoTo btnPrintReport_Click_Error
DoCmd.RunCommand acCmdPrint
btnPrintReport_Click_Exit:
Exit Sub
btnPrintReport_Click_Error:
MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "Error Message"
Resume btnPrintReport_Click_Exit
End Sub
The strFileName and strSubject lines are what I need help with. With exporting and emailing, I have to specify a file name, subject line, etc in the VBA. These are always the same name across the board. I was hoping I could just declare this name once and then reference it throughout the VBA. That way, if i want to change it, I don't have to change it in three different places. I'm not sure how to do that though across three private subs
Any suggestions?