1

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
Community
  • 1
  • 1
Owen4512
  • 13
  • 4
  • Try `wdocSource.Unprotect , Password:=""`. And comment out the `On Error Resume Next` to see any errors. – CDP1802 May 18 '21 at 17:07
  • Why are you trying to use a protected mailmerge main document? – macropod May 18 '21 at 21:22
  • I have made the adjustments suggested by @CDP1802 and i now get runtime error '429' - ActiveX component can't create object. – Owen4512 May 19 '21 at 12:31
  • @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 May 19 '21 at 12:34
  • Comment out the `Set wd = GetObject(` line. Use task manager to check for and end any orphaned Word processes. – CDP1802 May 19 '21 at 12:39
  • But *why* is the mailmerge main document protected and what *kind* of protection has it have? – macropod May 19 '21 at 12:41
  • I'm now getting runtime error '4605' - The Unprotect method or property is not available because this commend is not available for reading. @CDP1802 – Owen4512 May 19 '21 at 12:44
  • I used 'ActiveDocument.Protect wdAllowOnlyFormFields' to protect the word document. Pages one and two are not protected (where the merged fields are) and the rest of the document contains important information hence why it's protected. Other users will be running the macro so by protecting the document it eliminates the risk of the document being edited in anyway. @macropod – Owen4512 May 19 '21 at 14:57
  • Since you don't even have a password for that, using formfield protection pointless - anyone can still edit the document. Moreover, unless you password protect the output documents, they too can be edited by anyone. – macropod May 20 '21 at 00:16

2 Answers2

0

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.

Charles Kenyon
  • 869
  • 1
  • 8
  • 19
  • Good point on the closing without saving, i didn't think of that! The issue i'm having is that it's not unprotecting the word document therefor the merge doesn't execute. I assume it's my position of the below line. `wdocSource.Unprotect` – Owen4512 May 19 '21 at 11:53
  • What kind of protection are you applying? Is there a password? The previous two comments by @macropod and CDP1802 are good points. – Charles Kenyon May 19 '21 at 12:05
  • I used 'ActiveDocument.Protect wdAllowOnlyFormFields' to protect the word document. And no password @Charles Kenyon – Owen4512 May 19 '21 at 12:38
0

After some searching online i found the issue was being caused by word opening in "Reading mode" and not in print view. I've added 'wd.ActiveWindow.View = wdPrintView' which has resolved my issue. Thanks everyone for your help on this :)

Owen4512
  • 13
  • 4