merge excel

[nick thompson]

i have a spreadsheet with colums of info.The firstcolumn has always someones
name and a row may repeat someones name.What i want to do is send by email
this information to the correct person.I was hoping to do a merge in word and
then send the document.However, it is proving impossible to use merge and get
the info for each person on one page.
Example

Excel sheet
Joe bloggs, column 2, column 3 ,column4
Sarah Green,colum 2,colum 3,colum,4
Joe Bloggs, colum2 ,column 3 ,column 4

i want to be able to email to each person

Word document 1 sent to joe bloggs

Joe Bloggs,column,2,column3,column4
Joe Bloggs,colum2, column3,column4

total column2,total column3,total column 4


second document

Sarah Green,column2,column3,colum4
total column2,total colum3,total colum3

 

[Doug Robbins - Word MVP]

For starters, the Excel sheet will need to have the data sorted by the
column that contains the names of the persons.

But then, you are trying to perform a "multiple items per condition (=key
field)" mailmerge which, "out of the box", Word does not really have the
ability to do:

See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at
http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic


Or take a look at the following Knowledge Base Article

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303

or at:

http://cornell.veplan.net/article.aspx?&a=3815

You do not say what else is in your document, but if you create a Catalog
(on in Word XP and later, it's called Directory) type mailmerge main
document with the mergefields in the cells of a one row table in the
mailmerge main document with the keyfield in the first cell in the row and
then execute that merge to a new document and then run the following macro,
it will create separate tables with the records for each key field in them.
With a bit of further development, you may be able to get it to do what you
want. You may need to look at the "Individual Merge Letters" item on fellow
MVP Graham Mayor's website at:

http://www.gmayor.com/individual_merge_letters.htm

and if you want to email something out to each person, see the article "Mail
Merge to E-mail with Attachments" at

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm

' 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


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP