How loop through email addresses on a form to send email to each of them?

I have a form which contains 5 different checkboxes.  In addition to the checkboxes there are 5 different email addresses.  So for example, the checkbox named “OPpattern1” is tied to “OPemail1” , and so forth.
 
The challenge is this.  I need to send email blasts out to the email addresses that have their related checkbox checked.  So if 3 of the 5 checkboxes have been checked (true), then 3 separate emails have to be sent via MS Outlook.  Each email will be identical except of course for the email address it is going to.

The emails do not need to be viewed before they go out.

I think the code needs to be something like this but I am not sure how to loop through the email addresses:

Private Sub cmdEmailBlast_Click()

   Dim olApp As Object
   Dim objMail As Object

   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
  'Create e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = Me.txtEmailRecipient  ‘This needs to be each email address looped from one to the next.
     .Subject = "Request for Quotation - " & me.txtRFQ
     .HTMLBody = Me.txtBlock1 & “ “ & me.txtBlock2
     .send
   End With

   
  MsgBox "The email blast has been executed."

 End Sub

Open in new window


Can someone help?
SteveL13Asked:
Who is Participating?
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.

Rey Obrero (Capricorn1)Commented:
try this revision

Private Sub cmdEmailBlast_Click()

   Dim olApp As Object
   Dim objMail As Object
   
	Dim j As Integer, strEmail As String
	For j = 1 To 3
		If Me("OPpattern" & j) = True Then
			strEmail = strEmail & Me("OPemail" & j)
		End If
	Next

   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
  'Create e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = strEmail 'Me.txtEmailRecipient  ‘This needs to be each email address looped from one to the next.
     .Subject = "Request for Quotation - " & me.txtRFQ
     .HTMLBody = Me.txtBlock1 & “ “ & me.txtBlock2
     .send
   End With

   
  MsgBox "The email blast has been executed."

 End Sub

Open in new window

0
Rey Obrero (Capricorn1)Commented:
try this corrected version


Private Sub cmdEmailBlast_Click()

   Dim olApp As Object
   Dim objMail As Object
   
	Dim j As Integer, strEmail As String
	For j = 1 To 5
		If Me("OPpattern" & j) = True Then
			strEmail = strEmail & Me("OPemail" & j) & ";"
		End If
	Next

   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
  'Create e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = strEmail 'Me.txtEmailRecipient  ‘This needs to be each email address looped from one to the next.
     .Subject = "Request for Quotation - " & me.txtRFQ
     .HTMLBody = Me.txtBlock1 & “ “ & me.txtBlock2
     .send
   End With

   
  MsgBox "The email blast has been executed."

 End Sub
                          

Open in new window

0
PatHartmanCommented:
Are you asking how to loop through 5 controls on a form?  Rey's solution will work but as you can see it requires that the controls/fields be named in such a way that there is a common part plus a numeric suffix.  This is very poor database design.  Any time you have more than one of something, you have "many" and "many" requires a separate table.  Once you do that, life becomes simpler and you are no longer restricted to ALWAYS 5 and ONLY 5.  You can keep as many or as few as you need.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SteveL13Author Commented:
Rey,

Is not working.  I actually have my own email address in use for the 1st checkbox which is the only one checked in for the test.  But I'm not getting the email.  Please note that I had to add two Dim lines because it wouldn't compile until I did.

Here is my current code:

Dim olApp As Object
Dim objMail As Object
Dim olMailItem As Object
Dim olFormatHTML As Object

    Dim j As Integer, strEmail As String
    For j = 1 To 5
        If Me("chkbxPatternSource" & j) = True Then
            strEmail = strEmail & Me("txtPatternSourceEmail" & j) & ";"
        End If
    Next

    On Error Resume Next 'Keep going if there is an error
    Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
        Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
    'Set body format to HTML
        .BodyFormat = olFormatHTML
        .To = strEmail  'This needs to be each email address looped from one to the next.
        .Subject = "Request for Quotation - " & Forms!subfrmQuotationDetails.txtPartRFQ    '" & Me.txtRFQ
        .HTMLBody = "<htmltags>Per the attached documents Please quote best price and delivery for: <br>" & Forms!subfrmQuotationDetails.txtPartNo & " - " & Forms!subfrmQuotationDetails.txtPatternDescription & "<br></htmltags>"
        .send
    End With

   
    MsgBox "The email blast has been executed."
  
    Me.lblEmailBlastExecuted.Visible = True

Open in new window

0
Rey Obrero (Capricorn1)Commented:
to send one email per email address, use these codes


Private Sub cmdEmailBlast_Click()
   
	Dim j As Integer, strEmail As String
	For j = 1 To 5
		If Me("OPpattern" & j) = True Then
			'strEmail = strEmail & Me("OPemail" & j) & ";"
			
			SendEmail Me("OPemail" & j)
		End If
	Next
End Sub

Sub SendEmail(strEmail as String)
   Dim olApp As Object
   Dim objMail As Object
   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
  'Create e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = strEmail ‘This needs to be each email address looped from one to the next.
     .Subject = "Request for Quotation - " & me.txtRFQ
     .HTMLBody = Me.txtBlock1 & “ “ & me.txtBlock2
     .send
   End With

   
  MsgBox "The email blast has been executed."

 End Sub
                          

Open in new window

0

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
Rey Obrero (Capricorn1)Commented:
what are the names of the check boxes and text boxes?
0
SteveL13Author Commented:
The checkbox names are:

chkbxPatternSource1
chkbxPatternSource2
chkbxPatternSource3
chkbxPatternSource4
chkbxPatternSource5

The email text boxes are:

txtPatternSourceEmail1
txtPatternSourceEmail2
txtPatternSourceEmail3
txtPatternSourceEmail4
txtPatternSourceEmail5
0
Rey Obrero (Capricorn1)Commented:
are the textboxes populated with email address?
0
SteveL13Author Commented:
Yes.
0
SteveL13Author Commented:
But remember, we don't want to use the email address if the related checkbox isn't true.
0
Rey Obrero (Capricorn1)Commented:
yes, that is correct and you  need to check the box.

the code should work...

do a Debug>Compile
correct any error raised

do a COmpact and repair


upload a copy of the db..
0
Robert ShermanOwnerCommented:
Keep in mind that Rey's solution requires that you have Outlook configured for mail.    Not everyone does, though I may be the only person left on the planet that doesn't.   If you can open outlook on your desktop and send an email, then this doesn't apply to you and you can disregard.  :D
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.