Davisro
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
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
In Module 1Option 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
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 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 PrintReportAsPDFwithBullZi p, 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.
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 PrintReportAsPDFwithBullZi
I suggest that you replace the erroring line with:
ActiveDocument.PrintOut sFileName
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?
I was trying to adapt the code to work in Word a few months back, but surely someone has figured this out already, no?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, that worked! Thanks
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.