0

I have a Mail Merge attached to a SQL query that I need to reformat. The 'action_name' field was inserted and works properly. [EDIT: To clarify, all of the data appears under one mergefield name called "action_name"].

For example, it will appear in the letter like this:

PersonA, You are missing the following items: 'A, B, C, D, E and F.'
PersonB, You are missing the following items: 'A and B'
PersonC, You are missing the following items: 'A'
PersonD, You are missing the following items: 'A, B, C and D'

Of course each person receives their own letter, so they only see their own missing items.

When we give the letter to the other department, they have to manually hit enter on each time to reformat the letter and make it look like this:

PersonA, You are missing the following items: 
'A, 
 B, 
 C, 
 D, 
 E,
 F'

I will only do PersonA in this example. I think you get the idea.

How can I do this using code in a Mail Merge (or in the SQL code) so that others don't have to do this manually? I already know that the \v vertical formatting doesn't work for this. I read that it's for East Asian languages. Thanks for any tips!

byobob
  • 99
  • 1
  • 13

2 Answers2

1

From your description, "A, B, C, D, E and F" and "A and B" are each output via a single mergefield. Word has no field coding mechanism to insert breaks into such content. Inserting breaks would be trivial if the output was generated by separate mergefields. you could, of course, add a macro to the mailmerge main document to automate the post-merge processing. For example the following macro intercepts the 'Merge to Individual Docuemtns' action to do all the processing:

Sub MailMergeToDoc()
Application.ScreenUpdating = False
ActiveDocument.MailMerge.Execute
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchWildcards = True
  .Text = "('[A-Z],)"
  .Replacement.Text = "^l\1"
  .Execute Replace:=wdReplaceAll
  .Text = "([A-Z],) "
  .Replacement.Text = "\1^l"
  .Execute Replace:=wdReplaceAll
  .Text = "([A-Z]) and ([A-Z])"
  .Replacement.Text = "\1,^l\2"
  .Execute Replace:=wdReplaceAll
End With
Application.ScreenUpdating = True
End Sub
macropod
  • 12,757
  • 2
  • 9
  • 21
  • Yes, you are right! I didn't clarify, but all of the data is put together in one element/mergefield. As for this solution, how would you add a macro into a mail merge (I am new to mail merge)? And is this kind of like a line break: "\1^l" ? – byobob May 24 '19 at 15:51
  • For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm For Mac macro installation & usage instructions, see: https://wordmvp.com/Mac/InstallMacro.html And, yes, ^l inserts a line break. ^l is a standard Find/Replace expression in Word. – macropod May 25 '19 at 00:14
1

You can use SQL to insert a line break character between each list item before it gets to Word. Depending on what system you are on, the syntax will be different but you should have a function to concatenate an ascii character code 10.

For example on Transact-SQL/SQL Server: SELECT 'A' + char(10) + 'B' + char(10) + 'C'

or to replace commas with line breaks: SELECT replace('A, B, C', ',', char(10))

Colleen
  • 11
  • 2