PDA

View Full Version : Multiple-Record Word Mail Merge



Anne Troy
05-23-2004, 02:11 PM
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/cindymeister/mergfaq1.htm#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
05-26-2004, 03:39 AM
Why not record your data in Excel and use a PivotTable - much easier!

Anne Troy
05-26-2004, 07:12 AM
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
05-28-2004, 11:40 PM
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
05-29-2004, 12:52 AM
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
06-05-2004, 06:41 AM
I'm still working on this Anne... got it close but not perfect. Will continue struggling.

Anne Troy
06-05-2004, 06:58 AM
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
06-05-2004, 07:06 AM
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
06-05-2004, 07:37 AM
Did you see I curtsied for the Queen DB? That's for you girl!

Anne Troy
06-05-2004, 07:45 AM
LOL.
Sure! Steal my smilie!
:)

JOrzech
06-05-2004, 07:49 AM
OMG!!!! I didn't even notice! So sorry - I just want to emulate you DB!!! ROFL. What's the saying? Great minds....

Anne Troy
06-05-2004, 07:56 AM
Phew. I didn't see it at first.
Was going to yell at you to put it back...

JOrzech
06-05-2004, 08:03 AM
Maybe there's some way you can put a crown on yours? And mine will be in a peasant dress? ROFL

mdmackillop
06-09-2004, 12:36 PM
How does one submit a sample file?
MD

Zack Barresse
06-09-2004, 12:53 PM
When you post, go to "Manage Attachments", click "Browse", Open, Upload.

Post as usual :)

Anne Troy
06-09-2004, 01:00 PM
Oh, MD. Now you've got me excited. LOL!!

mdmackillop
06-09-2004, 01:08 PM
Hi Dreamboat,
Its still a bit clunky but ...
Worth pursuing?

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

:type

Anne Troy
06-09-2004, 01:28 PM
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
06-09-2004, 01:30 PM
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
06-09-2004, 01:41 PM
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
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

Anne Troy
06-09-2004, 01:45 PM
Cool. Learn how to use our VBA tags here (http://www.vbaexpress.com/vbatags.htm), MD. So great to have you here!

mld4165
06-17-2004, 05:13 AM
MD

Can you give me a little background on how this works?:dunno

I have a project that if I could modify your code and templates it would work.

If that is OK?

mld4165

mdmackillop
06-17-2004, 09:02 AM
Hi,

This has been a long term project with long periods of idleness and occasional spurts when I learn how to do things. Basically the Document is in four parts

1. Mergeall contains the Start of the document and leads into the list of NextRecord mergefields. It also contains all the macro coding

2. MergeHead contains the Start of the letter only

3. Mergefoot contains the letter footer, which may contain further merge records.

4. MergeData.rtf contains the data to be merged. One essential field is an ID for each Addressee or whatever.

The coding works by creating and index at the change of ID, going to a bookmark at the first NextRecord, counting down and inserting a MergeFoot, followed by a MergeHead, and repeating this process for each change in ID.

As a way forward, I am investigating analysing the basic mailmerge document which would contain one set of NextRecord records, and creating a set of Mhead, Mrec and Mfoot Autotext entries. These would be pasted into MergeAll as required, to form the basis for the new document.

One hiccup is the indexing of the records from the DataSource. While this works fine for 20 ? 30 records, it takes minutes for 250 or so records. A different mechanism is required here I think.

Anne Troy
03-30-2005, 09:29 PM
I moved that recent post to here: http://www.vbaexpress.com/forum/showthread.php?t=2570

OBP
04-21-2005, 11:08 AM
md, can you you use
[VBA]
Do
With ActiveDocument.MailMerge.DataSource
.ActiveRecord = wdNextRecord
If .ActiveRecord = Num Then Goto ExitLoop
Num = .ActiveRecord
If Tmp <> ActiveDocument.MailMerge.DataSource.DataFields("ClientID").Value Then
i = i + 1
RS(i, 1) = i
RS(i, 2) = Num
Tmp = ActiveDocument.MailMerge.DataSource.DataFields("ClientID").Value
End If
End With
Loop
ExitLoop:
MsgBox msg
[VBA]
and what does this do ?
If .ActiveRecord = Num Then Goto ExitLoop
Num = .ActiveRecord

mdmackillop
04-21-2005, 01:08 PM
Hi,

I'm occupied elsewhere just now, but will try to have a look at it soon.

Regards

MD

xanlythe
04-13-2010, 08:56 PM
Hey there all. forum N00b here and was hoping someone can point me to where the files are that MD uploaded? Trying to help wife with this issue for her work. They have office 2k7 and hoping this will work for her.

Xanlythe

VitoHGrind
08-18-2010, 08:15 AM
Second the post above. I'm new here, but I believe this would solve my problem.

Xanlythe, did you ever solve this issue via another method?

macropod
09-21-2010, 04:00 AM
For a non-vba solution (in most cases), check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/index.php?showtopic=731107 (wlmailhtml:{C7EF91AA-B2C7-45B8-98CA-046449FCFFF9}mid://00000027/!x-usc:http://lounge.windowssecrets.com/index.php?showtopic=731107)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip (wlmailhtml:{C7EF91AA-B2C7-45B8-98CA-046449FCFFF9}mid://00000027/!x-usc:http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip)
Do read the tutorial before trying to use the mailmerge document included with it.