Share via

Many to one email merge using tables

Anonymous
2015-12-23T11:48:07+00:00

So, we have decided that we want to communicate with all of our managers across our business, specifically to check the details of their subordinates to ensure that the reporting structure on our HR platform is correct.  As it stands, all of our emails go out one at a time - which isn't an issue for a leader with 1 or 2 members of staff... but when they have 30 members in their team, that becomes a bit ridiculous.

The extract from our HR platform will be pre-sorted by the Manager ID to ensure it's in a useable format (similar to below):

Manager ID Manager Name Subordinate ID Subordinate Forename Subordinate Surname Subordinate Job Title
12345 Joe Bloggs 11111 Adam Ant Analyst
12345 Joe Bloggs 22222 Bertie Bee Analyst
12345 Joe Bloggs 33333 Colin Caterpillar Specialist
23456 Jane Doe 15151 Derek Dog Field Agent
23456 Jane Doe 16161 Edward Elephant Field Agent

Our aim is, regardless of how many subordinates you have, you only receive 1 email.  We have tried numerous things and have at least managed to get the information on one page for each manager thanks in part to Microsoft's KB294686.  The results for the above would currently show as:

Manager: 12345 (Joe Bloggs)

11111 - Adam Ant - Analyst

22222 - Bertie Bee - Analyst

33333 - Colin Caterpillar - Specialist

--- NEW PAGE ---

Manager: 23456 (Jane Doe)

15151 - Derek Dog - Field Agent

16161 - Edward Elephant - Field Agent

In theory, this approach works - but doesn't fit into any of our communication styles... which causes us issues!  The other problem is, although this works to "individual documents", it doesn't seem to like being processed as emails and splits everything back up again - therefore undoing everything we've done.  We need the following:

  1. The results of each subordinate (could be 1, could be 30) to end up in a table (new row per new subordinate)
  2. The individual groups to be sent in a customised email direct to the line manager

As always, this comes with a few caveats of things we can't do:

  1. We are unable to access any file sharing websites (i.e. Dropbox)
  2. We are unable to download/install/copy any EXE files and a range of other filetypes
  3. We are unable to run any additional software, especially if retrieved from the internet

