Solved

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

Posted on 2011-09-26
34
614 Views
Last Modified: 2012-06-21
Hi everyone!  I've got a simple form in Access 2010.  There are a couple of combo boxes on the form (Shift and CallVolume).  There is also a multi-select combo box which pulls employee names from a table called "Employee".  I've created a submit button which I want to email the contents from this single form to a static email address, with a static subject line.  The problem I'm having is getting the selection from the multi-select combo box into the email.  Can you take a look at what I've got and help me out?

Thanks!!


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)
    
      
    
    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

screenshot.jpg
0
Comment
Question by:michaelleffew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 14
  • 4
34 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36600531
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

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601098
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.
0
 

Author Comment

by:michaelleffew
ID: 36601316
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36601355
oh.. you are using MVF...  :-(
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601414
@Capricorn1
Agreed.  Evil is afoot.

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36601444
@Nick, pls continue.. i'll be on a meeting. might take a while.
0
 

Author Comment

by:michaelleffew
ID: 36601518
@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.

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601707
<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
0
 

Author Comment

by:michaelleffew
ID: 36601730
Nick,

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

Expert Comment

by:Nick67
ID: 36601748
And strList is where you want the selections to go?
"</b></p><p><u>Operators on Shift</u>: &nbsp; <b>" & strList & "</b></p></body></html>"
0
 

Author Comment

by:michaelleffew
ID: 36601772
That is correct.  I've attached a screenshot of the combo box columns, if that helps.


combo-box.jpg
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601846
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.
0
 

Author Comment

by:michaelleffew
ID: 36601886
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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601894
Original code
0
 

Author Comment

by:michaelleffew
ID: 36601935
that would put the code after where the confirmation message box begins...
0
 

Author Comment

by:michaelleffew
ID: 36601939
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

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601949
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><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
0
 

Author Comment

by:michaelleffew
ID: 36602024
I get an "object required" error, pointing to line:  

Set rs = Me.EmployeeName.Value
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36602038
Try
Set rs = Me.Recordset!EmployeeName.Value
0
 

Author Comment

by:michaelleffew
ID: 36602079
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!
0
 

Author Comment

by:michaelleffew
ID: 36602137
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

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36602480
Ok,

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

Author Comment

by:michaelleffew
ID: 36602507
Here is a screenshot of the property sheet, and a screenshot of the combo box (EmployeeName) Property Sheet combo box
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36602520
can you show the design view of the table where EmployeeName is stored?
and expand any '+' in EmployeeName?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36602567
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36602610
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
0
 

Author Comment

by:michaelleffew
ID: 36603319
Here is a screenshot of the design view for the table "Employee"

 design view of table
Here is the datasheet view with the + sign expanded
 data sheet view
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36605214
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36684288
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
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36697301
OK,
It's a horror.  Even more evil than I thought.
But with the Access 2010 help I did create a working sample.
Two recordsets
Two field objects
Set the first recordset = Me.RecordsetClone.
Move to the current record
Set the first field object to the EmployeeName field
Then set the second recordset = to the field's value

The second recordset only has one field--named 'value'
Value contains the EmployeeID's
Then you need to Dlookup the actual names.

Whatever you gain on the frontside in convenience, you lose later.
Altered shim attached.
MultiValue.accdb
0
 

Author Comment

by:michaelleffew
ID: 36708886
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
0
 

Author Closing Comment

by:michaelleffew
ID: 36708887
Again, thank you SO much for your help!!!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36709006
@Nick,
Thanks for pitching in...
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36710902
You are both welcome.  
I stay away from multivalued fields because I need backward compatability, and the possibility of upgrading the backend to SQL Server.
But, it was quite a chore to find out how, and then actually get the values out of the MVF.
It probably would have been less hassle and more efficient to construct a parameterized query, saved it, and then loaded it into a querydef and recordset.
DLookup is one of those performance pigs that a person likes to avoid, and needing it PLUS two other recordset objects was a bit of a shock.
There was certainly nothing simple or intuitive about the process!

I am glad you got it figured out.

Nick67
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question