Multiple recipient 1

[Atalla]

Hi,
I have a ComboBox (languages), ListBox (employees), Command Button (Select) and a Command Button (email).

I choose a language in the ComboBox, click Select and then displays all Employees who speak that language in the listbox. When the listbox MultiSelect property is set to "None", I could only highlight one of the displayed employees and send him/her an email.

My question is how can I include all the employees who speak that language in the "To" part of the email, so I could send one email to all of them? Note: when setting the MultiSelect property to "Simple" or "Extended", the "To" section is blank.

Below is the code used in email_click:

Private Sub cmdMailAgents_Click()
On Error GoTo Err_cmdMailAgents_Click

DoCmd.SendObject acSendNoObject, , , lstLoggedInEmployee.Value, , , cboOtherLanguage & " call", "Please" & _
" call the following subscriber for " & cboOtherLanguage & " call assistance and reply to all to let" & _
" them know." & vbCrLf & vbCrLf & "Name:" & vbCrLf & "Account No.:" & vbCrLf & "Tel:", , True

Exit_cmdMailAgents_Click:
Exit Sub

Err_cmdMailAgents_Click:
Resume Exit_cmdMailAgents_Click
End Sub


Thank you for your help

atalla

 

[kewo99]

Hi Atalla,

Here is some code. Put this before the DoCmd.SendObject line and put SendTo in place of lstLoggedInEmployee.Value

Dim SendTo as String, ctl as Control, irow as Integer
Set ctl = Me!lstLoggedInEmployee
For irow = 0 To ctl.ListCount - 1
 If ctl.Selected(irow) Then
   SendTo = SendTo & ctl.Column(0, irow) & ";"
  End If
Next irow
Sendto = Left$(SendTo,Len(SendTo)-1)  'Remove the last semi-colon

If you want all items in lstLoggedInEmployee whether selected or not, rem out the If ... and End If lines (leave the SendTo = ...... line.

Ken

 

[Atalla]

Hi Ken,
Thank you very much for your reply, the code works fine + I added more code to include FirstName and LastName in the email.

Another thing, it's not urgent, but I've tried to apply the same code to another combobox on my form. The combobox contains all employees, but only LastName appears, so when I click a command button to send that employee an email, only the lastname appear in the "To" section of the email, and you can understand that if several employees share the same lastname, then by default, another form appears from Outlook asking me to choose one of the recipient.

My intention is to place FirstName and then LastName in the "To" part of the email.
Any ideas?

As I said, I managed to include the FirstName and LastName in the ListBox in our earlier message (through your help of course).

thanks again for you help.

Atalla

 

[kewo99]

The answer depends on what you have set as the rowsource of the combobox. If you only have the last name field, then I suggest you modify it to have two colums (e.g. SELECT [Lastname], [Firstname] from tblNames You will also have to make sure the Column Count property is set to 2 (or more if you have more).
Then assign the "To" in your e-mail to combobox.column(0) & " " & combobox.column(1)

If you already have the Lastname field in the rowsource of the cbo, use the appropriate column# (starts with 0). Again, make sure the Column Count property is set to the actual number of columns (starts with 1).

Ken

 

[Atalla]

Thanks again Ken for your help.