Using Mail Merge with multiple values

[Patrick]

I created a Mail Merge document where I am using Excel to
merge the data into my document. How can I list multiple
records on the same document. Below are some Excel records
that I am using. My document has a main header, which
consist of the Patient's First Name, Last Name, Member#,
Acct#, SSN and so on. The data displays on the header just
fine. In the middle part of my document, I need to display
some of the below listed information. For each patient, I
need to list their corresponding DIAG code and DESC on
that same form. For example, looking at the below data,
Ted Smith's information should be displayed in the middle
part of the document as follows.

Code Desc

25000 DIABETES
4139 ANGINA
7140 ARTHRITIS

After the above information is merged, then the next
record for Mike Jones should be displayed on a new page.
Is there a way to accomplish this task? My Excel
spreadsheet consist of 49,000 rows, so doing something
manual will take forever. If anyone knows of a way to help
me or put me in the right direction, it will be greatly
appreciated.

Thanks,

Patrick


MEMBER# ACCT# LNAME FNAME DOB DIAG DESC
58936057600 17444 SMITH TED 07/03/1935 25000 DIABETES
58936057600 17444 SMITH TED 07/03/1935 4139 ANGINA
58936057600 17444 SMITH TED 07/03/1935 7140 ARTHRITIS
00496426000 12796 JONES MIKE 03/13/1931 4280 HEART FAIL
01242927100 28703 DOE JOHN 08/11/1929 25000 DIABETES
01242927100 28703 DOE JOHN 08/11/1929 41090 MYOCARDIAL

 

[Cindy M -WordMVP-]

Hi Patrick,

Take a look at the discussion on doing "one-to-many" types of
mail merges in the Special Merges section of the FAQ on my
website. There are links to sample files/instructions for the
three basic approaches. For simplicity's sake, if you can
display the "many" list in a table format, I recommend the
DATABASE field approach.

My document has a main header, which
consist of the Patient's First Name, Last Name, Member#,
Acct#, SSN and so on. The data displays on the header just
fine. In the middle part of my document, I need to display
some of the below listed information. For each patient, I
need to list their corresponding DIAG code and DESC on
that same form.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Zed Gorski]

Hi Cindy

I am using merge to fax from Word 2002 and it is working fine.

My database has format as below:

Add1 Add2 Problem Fax
aaa aaaa 11111 [fax:12345678]
aaa aaaa 22222 [fax:12345678]
bbb bbbb 123488 [fax:23456789]

Using standard mail merge there will be 3 calls and 3 pages with problem and
its description

How can create multiple pages document (first 2 records) and only two calls
will be needed to process all recepients list

Thanks

Zed

 

[Doug Robbins - Word MVP]

Hi Zed,

You may need to modify this to do exactly what you want:

' Macro to create multiple items per condition from a document created by a
catalog or 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.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
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


--
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

Hi Cindy

I am using merge to fax from Word 2002 and it is working fine.

My database has format as below:

Add1 Add2 Problem Fax
aaa aaaa 11111 [fax:12345678]
aaa aaaa 22222 [fax:12345678]
bbb bbbb 123488 [fax:23456789]

Using standard mail merge there will be 3 calls and 3 pages with problem and
its description

How can create multiple pages document (first 2 records) and only two calls
will be needed to process all recepients list

Thanks

Zed

Hi Patrick,

Take a look at the discussion on doing "one-to-many" types of
mail merges in the Special Merges section of the FAQ on my
website. There are links to sample files/instructions for the
three basic approaches. For simplicity's sake, if you can
display the "many" list in a table format, I recommend the
DATABASE field approach.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)


This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Zed Gorski]

Hi,

Thanks for response

I am interested how to solve my problem on conceptual level
First question: Can be done without VBA
If VBA has to be used , please provide only step by step procedure

Thanks

Zed

Hi Zed,

You may need to modify this to do exactly what you want:

' Macro to create multiple items per condition from a document created by a
catalog or 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.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
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


--
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

Hi Cindy

I am using merge to fax from Word 2002 and it is working fine.

My database has format as below:

Add1 Add2 Problem Fax
aaa aaaa 11111 [fax:12345678]
aaa aaaa 22222 [fax:12345678]
bbb bbbb 123488 [fax:23456789]

Using standard mail merge there will be 3 calls and 3 pages with problem and
its description

How can create multiple pages document (first 2 records) and only two calls
will be needed to process all recepients list

Thanks

Zed

Hi Patrick,

Take a look at the discussion on doing "one-to-many" types of
mail merges in the Special Merges section of the FAQ on my
website. There are links to sample files/instructions for the
three basic approaches. For simplicity's sake, if you can
display the "many" list in a table format, I recommend the
DATABASE field approach.

My document has a main header, which
consist of the Patient's First Name, Last Name, Member#,
Acct#, SSN and so on. The data displays on the header just
fine. In the middle part of my document, I need to display
some of the below listed information. For each patient, I
need to list their corresponding DIAG code and DESC on
that same form.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)


This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Cindy M -WordMVP-]

Hi Zed,

I am interested how to solve my problem on conceptual level
First question: Can be done without VBA
If VBA has to be used , please provide only step by step procedure

Did you read up on the one-to-many information on my website, in the
Special Merges section?

I can assure you, we have no interest in re-typing that very basic
information. That's why it's on the website Follow-up questions
to the various approaches listed there, yes.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30
2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Zed Gorski]

Hi Cindy,

Thank you for sugestions and response

Zed