Macro for MS Word Mail Merge Formatting

Experts:

I have an MS Word document that is populated with several ‘Insert Mail Merge Fields’ that are populated from an Excel Spreadsheet.

Objective 1:  I would like to create a Macro that automatically runs (populates) the ‘Insert Mail Merge Fields’ whenever the document is opened (there will only be one-record set at any given time).

Objective 2:  Some of these ‘Insert Mail Merge Fields’ will populate with the word ‘Delete—‘ which, when applicable, will always appear at the beginning of a paragraph.  Example:

My name is John Doe.
Delete—my name is April Showers
Delete--I am a girl.
I am a boy.
I like football.
Delete--I like shopping

I would like to create a Macro in the Word Document that automatically deletes any paragraph that begins with the word ‘Delete-‘; and then shifts the remaining text up that there are no large gaps between the remaining paragraphs.  
Example:

My name is John Doe.
I am a boy.
I like football.

I would like this event to happen each time the document is opened.

Objective 3:  Upon opening this Word Document,  I would like a Save Browse Window to open up—basically forcing the user to save the document somewhere else before viewing it.

Can someone give me a code to make this happen?

Note:  I’m not very experienced in creating Macro’s in MS Word so if you could give me as detailed, step by step instructions as possible, it would be most appreciated.
mdstallaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
Put this macro in the ThisDocument module of your Mail Merge Main document. It will execute the merge and will call the code to reformat the Result document.
You can open the VBA editor with Alt+F11. Use the Project Explorer (Top left panel) to browse to the ThisDocument code module.
Sub Document_Open()
    With ThisDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    EditResultDocument
End Sub

Open in new window

Insert a code module into your document's project using the Insert menu and put this macro there.
Private Sub EditResultDocument()
    With ActiveDocument
        With .Range.Find
            
            'delete flagged paragraphs
            .Text = "^13Delete--*^13"
            .Replacement.Text = "^p"
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = True
            .Execute Replace:=wdReplaceAll
            
            'delete paragraphs that only contain spaces
            .Text = "^13[ ]{1,}^13"
            .Replacement.Text = "^p"
            .Execute Replace:=wdReplaceAll
           
            'remove manual page breaks
            .MatchWildcards = False
            .Text = "^m"
            .Replacement.Text = ""
            .Execute Replace:=wdReplaceAll
        End With
        'remove inter-paragraph vertical space
        .Range.Paragraphs.SpaceAfter = 0
        .Range.Paragraphs.SpaceBefore = 0
       
    End With
End Sub

Open in new window


You will have to save the document as macro-enabled (.docm extension).

There is no need to save the document. It does not get modified in the merge process. A new Result document is created instead. When you go to save that the SaveAs dialogue will come up automatically.
0
mdstallaAuthor Commented:
Graham:

I followed your instuctions but MS Word did not function as desired.  When I open the Word Document, it simply displays the word  'Delete--' as a basic mail-merge field insert.  When I open this document, paragraphs that begin with 'Delete--' are not being eliminated.

I cannot attach the Word document on Expert Exchange because it doesn't like the document format.  You should note that my Excel document is saved as a standard Excel workbook and my Word Document is saved as a 'Word Macro-enabled' document.

Any idea what I'm doing wrong?
0
mdstallaAuthor Commented:
Attached is an example Spreadsheet and Document that you can work with...
GrahamExcel.xlsx
GrahamWord.docx
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

GrahamSkanRetiredCommented:
The document has only one paragraph. The lines are separated by newline characters.
Use the Show/Hide toggle button (¶) on the Home tab and will see that the lines are terminated with a 'return' symbol as on the Enter key, as opposed to the pilcrow whcih is like the symbol on the Show/Hide button.
0
mdstallaAuthor Commented:
Graham:

I made the corrections you described, but when I open the document to see if it works, I get an error message:

"Sub Function Not Defined"

It takes me to this code and highlights Sub Document Open() and EditResultsDocument

Sub Document_Open()
    With ThisDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    EditResultDocument
