Solved Sending Email to recipients from Listbox (1 Viewer)

Momma

Member
Local time
Today, 11:39
Joined
Jan 22, 2022
Messages
70
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.

Code:
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

I’m here to help
Staff member
Local time
Yesterday, 18:39
Joined
Oct 29, 2018
Messages
19,154
Hi. The Column() property accepts both row and column arguments, so you can specify which column to pull.
 

Momma

Member
Local time
Today, 11:39
Joined
Jan 22, 2022
Messages
70
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

I’m here to help
Staff member
Local time
Yesterday, 18:39
Joined
Oct 29, 2018
Messages
19,154
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:39
Joined
May 7, 2009
Messages
16,788
do you want to display each email (individually)?
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 09:39
Joined
May 7, 2009
Messages
16,788
your test email:
 

Attachments

  • TestDB.accdb
    576 KB · Views: 180

Momma

Member
Local time
Today, 11:39
Joined
Jan 22, 2022
Messages
70
do you want to display each email (individually)?
Code:
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:39
Joined
May 7, 2009
Messages
16,788
you can read an HTML file then use it as HtmlBody of your email.
 

Users who are viewing this thread

Top Bottom