Unprotect/Protect Word document through Excel VBA

Question

I want to execute a mail merge from Excel through VBA.

I'm having some difficulties unprotecting and protecting the Word document. How do I unprotect the Word document, execute the mail merge and then protect the document once again?

Sub RunMerge()

Dim wd As Object
Dim wdocSource As Object

Dim strWorkbookName As String

Validate_Form
If Left(Sheet1.Range("B48").Text, 7) = "Missing" Then
Exit Sub
Else

            On Error Resume Next
            Set wd = GetObject(, "Word.Application")
            If wd Is Nothing Then
                Set wd = CreateObject("Word.Application")
            End If


            Set wdocSource = wd.Documents.Open("C:\Users\owen4512\Desktop\Templates\Document1.docx")
        
            strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
        
            wdocSource.MailMerge.MainDocumentType = wdFormLetters
            
            wdocSource.Unprotect
        
            wdocSource.MailMerge.OpenDataSource _
                    Name:=strWorkbookName, _
                    AddToRecentFiles:=False, _
                    Revert:=False, _
                    Format:=wdOpenFormatAuto, _
                    Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
                    SQLStatement:="SELECT * FROM `Admin$`"

            With wdocSource.MailMerge
                .Destination = wdSendToNewDocument
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = 11 'wdDefaultFirstRecord
                    .LastRecord = 11 'wdDefaultLastRecord
                End With
                .Execute Pause:=False
            End With
        
            wd.Visible = True
            wdocSource.Protect , Password:=""
            wdocSource.Close SaveChanges:=False

            Set wdocSource = Nothing
            Set wd = Nothing
End If
End Sub

Try wdocSource.Unprotect , Password:="". And comment out the On Error Resume Next to see any errors. — CDP1802
Why are you trying to use a protected mailmerge main document? — macropod
I have made the adjustments suggested by @CDP1802 and i now get runtime error '429' - ActiveX component can't create object. — Owen4512
@macropod So the main document is currently protected and i want the vba to unprotect the document and complete the merge - I guess i dont need to re-protect the document. — Owen4512
Comment out the Set wd = GetObject( line. Use task manager to check for and end any orphaned Word processes. — CDP1802

Charles Kenyon Charles Kenyon · Accepted Answer · 2021-05-18T17:01:15

I am assuming that your code already is unprotecting the primary merge document and performing the merge. If so, the primary merge document should still be protected after your macro runs. Closing it without saving changes should accomplish that, since unprotecting it is a change.

Unprotect/Protect Word document through Excel VBA

2 Answers