Link to home
Start Free TrialLog in
Avatar of Tiffany Foutz
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 PrintReportAsPDFwithBullZip(NameofReport, , "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 PrintReportAsPDFwithBullZip(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.PDFPrinterSettings")   '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
    PrintReportAsPDFwithBullZip = True

    If sDirectory = "" Then
        sDirectory = CurrentProject.Path & "\"
    Else
        sDirectory = sDirectory
    End If

    If sFileName = "" Then
        sFileName = Split(CurrentProject.Path, "\")(UBound(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.DeviceName, "BullZip") = 0 Then    ' If BullZip isn't the default printer
        blnPrinterChanged = True                                    ' Set the printer changed flag to true
        strDefaultPrinter = Application.Printer.DeviceName          ' 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:
    PrintReportAsPDFwithBullZip = 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.Application")
    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\Documents\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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

You could change this:

DoCmd.OpenReport "MemberDetailsReportAll", acViewNormal, , sFilterCriteria

to:

DoCmd.OpenReport rptName , acViewNormal, , sFilterCriteria

as you are already passing the report name into the procedure.

Jim.
Avatar of Tiffany Foutz
Tiffany Foutz

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.Application")
    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\Documents\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.
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.
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 PrintReportAsPDFwithBullZip(NameofReport, , "C:\Users\tfoutz\Documents\RS Member Log\", "ReportAllByName.pdf")
   
   Call SendPDFbyEmail("ReportAllByName.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.Application")
    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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much to Jim Dettman and MacroShadow.   Very helpful and much appreciated.  My simple DB is now working!  :)