End Sub
0
GrahamSkanRetiredCommented:
Sorry, inadequate testing on my part. For a while I had both procedures in the same module, so that it didn't matter that the called procedure was set to Private:
Private Sub EditResultDocument()

Open in new window

Change the scope of the second procedure by changing 'Private' to 'Public'. Or just remove the word. 'Public' is the default.
Sub EditResultDocument()

Open in new window

0
mdstallaAuthor Commented:
Okay, I made the changes you suggested and here's what happens:

When I open the main merge document-- MS Word creates a separate document that says: "Word found locked fields during the update.  Word cannot update locked fields."

It also creates another document titled "Letters1" which appears to be my main merge document without performing the desired function.  My main merge document remains open without performing the desired function.

Any suggestions?

Note:  My main merge document is saved as a Word Macro Enabled Document.  Expert Exchange wont let me upload it to show you.
GrahamExcel.xlsx
0
GrahamSkanRetiredCommented:
Normally one new result document is created. Its name will depend on the target format, with a number which is incremented for each merge during the particular Word session, so 'Letters1' is the first execution of that merge type. The extra document is there to report errors in the process.

If you have locked the fields in the Main document, you can unlock them by selecting the whole of the document (Ctrl+A) and then pressing Ctrl+Shift+F11. This short article from Microsoft explains how to lock and unlock any particular field:
http://office.microsoft.com/en-gb/word-help/lock-or-unlock-a-field-HP005189392.aspx

As an alternative, you could clear the locked fields in code:
Sub Document_Open()
    ThisDocument.Fields.Locked = False
    With ThisDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    EditResultDocument
End Sub

Open in new window

0
mdstallaAuthor Commented:
G—

We're getting close, but it's not functioning as 'nicely' as I would like it.

Here's what my form looks like before I place your codes in the Word Document:

My name is John Doe.

Delete--My name is April Showers.

Delete--I am a girl.

I am a boy.

I like football.

Delete--I like shopping.

And here's what it looks like after I place your codes in the Word Document (and the codes are run):

My name is John Doe.



I am a boy.

I like football.

And here's what I would prefer it to look like (notice the text shifts up and replaces the space where any paragraphs got deleted):

My name is John Doe.

I am a boy.

I like football.

