Mail Merge/ VBA

[Expiry]

I have a table, listing about 500 people that have been issued between 1 and 12 roles each. Each row of the table has a role. So, if Mr Smith has 12 roles, he will appear on 12 rows.

I want to do a mail merge which would send one email to each person that lists their roles. I can't work out how to do that in Word within the mail merge coding, so I think that I have to do some VBA in Excel first to merge the 12 roles into one cell so the mail merge can pick that up and send one email for each person.

Can anyone help with a bit of VBA code for this, or a better way?

Many thanks.

 

[Macropod]

How it's done with a mailmerge depends entirely on how your workbook is configured. Numerous approaches are possible.

For example, you can use Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://www.msofficeforums.com/mail-...d-catalogue-directory-mailmerge-tutorial.html
or:
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

Since the tutorial document includes working field codes and macros (where applicable) for all of its examples, most of the hard work has already been done for you - you should be able to do little more than copy/paste the relevant field codes into your own mailmerge main document, substitute/insert your own field names and adjust the formatting to get the results you desire before running the macro.

Another option would be to use a DATABASE field in a normal ‘letter’ mailmerge main document and a macro to drive the process. An outline of this approach can be found at: http://answers.microsoft.com/en-us/...g-tables/8bce1798-fbe8-41f9-a121-1996c14dca5d
Conversely, if you're using a relational database or, Excel workbook with a separate table with just a single instance of each of the grouping criteria, a DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach can be found at:
https://answers.microsoft.com/en-us...gle-page/4edb4654-27e0-47d2-bd5f-8642e46fa103
For some working examples, see:
http://www.msofficeforums.com/mail-merge/37844-mail-merge-using-one-excel-file-multiple.html
https://www.excelforum.com/excel-general/1273421-merge-excel-list-into-word-receipt.html#post5110813

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
Doug Robbins at https://onedrive.live.com/?cid=5AEDCB43615E886B&id=5AEDCB43615E886B!566

 

[BlueHornet]

This doesn't seem to be so much a problem of "how to do the mail merge", since those are pretty straightforward (or can be) once the data is set up properly. But it does seem that you have "a data problem".

You mentioned a table of "about 500 people" who each have "between 1 and 12 roles each". So I'm trying to visualize what your table looks like, and maybe that's where you're also confused. It sounds like someone tried to kinda sorta half-*** the problem by making a table of names, simply enough, and then just adding on roles in the same single worksheet, column by column, growing the thing like Topsy.

With the two primary tables you should have, one of NAMES with unique identifiers for each named person and another with ROLES, also uniquely identified as to RoleID, Role Name, Duties, etc. you can make another table from a one-to-many query of NAMES (as the driver for the table) with a secondary key of ROLES, permitting the one-to-many listing of each name containing as many roles that each name has. That query will be a "make table" query to build the Names and Roles table whenever you need to re-create it. The table will have as many rows as you have names of people, and each person can have, as you note, up to 12 roles. (This may be what you already have in a flat-file worksheet, but you can probably see already how difficult that is becoming to manage systematically. You really want a fully relational database application for this where you can manage NAMES in one table, ROLES in the other table, and using a form to drive the NAMES AND ROLES make-table query in a more organized fashion.)

The mail merge then becomes a process of listing the name (because every row will have at least one Name) and Role1 (assuming everyone has at least one Role), and then using a series of IF statements after that: If Role2 is not blank, then list it and look at Role3 (etc.) and when a blank Roleₙ is encountered, then on to the next field or next record.

You can probably look up how to do that; it's been too many years since the last mail merge I did in that way, but I have done it (maybe a decade ago; that long).

Still all of this is a lot easier to manage in a relational database, though, and you'll want that to manage the list in the first place.

 

[Expiry]

Thanks, I'll read through both of your posts and work out what to do. Table effectively looks like this:


Name.......Role
John.........Manager
John.........Supervisor
Bill...........Manager
Bob..........Supervisor


From that heavily simplified example, I would want 3 emails to be sent out. John's would say

"Hi, John. You have the following roles in the new company

Manager
Supervisor

Don't mess it up!"

Or something like that.


Just doing a simple mail merge would send John two emails, one for each role.