Multiple-Record Word Mail Merge

[Anne Troy]

Word's mailmerge feature has a terrible failing.

You cannot tell it you want to merge multiple records from one data source. In other words, if I have a list with:

clientname
invoice no
invoice date
invoice amount

I cannot create a mail merge that will put all of client one's invoices on one page. Cindy Meister shows how to do it here, but I'll be darned if I've ever gotten it to work, and at the moment, can't even get to that page:

http://homepage.swissonline.ch/cindy...tm#ComplexMerg

So, what I would have loved all these years is a wizard that will let me do this. It could simply grab and display the headings in the data source file, whatever that data source may be, and allow the user to pick which heading to group by--much like an Access report.

If someone can do this, it'd be worth (easily) $50 a pop right here from our web.

Do we need a sample file?

[royUK]

Why not record your data in Excel and use a PivotTable - much easier!

[Anne Troy]

LOL!

That won't work if you're wanting to create documents to mail out to people in letter form, Roy.

Get your mind out of the Excel box, LOL!

[royUK]

I use this method to create Financial statements and post /email them. With a PivotTable I can create a form for a specific client and for a specific Month or all months. The Pivot is part of a Standard Form, with Company headings etc and space for notes if required. Address details of the client can be added using VLookup.

It is possible to perform Mail Merge using Excel, I have an example somewhere from John Walkenbach's site that I will find.

[Anne Troy]

Hi, Roy.
Maybe I should explain.
Mailmerge is one of the simpler features. They've recently screwed it all up in XP and above, but it's still a fairly simple and a feature commonly used by "lightweight" MS Office users...the kind who may not even have Excel installed.

My whole point here is to do it using Word with whatever data source is provided.

However, please DO write a code submission (now or when the submission form becomes available--which is hopefully soon!) to do a *mailmerge* from Excel. It will be an alternative for many!

Thanks!

[JOrzech]

I'm still working on this Anne... got it close but not perfect. Will continue struggling.

[Anne Troy]

JO: We can make it a project if need be. You can PM fumei or Tommy (I think--have a look to see who's in those Word macros) and see if they'll help where you're stuck. That's the coolest thing about this site.

[JOrzech]

I'm not using a macro yet really... that's the funny part. I've almost got it with just Word fields .... but I'm sure a macro will have to intervene ....but thanks for the references. Will certainly call upon them.

I LOVE THIS SITE ANNE!

[JOrzech]

Did you see I curtsied for the Queen DB? That's for you girl!

[Anne Troy]

LOL.
Sure! Steal my smilie!

[JOrzech]

OMG!!!! I didn't even notice! So sorry - I just want to emulate you DB!!! ROFL. What's the saying? Great minds....

[Anne Troy]

Phew. I didn't see it at first.
Was going to yell at you to put it back...

[JOrzech]

Maybe there's some way you can put a crown on yours? And mine will be in a peasant dress? ROFL

[mdmackillop]

How does one submit a sample file?
MD

[Zack Barresse]

When you post, go to "Manage Attachments", click "Browse", Open, Upload.

Post as usual

[Anne Troy]

Oh, MD. Now you've got me excited. LOL!!

[mdmackillop]

Hi Dreamboat,
Its still a bit clunky but ...
Worth pursuing?

Save all files into C:\AMerge; open MergeAll
MD

[Anne Troy]

Well. It's really terrible, MD.

Your code didn't work!!!

I had to change the code to go to Amerge instead of Atest.

AND THEN IT WORKED BEAUTIFULLY.

I just want to check out the rest of the files.

[Anne Troy]

It's freaking gorgeous.
Now, do we leave it as is?
Or attempt to create some kind of template to allow people to build this thing for their custom purposes?
Do we make this a project where you get some help and you be Proj Mgr?
What do you want to do today?
Where do you want to go today?
I love this MD
Do you have ANY idea how many people would kill for it?
They just do NOT want to hear "use Access".

[mdmackillop]

It works fine with small data, but with hundreds of records it gets too slowly. I posted the following in "another place", but have not yet resolved this problem. Any assistance would be appreciated.
MD

I?ve created some code which is attached to a word document to do a compound merge. The database source is in an RTF file, and the first field (of 8 fields) in this document contains data from the ClientID autonumber field in my database. As there are a number of sub-records attached, each ClientID is repeated one or more times, in ascending order. The following code creates an array RS(i,x), where ?i? increments and ?x? is the count of each ClientID entry.
While the code works well for 20 ? 30 records, it takes a long time (70-80 secs.!) to loop through 256 records to create the array. It starts off quickly enough, but slows down exponentially as it progresses (running out of steam, I guess). Can someone suggest a quicker way to create the array. I don?t merge directly from the database because that is very much slower.
MD

'Create array of number of sub-records under each main record
[vba]Do
With ActiveDocument.MailMerge.DataSource
.ActiveRecord = wdNextRecord
If .ActiveRecord = Num Then GoTo ExitLoop
Num = .ActiveRecord
RecNo = ActiveDocument.MailMerge.DataSource.DataFields("ClientID").Value
If RecNo <> Tmp Then
i = i + 1
RS(i, 1) = i
RS(i, 2) = Num
Tmp = RecNo
End If
End With
Loop
ExitLoop:
MsgBox msg[/vba]