Link to home
Start Free TrialLog in
Avatar of Davisro
DavisroFlag for United States of America

asked on

Output a Word Mailmerge as password protected PDF's with VBA and Bullzip

I have a distribution list in Excel linked to a Word mailmerge document, and need to save 500 merged letters as Password Protected PDF's.

I have VBA code that is supposed to accomplish the task but its not working. Does anyone have a suggestion to how to get it done? If I have to, I can output the PDF's and manually add the password.

Below is the code I have...

Word This Document Module
Dim WithEvents wdapp As Application
Dim bCustomProcessing As Boolean

'Sub MergeDocs()
Private Sub Document_Open()

Set wdapp = Application
bCustomProcessing = False
ThisDocument.MailMerge.DataSource.ActiveRecord = 1
ThisDocument.MailMerge.ShowWizard 1
With ActiveDocument.MailMerge
   If .MainDocumentType = wdFormLetters Then
       .ShowSendToCustom = "Custom Letter Processing"
   End If
End With

End Sub
Private Sub wdapp_MailMergeWizardSendToCustom(ByVal Doc As Document)

bCustomProcessing = True
Doc.MailMerge.Destination = wdSendToNewDocument
With Doc.MailMerge
    For rec = 1 To .DataSource.RecordCount
        .DataSource.ActiveRecord = rec
        .DataSource.FirstRecord = rec
        .DataSource.LastRecord = rec
        .Execute
    Next
End With

MsgBox "Merge Finished"
End Sub

Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
Dim sFilePath As String, sFullPath As String
sFilePath = "C:\Temp"
If bCustomProcessing = True Then
    With Doc.MailMerge.DataSource.DataFields
        sFirmPathName = .Item(3).Value  
    End With
    sFullPath = sFilePath & sFirmPathName
    DocResult.SaveAs sFullPath & ".docx", wdFormatXMLDocument
    DocResult.Close False
    Call PrintReportAsPDFwithBullZip(sFirmPathName)
End If
End Sub

Open in new window

In Module 1
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 = ActiveDocument.Path & "\"
    Else
        sDirectory = sDirectory
    End If

    If sFileName = "" Then
        sFileName = Split(ActiveDocument.Path, "\")(UBound(Split(ActiveDocument.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 rptName, 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

Open in new window

Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

What actually happens?  There is quite a lot of sophisticated code to analyse by eye alone.

Bullzip is not universally known, so I, and most others, would have to install it. We would also have to set up a datasource and a main document. This in order to reproduce the situation so that the problem can be defined.
Avatar of Davisro

ASKER

It breaks with an Invalid Reference at DoCmd.OpenReport rptName, acViewNormal, , sFilterCriteria
I realize now that I probably haven't dimensioned rptName,

But, i'm not married to this solution. If there's another method or other code, I'm happy to hear about it. My understanding is that the Adobe printer driver does not allow VBA to password protect a file when printing, but Bullzip does.

But again, any solution will do...we publish many official communications to employees as password protected PDF's and have to print 500 of them to Word in a mailmerge, and then open each one, set a password and save it as PDF. Its just unnecessarily onerous so any solution is a good one.

thx
I don't recall an error message with those exact words. However it is obviously an Access command. Where is the code hosted?

What office version are you using? My 2007 version of Word doesn't support PDF passwording, but I understand that Word 2013 does.

I can't see any reason why BullZip shouldn't work, but the code doesn't seem to be trying to actually print the merge output. Instead It saves the document and then calls the function PrintReportAsPDFwithBullZip, but only passes a field from the datasource into the function. It seems to be building a file name for the PDF from the ActiveDocument name, but it doesn't do any printing within this procedure either.
I suggest that you replace the erroring line with:
   
ActiveDocument.PrintOut sFileName

Open in new window

Avatar of Davisro

ASKER

I'm using Word 2010, and I too see that this is code developed for Access. The original post where i got the code is here https://www.experts-exchange.com/questions/28561834/password-protect-a-pdf-document-in-msaccess-vba.html.

I was trying to adapt the code to work in Word a few months back, but surely someone has figured this out already, no?
Avatar of Davisro

ASKER

Actually, i just fond that, since i have Acrobat Pro, my Word 2010 ribbon has a Merge to Adobe PDF button and testing it, it outputs individual PDF files for each record in the Excel data source.  

Halfway there...

But the file names are PDFMailer151.pdf, PDFmailer152.pdf, etc

So I can use Excel VBA to open each file and rename it. I'll still need to password protect them though with individual passwords associated with each record
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland 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
Avatar of Davisro

ASKER

Yes, that worked! Thanks