Also, I would prefer it if the MS Word only displays the document that was updated and does not produce any other documents (i.e. I'd rather it not produce my original merge document, letters2 letters3, etc.)

Can you take one more look at it and see if we can tweak these functions?  Thanks again for all your work on this— the proper execution of this function it's a very important piece of the program I'm working on.
0
GrahamSkanRetiredCommented:
I modified your sample document to replace the newline characters with paragraph marks. The macro then did as expected. I couldn't reproduce your vertical spacing problem, and the plain text that you pasted into the comment  doesn't help. We need actual documents to understand what is causing the extra spacing. I did notice that some paragraphs were being missed, i.e. not deleted, so here is some code that corrects for that.
Public Sub EditResultDocument()

    With ActiveDocument
        With .Range.Find
            Do
               'Delete flagged paragraphs. Loop to overcome problem of missed adjacent paragraphs
                .Text = "^13Delete--*^13"
                .Replacement.Text = "^p"
                .MatchWildcards = True
            Loop While .Execute(Replace:=wdReplaceAll)
        End With
        With .Range.Find
            'delete paragraphs that only contain spaces
            .Text = "^13[ ]{1,}^13"
            .Replacement.Text = "^p"
            .Execute Replace:=wdReplaceAll
            'remove manual page breaks
            .MatchWildcards = False
            .Text = "^m"
            .Replacement.Text = ""
            .Execute Replace:=wdReplaceAll
        End With
        'remove inter-paragraph vertical space
        .Range.Paragraphs.SpaceAfter = 0
        .Range.Paragraphs.SpaceBefore = 0
    End With
End Sub

Open in new window

Note that the usual way of selectively including text in a merge document would be to use an IF field to test the mergefield value.
The field code would look something like this:
{ IF { MERGEFIELD Condition1 } = "Delete--" "" "My name is John Doe.¶" }
You wouldn't then need to delete the paragraphs after the merge has been run.

To change the name of the result document, you would have to save it with the new name. If the name is exactly the same and the Main document, you will overwrite it and hence lose it.

It seems that the design of the overall project need to be re-assessed.
0
mdstallaAuthor Commented:
Well, your codes seem to be working, in that they do appear to delete the appropriate paragraphs that need to be deleted.  

Perhaps it may be possible to run a second code (Macro) after the update from your codes that simply tells Word to shift all remaining text up so that each paragraph in the document is organized with exactly one space between the next.    This is something I can ask to the general EE Community and see if I can get some help/advise.

The only concern remaining with regard to your support are these extra pages that are generated-- again, I'd rather only produce the updated document-- without ever seeing the original merge document, letters2 letters3, etc..

By chance, do you have any additional code we could add to eliminate these extra reports?

I've attached the prototype reports I've been working with to test your codes.  All you have to do is merge Condition1 to Condition6 from the Excel Spreadsheet into the Word document, then save the Word document as a Maco-enabled document… and you'll be working with what I have.

Thanks again for sticking with me on this one.
MExcelSS.xlsx
MWordReport.docx
0
GrahamSkanRetiredCommented:
Your new sample document doesn't have any merge fields, but I think I get the idea. In it you have interspersed the text paragraphs with empty paragraphs. I guess that you don't want these empty paragraphs to appear in the result document, so I have added some code to delete them from there.

By design, the result document of a mail merge is given the name of the merge type (Letter, Catalog, Label) followed by a sequential number. The only way that VBA can change the name of an open document is to save it with the new name. It is not possible to save it with the exact name of the main document - Word must be able to distinguish between open documents. However, since the main document is macro-enabled, we can save the result with a ".docx" extension. Code to do this and to immediately close the main document has been included, so that it gives the appearance of the result document being the main document.

The intention of Mail merge is to process multiple records and to produce an output for each. In the case of Letters, each record is followed by a Next Page Section Break. Your data source worksheet has only one data row, so it is hard to devise how you want multiple records to be handled. However, since you have asked vertical white space to be removed, there is now a line to change the merge type from Letters to Directory (aka Catalog), which omits these section breaks.
Sub Document_Open()
    ThisDocument.Fields.Locked = False
    With ThisDocument.MailMerge
        .MainDocumentType = wdDirectory
        .Destination = wdSendToNewDocument
        .Execute
    End With
    EditResultDocument
End Sub

Public Sub EditResultDocument()

    With ActiveDocument
        With .Range.Find
            Do
               'Delete flagged paragraphs. Loop to overcome problem of missed adjacent paragraphs
                .Text = "^13Delete--*^13"
                .Replacement.Text = "^p"
                .MatchWildcards = True
            Loop While .Execute(Replace:=wdReplaceAll)
        End With
        With .Range.Find
            'delete paragraphs that only contain spaces
            .Text = "^13[ ]{1,}^13"
            .Replacement.Text = "^p"
            .Execute Replace:=wdReplaceAll
            
            'remove manual page breaks
            .MatchWildcards = False
            .Text = "^m"
            .Replacement.Text = ""
            .Execute Replace:=wdReplaceAll
            
            'remove empty paragraphs
            .MatchWildcards = False
            .Text = "^p^p"
            .Replacement.Text = "^p"
            .Execute Replace:=wdReplaceAll
        End With
        'remove inter-paragraph vertical space
        .Range.Paragraphs.SpaceAfter = 0
        .Range.Paragraphs.SpaceBefore = 0
        
    End With
    ActiveDocument.SaveAs Replace(ThisDocument.FullName, ".docm", ".docx", , , vbTextCompare), wdFormatXMLDocument
    ThisDocument.Close wdDoNotSaveChanges
End Sub

Open in new window

The outcome is now quite a distance from a mail merge. The further away it is, the less maintainable it is, so if any more changes become necessary, it might be a good idea to approach your systems analyst for a rethink.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdstallaAuthor Commented:
Yah, that seems to work.  When I open the Word document, all sentences that begin with 'Delete--' are eliminated and no other forms are opened.

I can work with that!

Thanks for all your hard work on this one!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.