Mail Merge - of table

Say, I've an Access table in Access 2013 and they structure is as follows : Name, Surname and Volunteer.
The Name and Surname are repeated exactly but Volunteer changes at each instance ie. for each name and surname there are a number of volunteers.  Sample table follows :
Name Surn   Volunteer
Peter  Smith  Harry
Peter Smith   Jeff
Peter Smith    Archie
William Jones  Shaun
William Jones Neville

Say, How can I use Word 2013 Mail Merge to Merge a single document for each unique name with the corresponding Volunteers? I was thinking of creating a 2nd table with the names and joining the two tables with the query as data source for the merge document. Would this work? How do I merge from a query?
shaunwinginAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
Are you expecting to get two documents from your sample, like this:
------------------------------
Name: Peter Smith
Volunteers:
                Harry
                Jeff
---------------------------
Name: William Jones
Volunteers:
                Shaun
                Neville
---------------------------

If so, be aware that the mail merge basic design expects to work with a single table-like structure. Yours is a one-to-many situation requiring two tables to be joined in an hierarchical dataset.

There are several workarounds discussed in the following link. The best depends on your exact situation and skill set.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_23658368.h
Jeffrey CoachmanMIS LiasonCommented:
Ultimately you would need two tables:

tblEmp
eID
eFN
eLN

tblVolunteers
vID
v_eID
eFN
eLN

Then you can join them in a query
Then create a "Grouped" report
Jeffrey CoachmanMIS LiasonCommented:
Sample db attached.

Note the you can also do things like:
Moving the filed around
concatenating the first and last names
adding headings
having counts of volunteers
...etc

But I have presented the basics here...

JeffCoachman
Database51.mdb
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

shaunwinginAuthor Commented:
Jeffrey Coachman, much appreciated.

Group Report is in ms access I presume. This is an option but prefer to use word 2013 as allows easier formatting of the document. Can you explain these steps please? I also need to save each page as a unique file name by ID no so that GroupMail can pick up the file and attach to an email.
Tx
Jeffrey CoachmanMIS LiasonCommented:
Then you have misunderstood what "Merge" means in word.

Mail Merge will "Merge" the existing data into a Word document.
It will not "Merge" the data itself.

So in your case, you cannot use word mail merge alone to get the output you specified.
The Merge data must be in a table format.
One to many relationships (one Employee/many Volunteers), in the format you desire, are not directly supported.

So even if you had two related tables, ...word cannot "Hide" the repeating Employees.

There may be a way to do this in Word, ...but I don't know of anything off hand.

You can do this in Access, ...but it is more of a "Procedure", ...not really steps you can follow like Mail Merge.

I can modify my sample and send you some notes if you like.

let me know.

Jeff
GrahamSkanRetiredCommented:
Note that it can be done in mail merge, but it is tricky. The link on my comment above is to an earlier question where some methods are outlined. One is a suggestion from Microsoft involving the use of Word field logic. The details are here:
http://support.microsoft.com/kb/294686/
shaunwinginAuthor Commented:
Seems Access is simplest way to go.
How can one print a report to pdf - each new page in the report to be printed as a new file. the file name to be the ID. This way can use GroupMail to send off the attachments to their respective email addresses.
Jeffrey CoachmanMIS LiasonCommented:
One step at a time...

If you want to go the Access route, then will each "Employee/Volunteers" will be on a page?
If so, then we can loop the Employees and print a PDF report for each employee/volunteer.

Does this sound like what you want?
If so, I can whip up something quick by this afternoon.

JeffCoachman
shaunwinginAuthor Commented:
tx, yes the Access Route seems simplest and as you've described - but open to any other suggestions as to how to go about this.

These are great suggestions:

concatenating the first and last names
adding headings
having counts of volunteers

tx
Jeffrey CoachmanMIS LiasonCommented:
all doable...
shaunwinginAuthor Commented:
Would appreciate if can wip it together please Jeffrey. PS this is to assist us to send Volunteers to visit Senior Citizens. Shaun
Jeffrey CoachmanMIS LiasonCommented:
Post a clear graphical example of the *exact* output you are seeking, and I'll have a sample posted 8 hrs later...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamSkanRetiredCommented:
I am speaking as an OAP, and a Word expert, so I hopethat my contribution s acceptable.

