Many to one email merge using tables

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!

 

Question Info


Last updated April 22, 2019 Views 5,613 Applies to:
Answer

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

Cheers,
Paul Edstein
[MS MVP - Word]

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer

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.

Cheers,
Paul Edstein
[MS MVP - Word]

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.