Populate Multiple Email Addresses

[DMerchen]

Please excuse me for this post, but I could definitely use some assistance. I have searched through the forums and I am still struggling. I am trying to populate multiple email addresses based on a table and a yes/no check box. If the checkbox is checked, they receive an email. I have a table with Employee/email combobox with the second column being the email address, and a checkbox. What is the best way to accomplish this? I hope I have worded this properly for others to understand.
[FONT=&quot]
[/FONT]Any help is greatly appreciated. If there is a better way, I am all ears.

 

[Cronk]

I open a recordset with email addresses I want and then loop through it, appending the address and separator to a string variable.

 

[DMerchen]

I have a table with employee, email, and a checkbox field. If the checkbox is checked I want to add the email to a string so I can send out an email to multiple recipients. I have tried this code with no success. What am I missing? I am still struggling with recordsets.

Dim strEmail As String
Dim rsEmail

rsEmail = "Select Employee from tblEmailNotifier Where RMA1=True"

Dim Recordset
Set Recordset = CurrentDb.OpenRecordset(rsEmail)
Do Until Recordset.EOF
        strEmail = strEmail & ";" & rsEmail
        Recordset.MoveNext
Loop
Recordset.Close
Set Recordset = Nothing

 

[GinaWhipp]

Hmm, try...

Dim rs As Recordset
Dim strEmail As String
 
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblEmailNotifier Where RMA1=True")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!YoureMailAddressField) Then
strEmail = strEmail & rs!YoureMailAddressField & ";"
rs.MoveNext
Else
rs.MoveNext
End If
 
Loop Until rs.EOF
 
rs.Close
Set rs = Nothing

And I'm assuming the eMail Address field is in the above mentioned table.

 

[DMerchen]

Thanks for the reply! It is in the table mentioned, but it is part of a dropdown. It is the second column in a dropdown with the employee name in the first column. This is the only way I could come up with to make this work, perhaps you have other ideas. I have a linked table with all employee information (we use this to maintain one database for emplyees instead of a table in each database), and I built another table based on employee name and email together in a dropdown. In the table I have other columns with checkboxes to determine if the person receives an email or not. Is there a better solution to get this because I am having a difficult time getting the email out of that second column? Your help is greatly appreciated!!

 

[DMerchen]

Getting an error on this line. Not sure why.

Dim rs As Recordset

It says User-defined type not defined.

 

[GinaWhipp]

Hmm, try...

Dim rs As DAO.Recordset

If that still doesn't work post the entire section as you have it now.

 

[GinaWhipp]

The rountine does not need to be based on the Record Source for the Form or the Row Source for the Combo Box. It can be based off of a query that gives you the information you need.

 

[DMerchen]

Silly me!! I didn't have the object library selected in references!! My bad!

 

[GinaWhipp]

No problem, happens to the best of us!

 

[DMerchen]

So this is getting me the index of the employee, but not the text or the email.

Dim rs As Recordset
Dim strEmail As String
 
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblEmailNotifier Where RMA1=True")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!Employee) Then
strEmail = strEmail & rs!Employee & ";"
rs.MoveNext
Else
rs.MoveNext
End If
 
Loop Until rs.EOF
 
rs.Close
Set rs = Nothing
End If

 

[GinaWhipp]

Well the first thing you need is to create a query that pulls what you want. This should help...

http://www.access-diva.com/vba16.html

Look at the query in the purple square.

 

[Cronk]

What field stores the email address?

If the Employee field in the table holds something other than the email address, then you will have that data in your string.

Maybe the email address is stored in a field called Email in which case use
rs!Email

 

[DMerchen]

The attached image shows how I have my table set up. I set it up this way because of pulling the employee and email from the linked table. Basically, based on the checkboxes, I want to send an email if the checkbox is checked. I have several steps, and there is a checkbox assigned to each step and then the recipient would receive the email. Hopefully this makes sense. Perhaps this is not the best way to do this. Thanks again for the help.

 

[GinaWhipp]

Oh dear, just another reason not to use Look-Up Fields in tables. Never use them so this is untested, try changing the...

rs!Employee to rs!Employee.Column(2)

 

[DMerchen]

So, can you help me to learn what would be a better method. Since I have a linked table I can't add my checkboxes to this table, correct? What would be a better method? Any thoughts? I will give what you suggested a try and begin looking at other things as well. There has to be a better way!! Thanks! You are all too kind and helpful to this struggling database programmer.

 

[GinaWhipp]

The better BEST way would be to store eMail addresses in the same table just a separate field. You can keep your check boxes, no one said they have to go away!

 

[DMerchen]

I was thinking the same thing last night on my drive home. I will give this a try and let you know how it goes. Again, thanks for the assist!

 

[GinaWhipp]

When you're ready you can post your table outlines. We will be more than happy to help.

 

[DMerchen]

That got it easy enough. Just stored the email in a separate column and everything ran great. I have tested the checkboxes and everything seems to work as planned!! Thank you all for your assistance!!