MS Access Query to Mail Merge Word

Hi Everyone.

I am using the mail merge function in Word 2007 and getting data from a query in Access, creatings Letters.  It is  contacts table and a subtable for emails.  For instance, one contact in the main table can have multiple email addresses in the subtable, so I get records coming out like this:
ID      Name      Email
1      Bob      bob@gmail.com
1      Bob      bob@yahoo.com

So how can I make the mail merge insert both email adrresses on the template for each contact without repeating the contact.  Like this:

Dear Bob;

On your account we have these addresses:
bob@gmail.com
bob@yahoo.com

Thanks.

______________ (next page, next record)

Dear John,

On your account we have these addresses:
john@gmail.com


Thanks.
_________________________

Can anyone help me please?  I have never used the mail merge so I am confused.

Thanks!

Jetera
jeteraAsked:
Who is Participating?
 
GrahamMandenoConnect With a Mentor Commented:
Hi Jetera
You need to make a query that has only one row per contact, and that has in one field a delimited list of the email addresses:
ID      Name      Emails
1      Bob      bob@gmail.com; bob@yahoo.com
2      Mary     mary@hotmail.com
... etc
There is no built-in method to do this, so you must use a user-defined function such as the one attached below.
You use it in your query like this:
SELECT ID, Name, DList( "Email", "ContactEmailsTable", "ID=" & ID, "; ") AS Emails FROM ContactsTable;
Good luck!  :-)
--
Graham
 

Public Function DList(FieldName As String, DomainName As String, _
  Optional WhereCondition As String, _
  Optional Delimiter As String = ", ") As Variant
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSql As String, sList As String
  On Error GoTo ProcErr
  DList = Null
  sSql = "SELECT DISTINCT [" & FieldName & "] FROM [" & DomainName & "]"
  sSql = sSql & " WHERE ([" & FieldName & "] is not Null)"
  If Len(WhereCondition) <> 0 Then
    sSql = sSql & " AND (" & WhereCondition & ")"
  End If
  sSql = sSql & " order by [" & FieldName & "];"
  Set db = CurrentDb
  Set rs = db.OpenRecordset(sSql, dbOpenForwardOnly)
  Do Until rs.EOF
    sList = sList & rs(0) & Delimiter
    rs.MoveNext
  Loop
  If Len(sList) <> 0 Then
    DList = Left(sList, Len(sList) - Len(Delimiter))
  End If
ProcEnd:
  On Error Resume Next
  If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
  End If
  Set db = Nothing
  Exit Function
ProcErr:
  With Err
    .Raise .Number, .Source, "DList Error:" & vbCrLf & .Description
  End With
  Resume ProcEnd
End Function

Open in new window

0
 
GrahamMandenoCommented:
PS:  I just noticed that you not wanting to use the emails to send a merge email to all the listed addresses, but rather you want to list them one per line.
To do this, use line separator characters (CR + LF) as a delimiter for your list instead of a semicolon:
SELECT ID, Name, DList( "Email", "ContactEmailsTable", "ID=" & ID, Chr(13) & Chr(10)) AS Emails FROM ContactsTable;
--
Graham
0
 
jeteraAuthor Commented:
Hi Graham,

I am just about to try this, but then I realized I am confused again.  Where do I call the function from or how do I get this function to run?  In Word?  (Sorry I never use Word for anything other than basic documents).

Thanks!
Jetera
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jeteraAuthor Commented:
ok wait, I just read your second comment!  So I have it all set up, I saved the above as a module called Dlist.  Now I created the query in sql view and trued to run it and its says "Undefined Function DLIst in expression"... so i am missing something :(
0
 
GrahamMandenoCommented:
You cannot have a function or sub with the same name as a module.  Change the name of the module to mdlDList.
--
Graham
0
 
jeteraAuthor Commented:
Hi graham,

I am really close but now I am getting an error running the query that says
"Ambiguous name. in query expression Dlist(...)"

So I am wondering if in the code I have entered the select statement properly?
I have:
Public Function DList(FieldName As String, DomainName As String, _
  Optional WhereCondition As String, _
  Optional Delimiter As String = ", ") As Variant
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSql As String, sList As String
  On Error GoTo ProcErr
  DList = Null
  'SELECT ID, Name, DList( "Email", "ContactEmailsTable", "ID=" & ID, Chr(13) & Chr(10)) AS Emails FROM ContactsTable
 
  sSql = "SELECT  Distinct [" & Email & "] FROM [" & ContactsEmailTable & "]"

  sSql = sSql & " WHERE ([" & ID& "] is not Null)"
 
  sSql = sSql & " order by [" & ID& "];"
  Set db = CurrentDb
  Set rs = db.OpenRecordset(sSql, dbOpenForwardOnly)
  Do Until rs.EOF
    sList = sList & rs(0) & Delimiter
    rs.MoveNext
  Loop
  If Len(sList) <> 0 Then
    DList = Left(sList, Len(sList) - Len(Delimiter))
  End If
ProcEnd:
  On Error Resume Next
  If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
  End If
  Set db = Nothing
  Exit Function
ProcErr:
  With Err
    .Raise .Number, .Source, "DList Error:" & vbCrLf & .Description
  End With
  Resume ProcEnd
End Function



Do you see anything wrong with it?  Sorry I was late getting back to you, I was away over the Easter weekend.  Thanks!!!!!!
0
 
GrahamMandenoCommented:
You don't need to change the code of DList at all!!
Just use the function, exactly as I gave it to you, and call it from your query:
SELECT ID, Name, DList( "Email", "ContactEmailsTable", "ID=" & ID, Chr(13) & Chr(10)) AS Emails FROM ContactsTable;
--
Graham  
0
 
jeteraAuthor Commented:
Hi Graham,
Ok I copied the code and went back to my query and ran it and still got the same error: Ambiguous name. in query expression Dlist(...)"
I am so close!!!!!  Does it work for you?
0
 
jeteraAuthor Commented:
And I tried to qualify everything in the query as well, as in:
SELECT ContactsTable.ID, ContactsTable.Name, DList( "Email", "ContactEmailsTable", "ID=" & ContactsTable.ID, Chr(13) & Chr(10)) AS Emails FROM ContactsTable;

0
 
GrahamMandenoCommented:
It sounds like there are syntax errors in your code.  Have you compiled it?
In the VB Editor, click on Debug > Compile... and this will tell you if your code contains any errors.
When any errors have been fixed, type this in the Immediate window (below the code) and press <enter>:
?DList( "Email", "ContactEmailsTable", "ID=" & 1, Chr(13) & Chr(10))  
It should give you a list of the email addresses for contact 1.
--
Graham
0
 
jeteraAuthor Commented:
Hi Graham!  I got it to work, it didn't like the brackets, so I took them out and ran it and it worked.  Oh also, I had two modules with different names saved, but calling the same function... so obviously that is why I was getting the ambiguous error...and obviously when I did that I was not working at 100% brain power.

Thanks for all your help!  
0
 
jeteraAuthor Commented:
Excellent answer, thanks for sticking with me while I put it together on my end.
0
All Courses

From novice to tech pro — start learning today.