Mail Merge Macro

[Michelle]

I have a CSV file with content as follows
H, Ben, Smith, 5 North Lane, Some Suburb, 3131
D, Bicycle, $120
H, Jill, Brown, 63 Heather Street, Blue Suburb, 546
D, Skateboard, $45
D, Computer, $300

There is one H (Header) row for each person, but each
person may have more than one D (Detail) row. I need to
mail merge this data into a Word letter so that I can
send each person a letter with the items in the Detail
rows listed. There is no limit to how many Detail rows a
person may have.

The CSV file is generated automatically by another
application and does not include a traditional header
(Name, Surname etc) for the document.

Looking forward to hearing ANY ideas,
Michelle

 

[Doug Robbins - Word MVP]

Hi Michelle,

That's not the sort of thing that mailmerge does well, if at all. With that
layout, it would not be too difficult to write a macro that "processed" the
data in the CSV file and created letters from it.

If you had a template for the letter named order.dot in which you have a
bookmark named "address" where you want the address to go and another
bookmark named order where you want the details of the products to go, and
you open the csv file in Word and then run the following macro, it should
create new document for each H record and populate it with the addressee and
the product details:

' Throwaway Macro created by Doug Robbins
'
Dim source As Document, target As Document, i As Long, Addressee As String,
Detail As String, spara As Range
Dim fname As String, lname As String, street As String, town As String, item
As String, price As String
Set source = ActiveDocument
For i = 1 To source.Paragraphs.Count
Set spara = source.Paragraphs(i).Range
If Left(spara, 1) = "H" Then
Set target = Documents.Add("order.dot")
Addressee = Mid(spara, 4)
fname = Left(Addressee, InStr(Addressee, ",") - 1)
Addressee = Mid(Addressee, InStr(Addressee, ",") + 1)
lname = Left(Addressee, InStr(Addressee, ",") - 1)
Addressee = Mid(Addressee, InStr(Addressee, ",") + 2)
street = Left(Addressee, InStr(Addressee, ","))
town = Mid(Addressee, InStr(Addressee, ",") + 2)
target.Bookmarks("Address").Range.InsertBefore fname & lname & vbCr
& street & vbCr & town
Else
Addressee = Mid(spara, 4)
item = Left(Addressee, InStr(Addressee, ",") - 1)
Addressee = Mid(Addressee, InStr(Addressee, ",") + 2)
price = Mid(Addressee, InStr(Addressee, ",") + 1)
target.Bookmarks("Detail").Range.InsertBefore item & vbTab & price
End If
Next i

It assumes that in the CSV file, there are spaces after the commas as in
the example of the data in your post.

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

 

[Cindy M -WordMVP-]

Hi Michelle,

You'll find a discussion on "one-to-many" mail merges in the
Special Merges section of my website's mail merge FAQ. I'm
not certain whether the Database field approach will work
with a CSV file, but it would certainly be the easiest to
implement, if it does.

Otherwise, you'll need to use the technique described in the
referenced Knowledge Base article.

I have a CSV file with content as follows
H, Ben, Smith, 5 North Lane, Some Suburb, 3131
D, Bicycle, $120
H, Jill, Brown, 63 Heather Street, Blue Suburb, 546
D, Skateboard, $45
D, Computer, $300

There is one H (Header) row for each person, but each
person may have more than one D (Detail) row. I need to
mail merge this data into a Word letter so that I can
send each person a letter with the items in the Detail
rows listed. There is no limit to how many Detail rows a
person may have.

The CSV file is generated automatically by another
application and does not include a traditional header
(Name, Surname etc) for the document.

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