Help with mail merge

[Guest]

Hey there, thanks for the reply earlier. I am trying to mail merge using
Outlook 2003. I am sending an email to 100 people and Outlook lets you select
almost all the fields you want from Access.

However I am having a problem merging results from particular customers. An
example of this would be...

In the Access database I have 100 customers. Each customer has 5 results
which are listed below.

If I create a repeat region using ASP in my website I get the following
result.

Name: Bob
Product: Cars, Trees, Trams, Animals, Houses
Email: (e-mail address removed)

When I create a mail merge I get the following result.

Name: Bob
Product: Cars
Email: (e-mail address removed)

Name: Bob
Product: Trees
Email: (e-mail address removed)

Name: Bob
Product: Trams
Email: (e-mail address removed)

Name: Bob
Product: Animals
Email: (e-mail address removed)

Name: Bob
Product: Houses
Email: (e-mail address removed)

This also means that if I go ahead with the mail merge email, my client
receives five seperate emails.

Can anyone help with this issue?

If not clear please ask any questions.

Cheers
Mally.

 

[Arvin Meyer [MVP]]

Here's some code written by Dev Ashish which will concatenate all the values
from the many side table into a single string. That should allow 1 email per
customer:
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

(Q) How can I extract all values of a field from a table which is the
related to another table in a 1:M relationship?

(A) The following function will return a semi-colon separated list from
the "Many" side table for a given Primary Key value from the "One" Side of
the table.

'************ Code Start **********
'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish

Function fReturnStr(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fReturnStr("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fReturnStr

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fReturnStr
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fReturnStr = Left(varConcat, Len(varConcat) - 1)

Exit_fReturnStr:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fReturnStr:
Resume Exit_fReturnStr
End Function
'************ Code End **********