Jeffrey's suggestion is the simpler alternative between a difficult Mail Merge and an  Access report. However, if you want the superior formatting possibilities of Word then you can import the Access report into Word and then apply your custom formatting.
Jeffrey CoachmanMIS LiasonCommented:
starting point:
samle
shaunwinginAuthor Commented:
Can you suggest a simple graphic tool to post a clear picture of what is required?
shaunwinginAuthor Commented:
Perhaps you can re-read my requests and incorporate your suggestions these are great. If you send me the report - I can modify it accordingly to tailor it. tx Shaun
Helen FeddemaCommented:
I would recommend using another method than mail merge.  You need to merge data from a main table and a linked table (since the tables are not distinct, you can use queries to separate the data into two groups).  See my old Four Ways to Merge to Word sample database:

http://www.helenfeddema.com/Files/code24.zip

or my recently updated 4th edition of my Working with Word ebook (available from the Office Watch Website), which covers Office versions through 2013.

One of the examples demonstrates merging from linked tables, using doc properties and TypeText.
shaunwinginAuthor Commented:
Tx Helen
Can you perhaps send a sample implementation using the data I supplied?
Helen FeddemaCommented:
That is a very small data sample -- I can add some more rows for testing.  I will see if I can come up with something later today.
Helen FeddemaCommented:
Here is the code:

Public Function CreateWordDocs()
'Written by Helen Feddema 12-Aug-2015
'Last modified by Helen Feddema 12-Aug-2015

   Dim appWord As Word.Application
   Dim blnSaveNameFail As Boolean
   Dim doc As Word.Document
   Dim docs As Object
   Dim fil As Scripting.File
   Dim fso As New Scripting.FileSystemObject
   Dim intCount As Integer
   Dim intReturn As Integer
   Dim lngCount As Long
   Dim lngPersonID As Long
   Dim prps As Object
   Dim rstPeople As DAO.Recordset
   Dim rstVolunteers As DAO.Recordset
   Dim strDefaultDocsPath As String
   Dim strDefaultTemplatesPath As String
   Dim strDoc As String
   Dim strDocsPath As String
   Dim strFirstName As String
   Dim strLastName As String
   Dim strMessage As String
   Dim strMessageTitle As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strSaveDateFormat As String
   Dim strSaveName As String
   Dim strSaveNamePath As String
   Dim strShortDate As String
   Dim strTemplateName As String
   Dim strTemplateNameAndPath As String
   Dim strTemplatesPath As String
   Dim strTest As String
   Dim strTestFile As String
   Dim strTitle As String
   Dim strSQL As String
   Dim strVolunteer As String
   
On Error GoTo ErrorHandler
   
   'Create a Word instance to use for the invoice; uses the existing Word
   'instance if there is one, otherwise creates a new instance
    Set appWord = GetObject(, "Word.Application")
   
   'Keep Word invisible until the document is finished
   'appWord.Visible = False
   
   'Get standard Docs and Templates paths from Word
   '(saved from controls on main menu)
   strDocsPath = appWord.Options.DefaultFilePath(wdDocumentsPath)
   strTemplatesPath = appWord.Options.DefaultFilePath(wdUserTemplatesPath)
   strTemplateName = "People and Volunteers.dotx"
   
   strTemplateNameAndPath = strTemplatesPath & "\" & strTemplateName
   Debug.Print "Template name and path: " & strTemplateNameAndPath
   
   'This date string is used in creating the invoice's save name
   strShortDate = Format(Date, "d-m-yyyy")
   
On Error Resume Next
   'Check for existence of template in templates folder,
   'and exit if not found

   Set fil = fso.GetFile(strTemplateNameAndPath)
   If fil Is Nothing Then
      strTitle = "Template not found"
      strPrompt = "Can't find " & strTemplateName & " in " _
         & strTemplatesPath & "; canceling"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   End If
   
