woensdag 2 oktober 2013

Word: VBA to Remove Recipient List from a Mail Merge Letter

A company I have been working for some time ago uses about two thousand mail merge letter templates. Now and then they have to make some changes in those letters. And then, for some reason out of all the templates the recipient list had to be removed. This, they did by hand. For each template 8 steps. 16,000 steps in total. Several times a year. Hundreds of hours every year. I keep wondering why it takes so long before people realize this can be automated.

With a pretty simple script I managed to automate the whole process:

Sub eraselink()
    Dim MyFile As String, MyPath As String, Mystring as String
    MyPath = "U:\Office\test\" 'Fill in the right path here
    MyString =  MyPath & "*.doc"
    MyFile = Dir(MyString)
    Do While MyFile <> ""   ' Start the loop with the first DOC file
         Application.ScreenUpdating = False
         Application.DisplayAlerts = wdAlertsNone
         Application.Documents.Open (MyPath & MyFile)
        
         ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
         'remove recipients list
         ActiveDocument.Save
         ActiveDocument.Close
         MyFile = Dir()   ' Get next entry or DOC file
         Application.DisplayAlerts = wdAlertsNone
         Application.ScreenUpdating = True
    Loop

End Sub

I just keep wondering
Een reactie posten