Unfortunately, our IT policies are extremely strict and this rules out a number of options.  However, we are able to create our own documents and could create our own Macro's with code if required.  I've done a lot of research on Google before getting to this point of asking the question - most of which are regarding download options (i.e. GMayor's Many to One) that are unavailable to us.

Any help would be appreciated!

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Paul Edstein 82,861 Reputation points Volunteer Moderator
2015-12-24T03:36:44+00:00

You could do this with your existing mailmerge main document, but with the mergefields replaced by a DATABASE field that groups the data. Assuming the mailmerge main document is kept in the same folder as the data source, the DATABASE field could be coded as:

{DATABASE \d "{FILENAME \p}/../MM datasource.xlsx" \s " SELECT [Subordinate Forename], [Subordinate Surname], [Subordinate ID], [Subordinate Job Title] FROM [Sheet1$] WHERE [Manager ID] = {MERGEFIELD Manager_ID} ORDER BY [Subordinate Job Title] " \l "15" \b "49" \h}

where 'MM datasource.xlsx' is the data source filename - it could be a database filename, if that's what you use. If the mailmerge main document is NOT kept in the same folder as the data source, you'd need to replace all of "{FILENAME \p}/../MM datasource.xlsx" with the full path & filename (plus the encompassing double-quotes). The \l and \b switches control the table format, while the \h switch insert a table header row - see: https://support.office.com/en-us/article/Field-codes-Database-field-04398159-a2c9-463f-bb59-558a87badcbc?ui=en-US&rs=en-US&ad=US

Note: The field brace pairs (i.e. '{ }') for the above field code are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required.

Then, to drive the process, you could use a macro like:

Sub Merge_Groups_To_Individual_Emails()

Application.ScreenUpdating = False

Dim MainDoc As Document, StrMgrID As String, i As Long

Set MainDoc = ActiveDocument

With MainDoc

  For i = 1 To .MailMerge.DataSource.RecordCount

    With .MailMerge

      .Destination = wdSendToEmail

      .SuppressBlankLines = True

      With .DataSource

        .FirstRecord = i

        .LastRecord = i

        .ActiveRecord = i

      End With

      If .DataSource.DataFields("Manager_ID") <> StrMgrID Then

        StrMgrID = .DataSource.DataFields("Manager_ID")

        .Execute Pause:=False

      End If

    End With

  Next i

End With

Application.ScreenUpdating = True

End Sub

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Paul Edstein 82,861 Reputation points Volunteer Moderator
2016-01-08T10:55:37+00:00

After:

        StrMgrID = .DataSource.DataFields("Manager_ID")

you could insert code like:

        .MailAddressFieldName = "Recipient"

        .MailSubject = "Nominal Team Lists - Please Verify"

        .MailFormat = wdMailFormatHTML

where 'Recipient' is the name of the data field containing the email addresses and 'Nominal Team Lists - Please Verify' is the text of the email subject line.

Was this answer helpful?

0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Doug Robbins - MVP - Office Apps and Services 323.1K Reputation points MVP Volunteer Moderator
    2015-12-24T00:12:20+00:00

    The answer to your question is Yes.

    As far as the code in the template is concerned, it contains information that is valuable to me and I am not prepared to make it available.

    I would suggest that you follow my earlier suggestion to install the add-in on a computer that is isolated from your networks and then make use of the add-in and see how it works and satisfy yourselves that it does no harm to the installation on that computer.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-12-23T14:51:27+00:00

    Hi Doug,

    Thanks for your timely response.

    I've managed to get a copy of the tools and I'm going to see if this meets our requirements.  Can I just ask you quickly whether this will cover the 1st item I mentioned above "The results of each subordinate (could be 1, could be 30) to end up in a table (new row per new subordinate)"?

    EDIT: 23/12/2015 15:00- Sorry, we've hit yet another roadblock.  Although the file has been downloaded and virus scanned, we are unable to view/verify the integrity of the code.  As such, even though we can advise it doesn't have any viruses from a scan perspective - we can't verify that the code isn't doing anything dodgy - and as such we're unable to use.

    Back to the drawing board... any more ideas?

    Thanks

    Was this answer helpful?

    0 comments
  3. Doug Robbins - MVP - Office Apps and Services 323.1K Reputation points MVP Volunteer Moderator
    2015-12-23T12:43:04+00:00

    My MergeTools Add-in that contains a Many to One facility, that can do exactly what you want, is simply a macro-enabled Word template that you would save in the Word Startup folder.  

    To date there have been over 8,500 downloads of it, many of them by very substantial companies.

    I would suggest that you prevail on your IT Department to download it and install it on an isolated machine initially to satisfy themselves that it will not cause any harm to their "precious" network.

    You can download the MergeTools – 20150422.dotm Add-in that I created from the following page of my One Drive: http://bit.ly/1hduSCB

    The MergeTools – 20150422.dotm file needs to be saved in the Word Startup folder.  In Windows Vista and Windows 7, 8 or 8.1, and 10, the default location for that folder is

    C:\Users[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

    If you do not see the AppData folder: -

    In Windows 7, - In Windows Explorer, click on the Organize drop down and then on Folder and search options and in the Folder Options dialog, go to the View tab and select the item "Show hidden files, folders, and drives".  While there, it is a good idea to uncheck the box for "Hide extensions for known file types".

    In Windows 8,  8.1 or 10, in the File Explorer, click on Options on the View tab of the ribbon and then on the View tab in the dialog that appears and select the item "Show hidden files, folders, and drives".  While there, it is a good idea to uncheck the box for "Hide extensions for known file types".

     When that has been done and Word is started\re-started, the tab shown below will be added to the Ribbon:

    The requirements for using the system are:

    1. The mail merge main document must be of the Letter type, though that does not mean that the output cannot be sent as an e-mail message.
    2. For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left.  For the Chart Merge utility, download the Mail Merging with Charts document that is also on that page of my OneDrive for additional requirements of the data source for use with that utility
    3. For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
    4. For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.
    5. For a data source in the form of an Excel worksheet, the data must start in the second row of the worksheet and there should be no empty rows within the range of data that is to be processed.
    6. If there are multiple addressees in either the To or CC fields of the data source, the email addresses need to be separated by a semi-colon.

    NOTE: The MergeTools applications cannot handle “Compound” MergeFields such as the «AddressBlock» or «GreetingLine».  Instead of using those fields, you will need to insert the individual merge fields.

    You may also want to download:

    1. the Merging with Attachments document that is also on that page which explains how the system is used.  It is not actually necessary to have separate attachments as the facility can be used to send just the documents created by the merge itself as attachments, either as the body of the message itself or in the form of Word files or .pdf files.
    2. the Mail Merging with Charts document that is also on that page.  That document explains how you must set up the Excel Data Source and the Mail Merge Main document to be able to execute a merge with a Chart that is unique to each record in the data source.
    3. the Using the Many to One Facility document that describes how to use that facility.

    Was this answer helpful?

    0 comments