Tiffany Foutz
asked on
Help Passing Report Name as a Variable to Functions
I have a small Access that is utilizing MacroShadow's code for a button that saves a report to pdf with a password using Bullzip and even emails it. Huge thanks to MacroShadow for helping me to get this working.
Only one small problem left. I have 6 different reports each with their own button. I want to call the same Function regardless of which report button is clicked. I can't figure out how to pass the report name to the Function as my variable (currently hard coded report name and paths).
Can anyone help? Thank you in advance.
Here is the code.
REPORT BUTTON
Option Explicit
Private Sub EmailPDFBtn_Click()
Dim NameofReport As String
NameofReport = "MemberDetailsReportAll"
Call PrintReportAsPDFwithBullZi p(NameofRe port, , "C:\Users\tfoutz\Documents \RS Member Log\", "ReportAllByName.pdf")
Call SendPDFbyEmail
End Sub
SAVEPDFENCRYPTED MODULE
Option Explicit
Public Declare Function SetDefaultPrinter Lib "winspool.drv" _
Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
Function PrintReportAsPDFwithBullZi p(ByVal rptName As String, _
Optional sFilterCriteria As String = "", _
Optional sDirectory As String = "", _
Optional sFileName As String = "") _
As Boolean
On Error GoTo err_Error
Dim oBullzipPDF As Object, oBullzipUtil As Object
Dim strSavePath As String, strFileName As String
Dim strDefaultPrinter As String
Dim blnPrinterChanged As Boolean
Set oBullzipPDF = CreateObject("Bullzip.PDFP rinterSett ings") 'Initialize the PDF class
'set the success flag to true here but it will be set to
'false if the function fails at any point
PrintReportAsPDFwithBullZi p = True
If sDirectory = "" Then
sDirectory = CurrentProject.Path & "\"
Else
sDirectory = sDirectory
End If
If sFileName = "" Then
sFileName = Split(CurrentProject.Path, "\")(UBound(Split(CurrentP roject.Pat h, "\")))
Else
sFileName = sFileName
End If
If LCase(Right(sFileName, 4)) <> ".pdf" Then
sFileName = sFileName & ".pdf"
End If
With oBullzipPDF
.Init
.SetValue "Output", sDirectory & sFileName
.SetValue "ShowSettings", "never"
'''''''''''''''''''''''''' '''''''''' '''''''''' ''''''
' Here you have to set the desired security settings
'''''''''''''''''''''''''' '''''''''' '''''''''' ''''''
.SetValue "OwnerPassword", "123"
.SetValue "UserPassword", "123"
.SetValue "EncryptionType", "Standard128bit" ' AES 128 bit and AES 256 bit encryption are supported but you must purchase a license to use it
.SetValue "AllowAssembly", "True"
.SetValue "AllowCopy", "True"
.SetValue "AllowDegradedPrinting", "True"
.SetValue "AllowFillIn", "True"
.SetValue "AllowModifyAnnotations", "True"
.SetValue "AllowModifyContents", "True"
.SetValue "AllowPrinting", "True"
.SetValue "AllowScreenReaders", "True"
'''''''''''''''''''''''''' '''''''''' '''''''''' ''''''
' End of security settings
'''''''''''''''''''''''''' '''''''''' '''''''''' ''''''
.SetValue "ShowPDF", "no"
.SetValue "ConfirmOverwrite", "no"
.SetValue "SuppressErrors", "yes"
.SetValue "ShowProgress", "no"
.SetValue "ShowProgressFinished", "no"
.SetValue "Author", "Me"
.SetValue "Title", "My File"
.SetValue "Subject", "My Subject"
.WriteSettings (True) 'writes the settings in a runonce.ini that is immediately deleted after being used.
End With
If InStr(Application.Printer. DeviceName , "BullZip") = 0 Then ' If BullZip isn't the default printer
blnPrinterChanged = True ' Set the printer changed flag to true
strDefaultPrinter = Application.Printer.Device Name ' Save name of current printer
SetDefaultPrinter "Bullzip PDF Printer" ' Use API to set the Current printer to Bullzip
End If
DoEvents
DoCmd.OpenReport "MemberDetailsReportAll", acViewNormal, , sFilterCriteria
DoEvents
If blnPrinterChanged Then SetDefaultPrinter strDefaultPrinter
'error handler and exit
err_Exit:
Set oBullzipPDF = Nothing
Exit Function
err_Error:
PrintReportAsPDFwithBullZi p = False
MsgBox Err.Description
Resume err_Exit
Resume
End Function
SENDEMAIL MODULE
Option Explicit
Function SendPDFbyEmail()
Dim OutApp As Object
Dim OutMail As Object
'-- Standard Email Variables
Dim Variable_To As String
Dim Variable_Subject As String
Dim Variable_Body As String
'-- The e-mail address to where the ticket will be sent.
Variable_To = "tfoutz@gmail.com"
'-- The Subject of the email
Variable_Subject = "RS Member Info Log"
'-- The Body of the email
Variable_Body = "Attached is your RS Member Info Log Report."
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Variable_To
.CC = ""
.BCC = ""
.Subject = Variable_Subject
.Body = Variable_Body
.Attachments.Add ("C:\Users\tfoutz\Document s\RS Member Log\ReportAllByName.pdf")
.Display 'or use .Send
.ReadReceiptRequested = False
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
Only one small problem left. I have 6 different reports each with their own button. I want to call the same Function regardless of which report button is clicked. I can't figure out how to pass the report name to the Function as my variable (currently hard coded report name and paths).
Can anyone help? Thank you in advance.
Here is the code.
REPORT BUTTON
Option Explicit
Private Sub EmailPDFBtn_Click()
Dim NameofReport As String
NameofReport = "MemberDetailsReportAll"
Call PrintReportAsPDFwithBullZi
Call SendPDFbyEmail
End Sub
SAVEPDFENCRYPTED MODULE
Option Explicit
Public Declare Function SetDefaultPrinter Lib "winspool.drv" _
Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
Function PrintReportAsPDFwithBullZi
Optional sFilterCriteria As String = "", _
Optional sDirectory As String = "", _
Optional sFileName As String = "") _
As Boolean
On Error GoTo err_Error
Dim oBullzipPDF As Object, oBullzipUtil As Object
Dim strSavePath As String, strFileName As String
Dim strDefaultPrinter As String
Dim blnPrinterChanged As Boolean
Set oBullzipPDF = CreateObject("Bullzip.PDFP
'set the success flag to true here but it will be set to
'false if the function fails at any point
PrintReportAsPDFwithBullZi
If sDirectory = "" Then
sDirectory = CurrentProject.Path & "\"
Else
sDirectory = sDirectory
End If
If sFileName = "" Then
sFileName = Split(CurrentProject.Path,
Else
sFileName = sFileName
End If
If LCase(Right(sFileName, 4)) <> ".pdf" Then
sFileName = sFileName & ".pdf"
End If
With oBullzipPDF
.Init
.SetValue "Output", sDirectory & sFileName
.SetValue "ShowSettings", "never"
''''''''''''''''''''''''''
' Here you have to set the desired security settings
''''''''''''''''''''''''''
.SetValue "OwnerPassword", "123"
.SetValue "UserPassword", "123"
.SetValue "EncryptionType", "Standard128bit" ' AES 128 bit and AES 256 bit encryption are supported but you must purchase a license to use it
.SetValue "AllowAssembly", "True"
.SetValue "AllowCopy", "True"
.SetValue "AllowDegradedPrinting", "True"
.SetValue "AllowFillIn", "True"
.SetValue "AllowModifyAnnotations", "True"
.SetValue "AllowModifyContents", "True"
.SetValue "AllowPrinting", "True"
.SetValue "AllowScreenReaders", "True"
''''''''''''''''''''''''''
' End of security settings
''''''''''''''''''''''''''
.SetValue "ShowPDF", "no"
.SetValue "ConfirmOverwrite", "no"
.SetValue "SuppressErrors", "yes"
.SetValue "ShowProgress", "no"
.SetValue "ShowProgressFinished", "no"
.SetValue "Author", "Me"
.SetValue "Title", "My File"
.SetValue "Subject", "My Subject"
.WriteSettings (True) 'writes the settings in a runonce.ini that is immediately deleted after being used.
End With
If InStr(Application.Printer.
blnPrinterChanged = True ' Set the printer changed flag to true
strDefaultPrinter = Application.Printer.Device
SetDefaultPrinter "Bullzip PDF Printer" ' Use API to set the Current printer to Bullzip
End If
DoEvents
DoCmd.OpenReport "MemberDetailsReportAll", acViewNormal, , sFilterCriteria
DoEvents
If blnPrinterChanged Then SetDefaultPrinter strDefaultPrinter
'error handler and exit
err_Exit:
Set oBullzipPDF = Nothing
Exit Function
err_Error:
PrintReportAsPDFwithBullZi
MsgBox Err.Description
Resume err_Exit
Resume
End Function
SENDEMAIL MODULE
Option Explicit
Function SendPDFbyEmail()
Dim OutApp As Object
Dim OutMail As Object
'-- Standard Email Variables
Dim Variable_To As String
Dim Variable_Subject As String
Dim Variable_Body As String
'-- The e-mail address to where the ticket will be sent.
Variable_To = "tfoutz@gmail.com"
'-- The Subject of the email
Variable_Subject = "RS Member Info Log"
'-- The Body of the email
Variable_Body = "Attached is your RS Member Info Log Report."
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Variable_To
.CC = ""
.BCC = ""
.Subject = Variable_Subject
.Body = Variable_Body
.Attachments.Add ("C:\Users\tfoutz\Document
.Display 'or use .Send
.ReadReceiptRequested = False
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
ASKER
Thank you Jim. That works great for the Save piece.
Can you also tell me how to get it to attach the correct file in the email Function (notice the actual report name is not the same as the saved file created in the SavetoPDF procedure). I can make the save filed and report names identical if necessary to work.
SENDEMAIL MODULE
Option Explicit
Function SendPDFbyEmail()
Dim OutApp As Object
Dim OutMail As Object
'-- Standard Email Variables
Dim Variable_To As String
Dim Variable_Subject As String
Dim Variable_Body As String
'-- The e-mail address to where the ticket will be sent.
Variable_To = "tfoutz@gmail.com"
'-- The Subject of the email
Variable_Subject = "RS Member Info Log"
'-- The Body of the email
Variable_Body = "Attached is your RS Member Info Log Report."
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Variable_To
.CC = ""
.BCC = ""
.Subject = Variable_Subject
.Body = Variable_Body
.Attachments.Add ("C:\Users\tfoutz\Document s\RS Member Log\ReportAllByName.pdf")
.Display 'or use .Send
.ReadReceiptRequested = False
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
Thank you.
Can you also tell me how to get it to attach the correct file in the email Function (notice the actual report name is not the same as the saved file created in the SavetoPDF procedure). I can make the save filed and report names identical if necessary to work.
SENDEMAIL MODULE
Option Explicit
Function SendPDFbyEmail()
Dim OutApp As Object
Dim OutMail As Object
'-- Standard Email Variables
Dim Variable_To As String
Dim Variable_Subject As String
Dim Variable_Body As String
'-- The e-mail address to where the ticket will be sent.
Variable_To = "tfoutz@gmail.com"
'-- The Subject of the email
Variable_Subject = "RS Member Info Log"
'-- The Body of the email
Variable_Body = "Attached is your RS Member Info Log Report."
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Variable_To
.CC = ""
.BCC = ""
.Subject = Variable_Subject
.Body = Variable_Body
.Attachments.Add ("C:\Users\tfoutz\Document
.Display 'or use .Send
.ReadReceiptRequested = False
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
Thank you.
You would change this:
Function SendPDFbyEmail()
to
Function SendPDFbyEmail(strSaveFile as string)
and then:
.Attachments.Add (strSaveFile)
and then call SendPDFbyEmail() with the name as a string (in quotes).
Jim.
Function SendPDFbyEmail()
to
Function SendPDFbyEmail(strSaveFile
and then:
.Attachments.Add (strSaveFile)
and then call SendPDFbyEmail() with the name as a string (in quotes).
Jim.
ASKER
Thanks Jim. It does save the file but is not bringing up the email like it does when hard coded?
Here is the full and updated Code
REPORT BUTTON CODE
Private Sub EmailPDFBtn_Click()
Dim NameofReport As String
NameofReport = "MemberDetailsReportAll"
Call PrintReportAsPDFwithBullZi p(NameofRe port, , "C:\Users\tfoutz\Documents \RS Member Log\", "ReportAllByName.pdf")
Call SendPDFbyEmail("ReportAllB yName.pdf" )
End Sub
SENDEMAIL MODULE
Option Explicit
Function SendPDFbyEmail(strSaveFile As String)
Dim OutApp As Object
Dim OutMail As Object
'-- Standard Email Variables
Dim Variable_To As String
Dim Variable_Subject As String
Dim Variable_Body As String
'-- The e-mail address to where the ticket will be sent.
Variable_To = "tfoutz@gmail.com"
'-- The Subject of the email
Variable_Subject = "RS Member Info Log"
'-- The Body of the email
Variable_Body = "Attached is your RS Member Info Log Report."
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Variable_To
.CC = ""
.BCC = ""
.Subject = Variable_Subject
.Body = Variable_Body
.Attachments.Add (strSaveFile)
.Display 'or use .Send
.ReadReceiptRequested = False
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
Thanks for your help.
Here is the full and updated Code
REPORT BUTTON CODE
Private Sub EmailPDFBtn_Click()
Dim NameofReport As String
NameofReport = "MemberDetailsReportAll"
Call PrintReportAsPDFwithBullZi
Call SendPDFbyEmail("ReportAllB
End Sub
SENDEMAIL MODULE
Option Explicit
Function SendPDFbyEmail(strSaveFile
Dim OutApp As Object
Dim OutMail As Object
'-- Standard Email Variables
Dim Variable_To As String
Dim Variable_Subject As String
Dim Variable_Body As String
'-- The e-mail address to where the ticket will be sent.
Variable_To = "tfoutz@gmail.com"
'-- The Subject of the email
Variable_Subject = "RS Member Info Log"
'-- The Body of the email
Variable_Body = "Attached is your RS Member Info Log Report."
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Variable_To
.CC = ""
.BCC = ""
.Subject = Variable_Subject
.Body = Variable_Body
.Attachments.Add (strSaveFile)
.Display 'or use .Send
.ReadReceiptRequested = False
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
Thanks for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much to Jim Dettman and MacroShadow. Very helpful and much appreciated. My simple DB is now working! :)
DoCmd.OpenReport "MemberDetailsReportAll", acViewNormal, , sFilterCriteria
to:
DoCmd.OpenReport rptName , acViewNormal, , sFilterCriteria
as you are already passing the report name into the procedure.
Jim.