Solved Sending Email to recipients from Listbox

[Momma]

Hi there
I have a Listbox on my form with the name and email address. The email address is in column 1. Listbox name is ContactList
I want to send the same email to a few selected recipients from the Listbox.
I get as far as to Display the Email Message but it has a 0 in the place of the email address.
I'm not sure how to specify that the email address is in Column 1 of the Listbox.

Private Sub cmdEmail_Click()

    Dim OutMail As Outlook.MailItem
    Dim OutApp As New Outlook.Application
    Dim strbody As String
    Dim strEmailRecipients As String
    Dim strFirstName As String
    Dim I As Integer
        
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
          
    strFirstName = FirstName

    strbody = "Dear " & FirstName & "<br><br>" _
        & "We have now submitted the Change of Ownership form. <br><br>"
                  
    With OutMail
                For I = 0 To Forms!frmEmailContacts!ContactList.ListCount - 1
                    I = ContactList.Selected(I)
                    .To = I
                    .Subject = "Change of Ownership"
                    .HTMLBody = strbody
                    .Display
                    OutMail = Nothing
                    OutApp = Nothing
                Next
    End With

End Sub

 

[theDBguy]

Hi. The Column() property accepts both row and column arguments, so you can specify which column to pull.

 

[Momma]

Hi. The Column() property accepts both row and column arguments, so you can specify which column to pull.

Thank you for your reply. I'm not sure where to apply that. If you don't mind, I need some more guidance, please...

 

[theDBguy]

Thank you for your reply. I'm not sure where to apply that. If you don't mind, I need some more guidance, please...

If you can post a sample db, we can show you how to do it.

 

[Momma]

If you can post a sample db, we can show you how to do it.

Thank you!

 

[arnelgp]

do you want to display each email (individually)?

Private Sub cmdEmail_Click()

    Dim OutMail As Outlook.MailItem
    Dim OutApp As New Outlook.Application
    Dim strbody As String
    Dim strEmailRecipients As String
    Dim strFirstName As String
    Dim i As Long
        
    If Forms!frmEmailContacts!contactlist.ItemsSelected.Count < 1 Then
        Exit Sub
    End If
    
    Set OutApp = CreateObject("Outlook.Application")
        
    For i = 0 To Forms!frmEmailContacts!contactlist.ListCount - 1
        If Forms!frmEmailContacts!contactlist.Selected(i) Then
            strbody = "Dear " & Forms!frmEmailContacts!contactlist.Column(0, i) & "<br><br>" _
        & "We have now submitted the Change of Ownership form. <br><br>"
            
            Set OutMail = OutApp.CreateItem(0)
  
    
    'strFirstName = FirstName

    'strbody = "Dear " & FirstName & "<br><br>" _
    '    & "We have now submitted the Change of Ownership form. <br><br>"
                  
            With OutMail
                            'I = Contactlist.Selected(I)
                            .To = Forms!frmEmailContacts!contactlist.Column(1, i)
                            .Subject = "Change of Ownership"
                            .HTMLBody = strbody
                            .Display
                        
            End With
        End If
    Next
End Sub

 

[arnelgp]

your test email:

 

[Momma]

do you want to display each email (individually)?

Private Sub cmdEmail_Click()

    Dim OutMail As Outlook.MailItem
    Dim OutApp As New Outlook.Application
    Dim strbody As String
    Dim strEmailRecipients As String
    Dim strFirstName As String
    Dim i As Long
       
    If Forms!frmEmailContacts!contactlist.ItemsSelected.Count < 1 Then
        Exit Sub
    End If
   
    Set OutApp = CreateObject("Outlook.Application")
       
    For i = 0 To Forms!frmEmailContacts!contactlist.ListCount - 1
        If Forms!frmEmailContacts!contactlist.Selected(i) Then
            strbody = "Dear " & Forms!frmEmailContacts!contactlist.Column(0, i) & "<br><br>" _
        & "We have now submitted the Change of Ownership form. <br><br>"
           
            Set OutMail = OutApp.CreateItem(0)
 
   
    'strFirstName = FirstName

    'strbody = "Dear " & FirstName & "<br><br>" _
    '    & "We have now submitted the Change of Ownership form. <br><br>"
                 
            With OutMail
                            'I = Contactlist.Selected(I)
                            .To = Forms!frmEmailContacts!contactlist.Column(1, i)
                            .Subject = "Change of Ownership"
                            .HTMLBody = strbody
                            .Display
                       
            End With
        End If
    Next
End Sub

I do, yes

 

[Momma]

I do, yes

It is working, thank you so much!

Can I add a HTML file as the Body instead of adding the text in the code?
What would the code look like?

 

[arnelgp]

you can read an HTML file then use it as HtmlBody of your email.

 

[Momma]

you can read an HTML file then use it as HtmlBody of your email.

Thanks, I'll see if I can get that to work.