Password Protected PDF using word excel vba

[CanningTown]

Hi all,

I need help creating password protected pdf’s using vba.

Ideally, the passwords would each have to be unique using info from an excel spreadsheet (ie concatenation of date of birth and postcode etc).

My aim is to automatically generate emails, with the pdf’s as attachment using a macro in excel.

This is what I have done so far:

1. Created a vba in a word doc that fetches info from the excel spreadsheet
a. Run the macro so that it creates the pdf’s

2. Then I go to the same excel spreadsheet and run a macro that generates emails in outlook with the attached pdfs.

All I need now is to have a code that makes the pdfs password protected, ideally unique.

I have trawled through the internet, but haven’t had any luck in funding anyone that has automatically created password protected pdfs.

I’m running on Adobe Acrobat DC and MS 16.

I would be very appreciative of your help.

Thanks,

Canning

[CanningTown]

By the way, it's a mail merge that I'm running in word, then running a macro to create the pdf's.

Thanks,

Canning

[Logit]

.

Here are a few resources. However, I've not attempted what you are seeking so I don't know if these are even pertinent:


https://forums.adobe.com/thread/2292651

https://acrobatusers.com/tutorials/apply_security_with_js/

[macropod]

Try something based on:

Sub Merge_To_Individual_Files()
'Merges one record at a time to the folder containing the mailmerge main document.
' Sourced from: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, StrPwd As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
StrFolder = .Path & Application.PathSeparator
For i = 1 To .MailMerge.DataSource.RecordCount
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = i
.LastRecord = i
.ActiveRecord = i
If Trim(.DataFields("Last_Name")) = "" Then Exit For
'StrFolder = .DataFields("Folder") & Application.PathSeparator
StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
StrPwd = .DataFields("date_of_birth ") & .DataFields("postcode")
End With
.Execute Pause:=False
End With
For j = 1 To Len(StrNoChr)
StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
StrPwd = Replace(StrPwd, Mid(StrNoChr, j, 1), "_")
Next
StrName = Trim(StrName)
With ActiveDocument
.SaveAs2 FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, Password:=StrPwd, AddToRecentFiles:=False
.Close SaveChanges:=False
End With
Next i
End With
Application.ScreenUpdating = True
End Sub
The above code will produce the PDFs. Presumably you already have the required email automation code for sending the attachments; otherwise see: https://wordmvp.com/FAQs/MailMerge/MergeWithAttachments.htm

[CanningTown]

Hi Paul,


Thanks, this is basically the code I've used except originally I didn't have the "StrPwd" written in.


Unfortunately, when I run the macro, it doesn't produce the pdf's with passwords, however, it does produce the pdf's.


One interesting thing now is that it now comes up with a rune-time error '5853' invalid parameter on a piece of code that was previously fine.


The code is: .ActiveRecord = i


I've googled the error but can't seem to find the issue.


Would you know what it would be?


Thanks,

[CanningTown]

Thanks Logit but unfortunately this doesn't help to solve my issue of automatically creating password protected pdf's in vba.


Thanks though

[macropod]

I don't know why you'd get an error on .ActiveRecord = i' - the code ordinarily works just fine.

If the passwords aren't being applied via SaveAs2, your only option would be to automate Adobe Acrobat Pro (not the Reader) and have it open the PDFs and protect them after Word has saved & closed them and before emailing. At least the code above extracts the password for you to feed into the Acrobat automation code.

[CanningTown]

Hi Paul,


Thanks again for your help.


It no longer comes up with an error message for .ActiveRecord = i


However, it does now come up with a 4198 error message for SaveAs2 code


.SaveAs2 FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, Password:=StrPwd, AddToRecentFiles:=False


I understand that that error is associated with hyperlinks?


There is some rangetoHTML vba in the excel file, would this be causing the problem? If so how can it be resolved?


Thanks,


Canning

[macropod]

In post #5 you indicated the Save worked, so what have you changed since then?

[CanningTown]

Hi Paul,


I haven't changed anything.


It just seems to be fickle. Any idea of what might be the issue?


Thanks,


Canning

[macropod]

Are you running the code in a saved document? The StrFolder variable will be invalid otherwise.