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?
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.

GrahamMandenoCommented:
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

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
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.