Output a query Email List From Table

[Robert_DubYa]

I would like to populate an email distribution string from an existing table.
With this I mean I want to send a query to a list of email addresses in a
table. I know how to send the output of a query out if the distribution is
"hard coded" (meaning I write the address into the code). Any ideas on how
to use a table to get the addresses? I was going to use a list box and loop
through each, and not have the list box visible on the form I am using, but I
want to learn how to do this from a table if possible.

Any help would be apperciated,
Robert

 

[Douglas J. Steele]

Dim rsCurr As DAO.Recordset
Dim strAddresses As String
Dim strSQL As String

strSQL = "SELECT DISTINCT EMailAddress FROM MyTable"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
Do Until rsCurr.EOF
strAddresses = strAddresses & rsCurr!EmailAddress & "; "
rsCurr.Next
Loop
rsCurr.Close
Set rsCurr = Nothing
If Len(strAddresses) > 0 Then
strAddresses = Left$(strAddresses, Len(strAddresses) - 2)
End If

 

[Beetle]

You can loop through a recordset instead of a
hidden list box. Here is an example. This is air
code so there may be some typos.

Dim Db As DAO.Database
Dim rst As DAO.Recordset
Dim strList As String

Set Db = CurrentDb
Set rst = Db.OpenRecordset ("tblYourTable", DbOpenSnapshot)

strList = ""

With rst
Do While Not .EOF
If Nz(![EmailField], "") <> "" Then
If strList = "" Then
strList = strList & ![EmailField]
Else
strList = strList & "; " & ![EmailField]
End If
End If
.MoveNext
Loop
End With

Cleanup:
Db.Close
rst.Close
Set Db = Nothing
Set rst = Nothing

 

[Douglas J. Steele]

<picky>

Why not

strSQL = "SELECT EMailField FROM tblYourTable " & _
"WHERE Len(EMailField & vbNullString) > 0"

Set rst = Db.OpenRecordset (strSQL, DbOpenSnapshot)

</picky>
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can loop through a recordset instead of a
hidden list box. Here is an example. This is air
code so there may be some typos.

Dim Db As DAO.Database
Dim rst As DAO.Recordset
Dim strList As String

Set Db = CurrentDb
Set rst = Db.OpenRecordset ("tblYourTable", DbOpenSnapshot)

strList = ""

With rst
Do While Not .EOF
If Nz(![EmailField], "") <> "" Then
If strList = "" Then
strList = strList & ![EmailField]
Else
strList = strList & "; " & ![EmailField]
End If
End If
.MoveNext
Loop
End With

Cleanup:
Db.Close
rst.Close
Set Db = Nothing
Set rst = Nothing

--
_________

Sean Bailey

I would like to populate an email distribution string from an existing
table.
With this I mean I want to send a query to a list of email addresses in
a
table. I know how to send the output of a query out if the distribution
is
"hard coded" (meaning I write the address into the code). Any ideas on
how
to use a table to get the addresses? I was going to use a list box and
loop
through each, and not have the list box visible on the form I am using,
but I
want to learn how to do this from a table if possible.

Any help would be apperciated,
Robert