asked on

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?

Rey Obrero (Capricorn1)

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

Rey Obrero (Capricorn1)

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

PatHartman

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.

SteveL13

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

what are the names of the check boxes and text boxes?

SteveL13

ASKER

The checkbox names are:

chkbxPatternSource1
chkbxPatternSource2
chkbxPatternSource3
chkbxPatternSource4
chkbxPatternSource5

The email text boxes are:

txtPatternSourceEmail1
txtPatternSourceEmail2
txtPatternSourceEmail3
txtPatternSourceEmail4
txtPatternSourceEmail5

Rey Obrero (Capricorn1)

are the textboxes populated with email address?

SteveL13

ASKER

Yes.

SteveL13

ASKER

But remember, we don't want to use the email address if the related checkbox isn't true.

Rey Obrero (Capricorn1)

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..

Robert Sherman

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