Using a continuous form to display email recipients

[jjake]

Hello,

I have a table with a list of users [tblUsers]. The last field [EmailSend] is a Yes/No Field

|UserID|UserTag|UserName|UserEmail|EmailSend|


I have a form that that is filled [frm1] then a button that will open a continuous form that displays all the users/Email addresses [frmUsers]. I can select the Yes/No box for each individual, but how do i use this to send an email to these individuals since this field is not different for every user?

 

[isladogs]

Basically do what you described using a query selecting all records in tblUsers where EmailSend = True (yes)
Then depending on how you are sending emails, you may need to concatenate each email address into a long string separated by semicolons
e.g. abc@xyz.com; daffy.duck@google.com; ....

 

[June7]

Is this a multi-user database? If multiple users do this at same time, each selecting records by changing the yes/no field value, they will conflict.

Requires VBA code to build the multi-address string, review http://allenbrowne.com/func-concat.html

 

[jjake]

Is this a multi-user database? If multiple users do this at same time, each selecting records by changing the yes/no field value, they will conflict.

Requires VBA code to build the multi-address string, review http://allenbrowne.com/func-concat.html

Yes it is.

 

[arnelgp]

Either you copy the data from table user to a tmp table abd use that table as the recordsource of continuous form or create an adodb recordset.

 

[jjake]

Either you copy the data from table user to a tmp table abd use that table as the recordsource of continuous form or create an adodb recordset.

Got it. I used a button to create the temp table which provided the record source to the continuous form, then used VBA to run a query for the email section. Then i just use the code to delete the temp table after

Code below for anyone else reading,

Private Sub SendEmailButton_Click()

'DoCmd.SetWarnings False

'DoCmd.OpenQuery "QueryNameHere"

'DoCmd.SetWarnings True

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("QueryNameHere", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
'Number of column in query that holds the email address (2)
If IsNull(.Fields(2)) = False Then
sToName = .Fields(2)
sSubject = "Subject here"
sMessageBody = "Body of message here"



DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing

DoCmd.DeleteObject acTable, "TempTableHere"

End Sub

 

[June7]

Making and deleting table is changing db design and can cause db bloat.

Instead of repeatedly creating and deleting temp table, make the table permanent in the frontend and just INSERT and DELETE records.

 

[jjake]

Making and deleting table is changing db design and can cause db bloat.

Instead of repeatedly creating and deleting temp table, make the table permanent in the frontend and just INSERT and DELETE records.

But don't i have to create the temp table so that the checkboxes can be changed without affecting another user? If i create a front end table and add another user later to the backend, the front end will be out of date

 

[June7]

What? How will the frontend be 'out of date'? Instead of creating/deleting table, the table is permanent and records are inserted/deleted. I use this approach. Users come and go. Has no impact on the process.

However, how large is recordset?

 

[jjake]

Because i was using the temp table for the recordsource of the continuous form. This would have the memory of the check box wouldn't it?

I did create the front end table though as suggested and used an append query and delete query to update the table which populates my email list.

 

[June7]

Yes, form is bound to frontend 'temp' table so each user is editing an independent version of the data. The check box is bound to yes/no field in the temp table, not the shared table in the original backend.

Suggest no need to add every field to the temp table. Build temp table with 2 fields - UserID and EmailSend. The form RecordSource will be a query that joins the temp table to the original table to retrieve and display related data. Eliminate the EmailSend field from the original table.

 

[jjake]

June,

I created the local table [tblemailsend] and created and append query that runs when the user form is opened that list all the users. This works great, but there seems to be a slight problem when the check box is selected in the form. The results do not match up to what is selected.

e.g

open form. select cb 1, table does not update. select cb2, table only updates record 1. Select cb3, table only update record 2.

It seems there is a 1 record delay from form to table. any idea?

 

[June7]

Record is committed to table when: 1. close/table/query/form; or 2. move to another record, or 3. run code to save record. So probably # 2 applies to your situation.

 

[jjake]

Got it. I just added a refresh command on the Onclick event of the yes/no box.

another thing,

I'm trying to use the following code to refrence the table that the fields are insert into (tblEmailSend).

Dim rst As DAO.Recordset
Dim strEmailAddress

Set rst = CurrentDb.OpenRecordset("TblEmailSend")

Do Until rst.EOF
  strEmailAddress = strEmailAddress & rst("UserEmail") & ","
  rst.MoveNext
Loop

strEmailAddress = Left(strEmailAddress, Len(strEmailAddress)-4)

DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False

rst.Close
Set rst = Nothing

How would i modify this code to only send to the recipients who have a True value in the yes/no field.

The (UserEmail) field is in column 4 and the Yes/No field (EmailSend) is in column 7