Multiple Names In A Mail Merge Field

  • Thread starter Thread starter Traci
  • Start date Start date
T

Traci

I need to do a mail merge letter from my database. The letter will be addressed
to small companies and in the body of the letter I need to list employees of the
company. There will be from 1 to 15 employees. My database is all set up to
provide the names and addresses of the companies and the list of employees of
each company. I need help on how to do the employee merge field when there will
be multiple employees to be listed in this field.

Anyone?

Thanks!

Traci
 
Probably the easiest way is to create the form letter in Word and use Word
to connect to the table or query that contains the information you need.
After making the connection from Word to Access and specifying a table or
query, you will have access to the fields and able to place them in the
appropriate locations in your letter. See Merge under Word.

You could also create a report in Access using the fields you need placed in
the area's where needed to create your form letter.
 
Thanks for the quick response!

Yes, I have all that! My question is how to merge a list of names into the merge
document. How do I set up the merge field to be able to merge the list of names?

Traci
 
Hi Traci

You can write a function which opens a recordset of the employees for the
given company (passed as an argument) and creates a string which is a list
of all the names. For example:

Function ListEmployees(CompanyID as long) as String
Dim rs as DAO.Recordset
Dim sList as String
Set rs = CurrentDb.OpenRecordset( _
"Select * from Employees where EmpCompany=" & CompanyID, _
dbForwardOnly)
With rs
Do until .EOF
sList = sList & !FirstName & " " & !LastName & vbCrLf
.MoveNext
Loop
.Close
End With
Set rs =- Nothing
' remove last Cr/Lf
ListEmployees = Left(sList, Len(sList)-2)
End Function

Now you can use this function in an expression in your query:
EmployeeList: ListEmployees([CompanyID])
....and then use the field EmployeeList as a merge field in your document.

This function lists one employee per line, but you might want to list them
some other way, say as a comma-separated list.
 
Back
Top