How to add selection from multi-select combo box to email from single form

assuming your multi select combo name is lstEmail

Private Sub Submit_Click()

'Creates a new e-mail item and modifies its properties.

    Dim olApp As Outlook.Application
    Dim objMail As MailItem
    Set olApp = Outlook.Application
    'Create mail item
    Set objMail = olApp.CreateItem(olMailItem)
    
Dim strEmail as string, i As Variant

 
With Me.lstEmail
    If .ItemsSelected.Count Then
        For Each i In .ItemsSelected
        strEmail = strEmail & ";" & .ItemData(i)  
        Next

    else
        msgbox "Please select email"
        .setfocus
        exit sub
    End If
End With

      
    
    With objMail
       .To =strEmail 
       .Subject = "Shift Communication"
       .BodyFormat = olFormatHTML
       .HTMLBody = "<html><body><u>Current Date</u>: &nbsp; <b>" & Me.CurrentDate & "</b><p><u>Shift</u>: &nbsp; <b>" & Me.Shift & _
       "</b></p><p><u>Call Volume</u>: &nbsp; <b>" & Me.CallVolume & "</b></p><p><u>Operators on Shift</u>: &nbsp; <b>" & strList & "</b></p></body></html>"
       .Send
    End With
    


    

Dim strMsg As String, strTitle As String

strMsg = "Your shift communication has been submitted."
strTitle = " SUCCESS!! "

If MsgBox(strMsg, vbQuestion + vbOKOnly, strTitle) = vbOK Then
 DoCmd.Close acForm, "Frm_ShiftCom"


End If
End Sub

Open in new window

I'll second @capricorn1
I see strList in your code--but I don't see where you declare it, or where you add any values to it.
As @capricorn1 also noted, you haven't given us the name of your multi-select combobox either.
Extra flies in the ointment include that your combobox may have more than 1 column--and you may want the stuff from columns 1,2,3 or more on the email, rather than the values from column 0

@capricorn1's code builds up a semi-colon delimited list of emails but I don't think that was what you were driving at
<to a static email address>

So, more detail about your combobox, it's name, it's number of columns and where in the code the multi-select items are meant to go, is needed.

ASKER

Thank you both for the quick responses! The name of the combo box is "EmployeeName" and I've attached a screenshot of what the box looks like when you click on it.

These are not email addresses, rather just the name of the employee. I'd like to take the names that are checked off and include them in the body of the email.

Thanks,

Mike
operators.jpg

oh.. you are using MVF... :-(

@Capricorn1
Agreed. Evil is afoot.

@Mike,
Does this control have more than one column?
Normally, you hide something like OperatorID and display OperatorName

@Nick, pls continue.. i'll be on a meeting. might take a while.

ASKER

@capricorn1 - Thank you for your time and responses!!

@Nick - The combo box is tied to a table containing employee information, and is set to only display active employees. The employee name field is the 7th column in the employee table.

<The combo box is tied to a table containing employee information, and is set to only display active employees. The employee name field is the 7th column in the employee table.>

I run Access 2003 here at the shop, but I'll post sample to show you what I mean.
In the sample, both listboxes have the same data.
Both would have the same values.
They just don't display them same.
Both have 2 columns
But the column width for column 0 on the second one is set to 0"

How many columns does your combobox have?
Multi-ListBox.mdb

ASKER

Nick,

Thanks for the clarification. The combo box has three columns, only the second column is displayed.

And strList is where you want the selections to go?
"Operators on Shift:   " & strList & "</body></html>"

ASKER

That is correct. I've attached a screenshot of the combo box columns, if that helps.

combo-box.jpg

This is going to be tricky.
You have a mutli-valued field, which are of the devil, on the go
And then you'd really like the things in column 1 -- and not EmployeeID which is column 0
And I am using a version that doesn't support the things you are doing.

So,
It appears that the value of that monster you've created can be set equal to a recordset
http://www.pcreview.co.uk/forums/there-way-select-all-values-multivalued-lookup-column-t3648007.html
Therefore

dim rs as recordset
set rs = me.EmployeeName.value

Do until rs.EOF = true
strList = strList & rs!EmployeeName & " "
rs.movenext
loop

should populate strList for you.
Put the bold stuff into your code around line 27 and have a go at it.

ASKER

Nick,

Thank you for the quick response, again :). I just want to clarify. This new code goes in around line 27 of my original code, or the modified code from capricorn1?

Original code

ASKER

that would put the code after where the confirmation message box begins...

ASKER

Did you mean for me to place it here:

Private Sub Submit_Click()