On Error GoTo ErrorHandler
   
   Set docs = appWord.Documents
   
   'Create recordset and get needed doc properties for this invoice
   Set rstPeople = CurrentDb.OpenRecordset("qryPeople")
   
   With rstPeople
      Do While Not rstPeople.EOF
         Set doc = docs.Add(strTemplateNameAndPath)
         lngPersonID = Nz(![PersonID])
         'Debug.Print "Person ID: " & lngPersonID
         strFirstName = Nz(![FirstName])
         strLastName = Nz(![LastName])
         
         'Write information to Word custom document properties from
         'previously created variables
         Set prps = doc.CustomDocumentProperties
         prps.Item("FirstName").Value = strFirstName
         prps.Item("LastName").Value = strLastName
         
         'Highlight the entire Word document and update fields, so the data
         'written to the custom doc props is displayed in the DocProperty fields
         With appWord
            .Selection.WholeStory
            .Selection.Fields.Update
            .Selection.HomeKey Unit:=wdStory
         End With
         
         'Create filtered recordset of volunteers for this person
         strRecordSource = "qryVolunteers"
         strQuery = "qryFilteredVolunteers"
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[PersonID] = " & lngPersonID & ";"
         Debug.Print "SQL for " & strQuery & ": " & strSQL
         lngCount = CreateAndTestQuery(strQuery, strSQL)
         'Debug.Print "No. of items found: " & lngCount
         Set rstVolunteers = CurrentDb.OpenRecordset(strQuery)
            
         'Fill table with volunteer names
         'Go to table to fill with Details data
         appWord.Selection.GoTo What:=wdGoToTable, _
            Which:=wdGoToFirst, _
            Count:=1, _
            Name:=""
         '.MoveDown Unit:=wdLine, Count:=1
         
         Do While Not rstVolunteers.EOF
            strVolunteer = rstVolunteers![Volunteer]
            With appWord.Selection
               .TypeText Text:=strVolunteer
               .MoveRight Unit:=wdCell
            End With
            rstVolunteers.MoveNext
         Loop
         
         'Delete last, empty row
         appWord.Selection.SelectRow
         appWord.Selection.Rows.Delete
         
         'Check for existence of previously saved letter in documents folder,
         'and append an incremented number to save name if found
         strSaveName = strFirstName & " " & strLastName & " Volunteers on " _
            & strShortDate & ".docx"
          
         intCount = 2
         blnSaveNameFail = True
         
         Do While blnSaveNameFail
            strSaveNamePath = strDocsPath & "\" & strSaveName
           Debug.Print "Proposed save name and path: " _
               & vbCrLf & strSaveNamePath
            strTestFile = Nz(Dir(strSaveNamePath))
            If strTestFile = strSaveName Then
               
               'Create new save name with incremented number
               blnSaveNameFail = True
               strSaveName = strFirstName & " " & strLastName _
                  & " Volunteers on " & CStr(intCount) _
                  & strShortDate & ".docx"
         
               strSaveNamePath = strDocsPath & "\" & strSaveName
               intCount = intCount + 1
            Else
               blnSaveNameFail = False
            End If
         Loop
         
         doc.SaveAs strSaveNamePath
            
         .MoveNext
      Loop
      
   End With
   
   
ErrorHandlerExit:
   Set fil = Nothing
   Set appWord = Nothing
   'Close any open recordsets, in case code stops because of an error
   On Error Resume Next
   rstPeople.Close
   rstVolunteers.Close
   Exit Function

ErrorHandler:
   If Err = 429 Then
      'Word is not running; open Word with CreateObject
      Set appWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " _
         & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 24-Feb-2009

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Function

Open in new window


I am attaching a little database, and a very simple template that is used to create the Word docs.  The code is cut down from some in my Working with Word 2013 sample database, which has lots more stuff going on, such as selecting the Docs and Templates path from the main menu; here I just use the default directories.
People-and-Volunteers.dotx
Helen FeddemaCommented:
I tried repeatedly, but EE wouldn't upload the small .accdb database, either as is or zipped.  I will try to upload it later today, or maybe I could email it to you.
Helen FeddemaCommented:
Still won't upload here -- try this link:

http://www.helenfeddema.com/Files/TestMerge.accdb
shaunwinginAuthor Commented:
Link giving an error I'm afraid
Helen FeddemaCommented:
Trying to upload here once more.
Test-Merge.accdb
Helen FeddemaCommented:
It is working today!
shaunwinginAuthor Commented:
Tx. Downloaded and hope to get to the PC with ACCESS soon.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.