Concatenate Query Fields into Variable (1 Viewer)

[Adam Caramon]

Hi all,

This should be easy, but I must not be using the correct search terminology.

I have a function that opens a new email message in Groupwise (Email client). This is accessed by clicking a button on a form. The function has a variable "stTo" which I can set like this:

stTo = "test@test.com"

That works so that when the message is opened, the "To:" field is pre-populated with test@test.com. However, I want the stTo variable to be equal to the results of the "CoordinatorEmailAddress" field I have in a query.

I think I need to use a recordset and a SQL SELECT statement, but I could use some help constructing it if that is indeed the way to go.

Here is my attempt so far:

Public Sub SendEmailtoCoordinators()
On Error GoTo ProcErr
 
Dim stMessage as String
Dim stSubject as String
Dim stTo As String
 
stTo = "SELECT DISTINCT tblCoordinator.CoordinatorEmailAddress " & _
"FROM tblRSS INNER JOIN (tblRSSRenewal INNER JOIN tblCoordinator ON tblRSSRenewal.CoordinatorID = tblCoordinator.CoordinatorID) ON tblRSS.RSSID = tblRSSRenewal.RSSID " & _
"WHERE (((tblRSSRenewal.Current)=True) AND ((tblRSS.InActive)=False));"
 
DoCmd.SendObject acSendNoObject, , , stTo, , , stSubject, stMessage, True
 
ProcExit:
Exit Sub
ProcErr:
If Err.Number = 2501 Then
   GoTo ProcExit
End If
MsgBox "Error#" & Err.Number & "." & Err.Description & "- SendEmail_Click"
   Resume ProcExit
End Sub

Any help is appreciated.

 

[DCrake]

From:

Dim stMessage as String
Dim stSubject as String
Dim stTo As String
 
stTo = "SELECT DISTINCT tblCoordinator.CoordinatorEmailAddress " & _
"FROM tblRSS INNER JOIN (tblRSSRenewal INNER JOIN tblCoordinator ON tblRSSRenewal.CoordinatorID = tblCoordinator.CoordinatorID) ON tblRSS.RSSID = tblRSSRenewal.RSSID " & _
"WHERE (((tblRSSRenewal.Current)=True) AND ((tblRSS.InActive)=False));"
 
DoCmd.SendObject acSendNoObject, , , stTo, , , stSubject, stMessage, True

To:

Dim stMessage as String
Dim stSubject as String
Dim Rs As DAO.Recordset
Dim StrSQL As String
Dim stTo As String
 
StrSQL = "SELECT DISTINCT tblCoordinator.CoordinatorEmailAddress " & _
"FROM tblRSS INNER JOIN (tblRSSRenewal INNER JOIN tblCoordinator ON tblRSSRenewal.CoordinatorID = tblCoordinator.CoordinatorID) ON tblRSS.RSSID = tblRSSRenewal.RSSID " & _
"WHERE (((tblRSSRenewal.Current)=True) AND ((tblRSS.InActive)=False));"


Set Rs = CurrentDb.OpenRecordset(StrSQL)

If Not Rs.EOF And Not Rs.BOF  Then
    stTO = Rs("CoordinatorEmailAddress")
 
    DoCmd.SendObject acSendNoObject, , , stTo, , , stSubject, stMessage, True

End If
Rs.Close
Set Rs = Nothing

 

[Adam Caramon]

Thanks for your response DCrake. Your code works like a charm, however, Groupwise does not want to accept multiple items in the To: field. It only gives me the last one once I loop through the code you provided. According to some additional web searching, this is a known issue about Groupwise.

Thanks again.