Email & Multi Select List Boxes

[Robert Dunstan]

Hi guys,

Well I've been searching through the forums trying to find an answer / help with my problem but I've just ended up confusing myself Hopefully someone can help me out and I apologise in advance if similar posts have been made before

Basically I'm trying to send a standard monthly email to all or a selection of employees on the database asking them to complete their timesheets by such and such a date. I have a form with a list box that displays all salary personnel and their email address (lstRecipients). The 'Multi Select' property has been set to extended. What I want to do is when cmdSend is clicked I need to loop through the .ItemsSelected collection, pick out the value in the 'Email' field and assign it to the 'To:' in the SendObject action. Well something along those lines.

The only problem is I haven't got a clue how to code this

Any help would be much appreciated
Rob

 

[Mile-O]

Private Sub cmdSend_Click()


    Dim intCounter As Integer ' create loop counter
    Dim strEmail As String ' sttring to hold email addresses
    
    ' count from 0 (root index) to total of items in listbox
    For intCounter = 0 To lstEmails.ListCount Step 1
        ' check to see if current listbox index is selected
        If lstEmails.Selected(intCounter) = True Then
            ' add the current entry to strEmail and append a semi-colon to separate names
            strEmail = strEmail & lstEmails.ItemData(intCounter) & ";"
        End If
    Next intCounter ' return the loop counter
    
    ' remove the final semi-colon from the list of email addresses
    strEmail = left(strEmail, Len(strEmail) - 1)

   DoCmd.SendObject .....all the usual stuff
    
End Sub

 

[Robert Dunstan]

Hi Mile-O-Phile,

Many thanks for your quick response.

I've made use of your code and it's almost working. Unfortunately I neglected to mention that my list box is displayed as Name, email address. Using the code it picks up the name obviously because that is the first column. Now I could change the layout of the list to show email then name but I'm hoping that it can be changed in the code.

Here is the code:

Private Sub cmdSend_Click()

Dim intCounter As Integer ' create loop counter
Dim strEmail As String ' string to hold email addresses
Dim strMessage As String ' string to hold email message


If IsNull(txtMessage) = True Then
strMessage = "Please type in your message"
style = vbExclamation
strTitle = "No Message"

MsgBox strMessage, style, strTitle
Me.txtMessage.SetFocus
Exit Sub
Else
strMessage = Me.txtMessage.Value
' count from 0 (root index) to total of items in listbox
For intCounter = 0 To lstRecipients.ListCount Step 1
' check to see if current listbox index is selected
If lstRecipients.Selected(intCounter) = True Then
' add the current entry to strEmail and append a semi-colon to separate names
strEmail = strEmail & lstRecipients.ItemData(intCounter) & ";"
End If
Next intCounter ' return the loop counter

' remove the final semi-colon from the list of email addresses
strEmail = Left(strEmail, Len(strEmail) - 1)
End If

DoCmd.SendObject , , , strEmail, , , "Timesheet Reminder", strMessage, True


End Sub

 

[Mile-O]

Do you need to display the email addresses in the listbox?

If you didn't you could change the order in which the details are selected into the listbox to and then [Name] and then set the size of the first column to 0cm. That way, it'll pick up the email address in lieu of the name but the user will only see the names in the listbox.

 

[Robert Dunstan]

Hi there,

Yes I thought of that after my last post, so I've swapped the columns around and set the first column to 0cm and everythings working fine now.

Many thanks for your help

Rob