Create a string from a query result to add into sendObject

[michaelcloss]

Hi! I am trying to create a string to enter as the "To" argument for
sendObject in Access 2000. I want to create this string based on
several different query results. I have one table that contains the
email addresses for several different people in different departments
(i.e. Sales, IT, Finance). I would like to use queries to filter out
the seperate departments and create a string (i.e. strSales) that will
include all of the individuals from the query.

To illustrate, I create a parameter query to filter all of the email
addresses I have stored for Sales. The query results would list "John
Doe", "Jane Doe", "Joe Schmoe" as separate records. I need to capture
those separate records as one string that would read "John Doe, Jane
Doe, Joe Schmoe"; let's call this string strSales. This way I could
enter "strSales" into the "To" argument of sendObject.

This solution would work great as the Admin users could just add or
delete persons from the appropriate table rather than change the VBA.

Any ideas?

 

[Dirk Goldgar]

In

Hi! I am trying to create a string to enter as the "To" argument for
sendObject in Access 2000. I want to create this string based on
several different query results. I have one table that contains the
email addresses for several different people in different departments
(i.e. Sales, IT, Finance). I would like to use queries to filter out
the seperate departments and create a string (i.e. strSales) that will
include all of the individuals from the query.

To illustrate, I create a parameter query to filter all of the email
addresses I have stored for Sales. The query results would list "John
Doe", "Jane Doe", "Joe Schmoe" as separate records. I need to capture
those separate records as one string that would read "John Doe, Jane
Doe, Joe Schmoe"; let's call this string strSales. This way I could
enter "strSales" into the "To" argument of sendObject.

This solution would work great as the Admin users could just add or
delete persons from the appropriate table rather than change the VBA.

Any ideas?

The fConcatChild() function posted here:

http://www.mvps.org/access/modules/mdl0004.htm
Modules: Return a concatenated list of sub-record values

could easily be adapted to the purpose.

 

[Guest]

Hi Michael,

I suggest that you change your posting name, so that your e-mail address is
not exposed to all the scum-of-the-earth spammers. They love to harvest
e-mail addresses from newgroup messages.

This solution would work great as the Admin users could just add or
delete persons from the appropriate table rather than change the VBA.

Good idea, except that I question the part about "appropriate table". All
users should more likely be entered into a single tblPeople table.

You could have a combo box on your form that included a dropdown listing of
available departments. This would allow the user to select the department
that they wanted to send a message to. You could also use a Union query to
include (ALL) at the top of the list, if you wanted to send the same message
to all users simultaneously.

An example of code that can concatentate a list of e-mail addresses is shown
below:

Option Compare Database
Option Explicit

Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

Select Case cboDepartments
Case "(All)"
strSQL = "SELECT EMailAddress FROM tblPeople " _
& "WHERE EMailAddress Is Not Null;"
Case Else
strSQL = "SELECT EMailAddress FROM tblPeople " _
& "WHERE Position = '" & cboDepartments & "' " _
& "AND EMailAddress Is Not Null;"
End Select

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![EmailAddress] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

' Debug.Print BulkEmail

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function


The strSQL statements shown in this function assume that the cboDepartments
combo box uses a text field as it bound column. If on the other hand, you
have a numeric ID, then do not include the single quotes that wrap the
cboDepartments value. In addition, you'll need to modify the SELECT Case
statements for numeric values instead. The row source for your combo box
would be something like this:

SELECT Position FROM tblDepartments UNION SELECT '(All)' FROM tblDepartments
ORDER BY Position


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________