'Creates a new e-mail item and modifies its properties.

    Dim olApp As Outlook.Application
    Dim objMail As MailItem
    Set olApp = Outlook.Application
    'Create mail item
    Set objMail = olApp.CreateItem(olMailItem)
    
    Dim rs As Recordset
Set rs = Me.EmployeeName.Value

Do Until rs.EOF = True
    strList = strList & rs!EmployeeName & " "
    rs.MoveNext
Loop


      
    
    With objMail
       .To = "xxx@xxx.org"
       .Subject = "Shift Communication"
       .BodyFormat = olFormatHTML
       .HTMLBody = "<html><body><u>Current Date</u>: &nbsp; <b>" & Me.CurrentDate & "</b><p><u>Shift</u>: &nbsp; <b>" & Me.Shift & _
       "</b></p><p><u>Call Volume</u>: &nbsp; <b>" & Me.CallVolume & "</b></p><p><u>Operators on Shift</u>: &nbsp; <b>" & strList & "</b></p></body></html>"
       .Send
    End With
    


    

Dim strMsg As String, strTitle As String

strMsg = "Your shift communication has been submitted."
strTitle = " SUCCESS!! "

If MsgBox(strMsg, vbQuestion + vbOKOnly, strTitle) = vbOK Then
 DoCmd.Close acForm, "Frm_ShiftCom"


End If
End Sub

Open in new window

Yup.
Sorry

Dim olApp As Outlook.Application
Dim objMail As MailItem
Set olApp = Outlook.Application
'Create mail item
Set objMail = olApp.CreateItem(olMailItem)

'------------------Put the new code in here-------------------

With objMail
.To = "xxx@xxx.org"
.Subject = "Shift Communication"
.BodyFormat = olFormatHTML
.HTMLBody = "<html><body>Current Date:   " & Me.CurrentDate & "Shift:   " & Me.Shift & _
"Call Volume:   " & Me.CallVolume & "Operators on Shift:   " & strList & "</body></html>"
.Send
End With

ASKER

I get an "object required" error, pointing to line:

Set rs = Me.EmployeeName.Value

Try
Set rs = Me.Recordset!EmployeeName.Value

ASKER

Changed accordingly, and now getting "no current record" at line:

Set rs = Me.Recordset!EmployeeName.Value

Thank you for taking the time to work on this with me!

ASKER

I tried running this with and without selecting items in the combo box, and both return the same "no current record" at line:

Set rs = Me.Recordset!EmployeeName.Value

Ok,

Dumb question.
EmployeeName is bound to a multivalue field, right?

ASKER

Here is a screenshot of the property sheet, and a screenshot of the combo box (EmployeeName)

can you show the design view of the table where EmployeeName is stored?
and expand any '+' in EmployeeName?

Normally a combo box stores just one value.
The screenshot you posted suggests you have a multi-valued field on the go.
Needless to say, that complicates things.
They are not backward compatible, so 99% likely you have an accdb
Which I can't open in Access 2003
The multi-valued fields are also not compatible with SQL Server or anything else, so they tend to be, well, viewed as evil.

They also are just simply Access internalizing a table
Instead of a table EmployeesPresent that would have a primary key, a foreign key to some major lookup table and values of each EmployeeID that you presently have checked, you have this multi-valued field, which is really a recordset from that table--but Access built and internalized it. So they aren't really hard things to avoid using--which is what just about every professional developer does--because sooner or later they bite you.

The multi-value field combobox is just a fancy replacement for a subform.
I am not sure why it is returning 'No current record'
We are certainly on the right track
Look here, although Allen's stuff is hard syntax to look at :)
http://allenbrowne.com/ser-42.html

Post up a sample anyway.
I have A2010 at home, and there are other Experts who may take a gander

Have a look here too
http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx
That's a good overview of the evil, it's origins and it's uses.
There's a way to get the stuff you want back out as a recordset--it just takes some doing

ASKER

Here is a screenshot of the design view for the table "Employee"

Here is the datasheet view with the + sign expanded

Sorry, you misunderstood.
The combobox on your form is storing multiple EmployeeID's in a field named EmployeeName in some other table, possibly sched or shiftcom.
It's those I am interested in, not the Employee table.

Look for EmployeeName in other tables, please

So here's a simple shim that shows how multivalue field gets eliminated.
After, I'll play with it and see how to get those multivalues out of the control :)
MultiValue.accdb

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

Start Free Trial

ASKER

Nick,

I can't thank you enough for your assistance with this! You've also given me a lot to think about in regards to the layout and design of the forms.

-Mike

ASKER

Again, thank you SO much for your help!!!

@Nick,
Thanks for pitching in...