E-Mailing list of recipients

[Dave_cha]

Hi folks,

I have a mailling solution in place which builds a list of recipients from a listbox on an open form.

I'd like to modify this to remove the form from the equation and build a list directly from a table. I will need to have the option to filter the records pulled from the table as the recipients selected will need to conform to certain criteria.

The recipient list needs to appear as..."userid1,userid2,userid3....etc"

Does anyone have any suitable code?

Thanks,

Dave

 

[Jon K]

ADO code:-

Dim rst As New ADODB.Recordset
Dim SQL As String
Dim sUserIDs As String

SQL = "Select [UserID] from [TableName] where [Status]='Good'"

rst.Open SQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

sUserIDs = rst.GetString(, , , ",")
sUserIDs = Left(sUserIDs, Len(sUserIDs) - 1)​

(Use your own SQL statement. The Where clause is the place for the criteria.)
.

 

[Dave_cha]

Thanks Jon...that worked perfectly.

Dave

 

[p0welly]

Im trying to use Jon K's code to send text messages, however my database was converted from 97 to 2002 and uses DAO so when I try to use this code I get the error "User-Defined type not defined".

how would I convert this code to DAO or use it in my database

Thanks

 

[Jon K]

Can't find the GetString method in DAO 3.6. Have to use a loop instead.

DAO code:

   Dim rst As DAO.Recordset
   Dim SQL As String
   Dim sUserIDs As String
   
   SQL = "Select [UserID] from [TableName] where [Status]='Good'"
   
   Set rst = CurrentDb.OpenRecordset(SQL)
      
   Do While Not rst.EOF
       sUserIDs = sUserIDs & "," & rst![UserID]
       rst.MoveNext
   Loop
   sUserIDs = Mid(sUserIDs, 2)

.