Mail Merge using a section of a document, rather than the entire document?

[Robin Tucker]

Hiya,

I have a document with a "header" and "footer" (ie. 2 full pages of guff),
in-between I have a section containing mail merge field codes with
appropriate formatting for each item I want to merge with. What I want to
do is perform a merge with an external data source such that I end up with
the header page, followed by each of the merged records formatted into the
sections, followed by the footer page. At present, when I do a mail merge,
I end up with the entire document copied into the new document for every
record I am merging with.

How can I do this?

ie:


START OF MY COMPANY REPORT

<<startofmysection>>
First widget <<fielditem1>>
Second Widget <<fielditem2>>
<<endofmysection>>

END OF MY COMPANY REPORT


After mail merge becomes:


START OF MY COMPANY REPORT

<<startofmysection>>
First widget: Foo
Second Widget: Bar
<<endofmysection>>

<<startofmysection>>
First widget: my Other Foo
Second Widget: my Other Bar
<<endofmysection>>

END OF MY COMPANY REPORT

 

[Doug Robbins - Word MVP]

Try running the following with the mailmerge main document as the
activedocument.

Dim Source As Document, Target As Document, Head As Document, Foot As
Document, myrange As Range
Set Source = ActiveDocument
Selection.HomeKey wdStory
Set myrange = Selection.Bookmarks("\page").Range
Set Head = Documents.Add
Head.Range = myrange
myrange.Delete
Source.Activate
Selection.EndKey wdStory
Set myrange = Selection.Bookmarks("\page").Range
Set Foot = Documents.Add
Foot.Range = myrange
myrange.Delete
Source.Activate
With Source.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
Set Target = ActiveDocument
Set myrange = Head.Range
Target.Range.InsertBefore myrange
Head.Close wdDoNotSaveChanges
Set myrange = Foot.Range
Target.Range.InsertAfter myrange
Target.Close wdDoNotSaveChanges

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Robin Tucker]

Thanks for that Doug. I solved this problem in another way (due to other
constraints) - as I am working from a "template", what I am now doing is
finding "record start" and "record end" field codes, copying the text
in-between and pasting it n times in the document. A second pass will
manually go through and perform the database "merge". The advantage here is
I can have an arbritrary amount of text before and after the "record" part
of the template. Here is the code for the benefit of others in the group
who may find it useful:

Use: Find start and end field codes, then copy/paste the code inbetween "n"
times:

<you need to add a reference to ADO to use this...>

Sub Merge()

' Our SQL connections and strings.

Dim adoCN As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim strSQL As String

Set adoCN = New ADODB.Connection

' Open the connection

adoCN.Open "Provider=sqloledb;" & _
"Data Source=xxxxxxx;" & _
"Initial Catalog=yyyyyyy;" & _
"Persist Security Info=False;" & _
"Integrated Security=SSPI"

' Create a recordset

Set adoRS = New ADODB.Recordset

strSQL = "SELECT * From ReportTable"

' Execute the query

adoRS.Open strSQL, adoCN, adOpenStatic

' // Find out where the record field section starts and ends

Dim fieldStart As Field
Dim fieldEnd As Field

For i = 1 To ActiveDocument.Fields.Count

Dim theField As Field

Set theField = ActiveDocument.Fields(i)

Select Case theField.Result.Text

Case "«Record Start»"
Set fieldStart = theField

Case "«Record End»"
Set fieldEnd = theField

End Select

Next

' Guard here - check fieldStart and fieldEnd - if they are NOTHING, then
no record field codes in the template

' Create new document based on this template.

Documents.Add Template:=ActiveDocument.AttachedTemplate.FullName

' Set the range to the record start and end

Selection.SetRange fieldStart.Result.End, fieldEnd.Result.Start

' Copy between

Selection.Copy

' Collapse the selection to the end

Selection.Collapse Direction:=wdCollapseEnd

' and paste it into the document once for each record in the recordset.

For i = 0 To adoRS.RecordCount - 2
Selection.Range.Paste
Next

'
////////////////////////////////////////////////////////////////////////////
' // Close my data connections.
'
////////////////////////////////////////////////////////////////////////////

adoRS.Close
adoCN.Close

End Sub