mail merge - page break

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 24 Sep 2004 12:50 GMT

HELP - I am not sure how to describe my problem, so I hope this makes
sense - I am trying to do a mail merge from an excel file - My problem I do
not know how to get the page to break to a new page when the data has
changed - here is a sample of the data & form:

DATA:
ID # NAME POSITION EMAIL
000141 MICHAEL TAYLOR PRINCIPAL
000141 JANET MCCONNELL PERSONAL LINES CSR JCONS@DTCINSURANCE.COM
000141 JANET MCCONNELL PERSONAL LINES CSR JCONS@DTCINSURANCE.COM
000141 LISA ALLEN COMMERCIAL LINES CSR LALLEN@DTCINSURANCE.COM
000141 ANN TEMPLE ACCOUNTING ANNT@DTCINSURANCE.COM
000163 G GALLAGHER GGALLAGHER@DUNWODYINSURANCE.COM
000163 JEANNE PEACOCK JPEACOCK@DUNWODYINSURANCE.COM
000163 JAMES DUNWODY PRINCIPAL
000170 JOE BAKER VICE PRESIDENT/ALL LINES/ACCOUNTING
JSBAKER@BELLSOUT.NET
000178 MARY RONAS PRINCIPAL AUTOBAHNINS@ATLCAPPI.NET
000178 FLETCHER BALDWIN AGENT LEE@ATLCAPPI.NET
000182 DAVID JORDAN PRINCIPAL
000187 HAL FOSTER PRINCIPAL
000187 ROD WITMER COMMERCIAL LINES ROD.WITMER@FOSTERWITMER.COM
000187 DEBBIE PEAK PERSONAL LINES DEBBIE.PEAK@FOSTERWITMER.COM
000187 STACY MILLER ACCOUNTING INFO@FOSTER-ASSOCIATES.COM

Name:

Position:

Email Address:

----------------------------------------------------------------------------
-----------

Name:

Position:

Email Address:

----------------------------------------------------------------------------
-----------

Name:

Position:

Email Address:

There are up to 7 groups of the NAME, POSITION & EMAIL ADDRESS. What I need
to be able to do is have the data listed above merged into each of the
fields as long as the ID is the same. Once the ID # changes, I need it to
do a page break, then start a merge for the next ID # until all the data in
the spreadsheet is merged. Is there a way to handle?

Any help is greatly appreciated.

Thanks in advance for your time.

Reply to this Message

Scott - 24 Sep 2004 15:10 GMT

I found this code on microsoft's site, and it appears to almost work.
Problem I am having now is this code will only print the first record for
each group. Its breaking where it should, just not printing the records
before it breaks. Any ideas?

{ If { MERGESEQ } = "1" "{ MERGEFIELD CITY }?
" ""}{ SET Place1 { MERGEFIELD CITY }}?
{ If { Place2 } <> { Place1 }"?
----------------------------Page Break--------------------------------
{ MERGEFIELD CITY }?
?
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } {
MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}?

> HELP - I am not sure how to describe my problem, so I hope this makes
> sense - I am trying to do a mail merge from an excel file - My problem I do
[quoted text clipped - 54 lines]
>
> Thanks in advance for your time.

Reply to this Message

Doug Robbins - 24 Sep 2004 23:34 GMT

See if you can make use of the following macro to do it:

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range

Dim data As Range, stab As Table, ttab As Table

Dim i As Long, j As Long, k As Long, n As Long

Set source = ActiveDocument

Set target = Documents.Add

Set stab = source.Tables(1)

k = stab.Columns.Count

Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)

Set scat = stab.Cell(1, 1).Range

scat.End = scat.End - 1

ttab.Cell(1, 1).Range = scat

j = ttab.Rows.Count

For i = 1 To stab.Rows.Count

Set tcat = ttab.Cell(j, 1).Range

tcat.End = tcat.End - 1

Set scat = stab.Cell(i, 1).Range

scat.End = scat.End - 1

If scat <> tcat Then

ttab.Rows.Add

j = ttab.Rows.Count

ttab.Cell(j, 1).Range = scat

ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True

ttab.Rows.Add

ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False

For n = 2 To k

Set data = stab.Cell(i, n).Range

data.End = data.End - 1

ttab.Cell(ttab.Rows.Count, n - 1).Range = data

Next n

Else

ttab.Rows.Add

For n = 2 To k

Set data = stab.Cell(i, n).Range

data.End = data.End - 1

ttab.Cell(ttab.Rows.Count, n - 1).Range = data

Next n

End If

Next i

Signature

Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

> HELP - I am not sure how to describe my problem, so I hope this makes
> sense - I am trying to do a mail merge from an excel file - My problem I
[quoted text clipped - 57 lines]
>
> Thanks in advance for your time.

Reply to this Message

Rate this thread: