Loop through form checkboxes to send emails

I have a form that has 30 different checkboxes.  Any number of the checkboxes can be checked (true) by the user from 1 of them through all 30 of them.  Each checkbox is "related" to an email address in the database.  For example, checkbox1, if true, is to indicate that an email should be sent to the email address related to checkbox1.

I have a command button on the form labeled "Execute Email Blast".  When the command button is clicked I want to have a routine execute that will send an email to each checked email address.

I have no idea how to begin to write this code.  Can someone help?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
I'm assuming that the checkboxes are in a continuous form and that the recordsource of this form has a field holding your email addresses and another field bound to your checkbox.  Pretending that bound field is named SELECTED, the code to loop through and create your SendTo string would look something like this:
    Dim rs As Recordset
    Dim strSendTo As String
    
    Set rs = CurrentDb.OpenRecordset("Select EmailAddress From NameOfTable Where Selected = True")
    Do While Not rs.EOF
        strSendTo = strSendTo & ", " & rs!EmailAddress
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    'remove comma at start of string
    strSendTo = Mid(strSendTo, 3)
    

Open in new window

You would of course follow this with the code to send your email.

Ron
PatHartmanCommented:
I really hope that your table is designed the way Ron assumed it was.  If it is not designed that way, perhaps you should consider making the change before proceeding.

If you leave it as a flat structure, I don't see any looping opportunity.  It sounds like chkbox1 is going to have to send email to address1@yourplace.com.  In that case, you'll have 30 separate if statements and if you find you need to add an address or change one, you'll be making code changes to the form.  Normalization is always easier in the long run.
John TsioumprisSoftware & Systems EngineerCommented:
If think this code will do the job...just call it from a button click
Private Function MailBlast()

Dim ctl as control

for each ctl in me.controls
if ctl.ControlType = acCheckBox then
if ctl =true then
'The routine that triggers the mail according to selection probably you search from a table that matches checkbox name to e-mails e.g chk01 --> mymail.com

end if
end if
next ctl
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SteveL13Author Commented:
I'm going to try to use:

Private Function MailBlast()

Dim ctl as control

for each ctl in me.controls
if ctl.ControlType = acCheckBox then
if ctl =true then
'The routine that triggers the mail according to selection probably you search from a table that matches checkbox name to e-mails e.g chk01 --> mymail.com

end if
end if
next ctl
End Function

Open in new window


But I only want the email to execute if the checkbox is true.  So out of 30 checkboxes maybe 3 are checked or true.  In that case only the true ones should execute.  How can I change the code to work this way?
John TsioumprisSoftware & Systems EngineerCommented:
the loop first checks if the control is check box and if it is, checks if it is true,,,so according to your needs if 3 out 30 are checked the 3 checked ones will "trigger" the routine that sends emails...am i  missing something?
SteveL13Author Commented:
You are not missing anything.  But for some reason, I have 3 checkboxes checked.  But when I run the command button code with a msgbox inserted I get 4 messages.

Code:

Private Function MailBlast()

Dim ctl As Control

For Each ctl In Me.Controls

If ctl.ControlType = acCheckBox Then
If ctl = True Then


'The routine that triggers the mail according to selection probably you search from a table that matches checkbox name to e-mails e.g chk01 --> mymail.com

MsgBox "Got one email address!"

End If
End If
Next ctl
End Function

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
something must be "wrong" so in the msgbox put this
msgbox "Ctl Name : " & ctl.name

Open in new window

in order to check which checkboxes are true...
SteveL13Author Commented:
???  I just replaced my msgbox code with your msgbox code and it's working perfectly  ????

I'm getting exactly what I would expect.
John TsioumprisSoftware & Systems EngineerCommented:
glad to know....recheck your own code to see why you had 4 messages instead of 3....either a hidden checkbox or something misstyped
SteveL13Author Commented:
I'm going to continue to test but so far I think you got it!
John TsioumprisSoftware & Systems EngineerCommented:
ok...
IrogSintaCommented:
So how is each checkbox related to each email address?

Ron
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.