Solved

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

Posted on 2011-09-26
34
575 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
  • 16
  • 14
  • 4
34 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
oh.. you are using MVF...  :-(
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@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 119

Expert Comment

by:Rey Obrero
Comment Utility
@Nick, pls continue.. i'll be on a meeting. might take a while.
0
 

Author Comment

by:michaelleffew
Comment Utility
@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
Comment Utility
<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
Comment Utility
Nick,

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

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Original code
0
 

Author Comment

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

Author Comment

by:michaelleffew
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:michaelleffew
Comment Utility
I get an "object required" error, pointing to line:  

Set rs = Me.EmployeeName.Value
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Try
Set rs = Me.Recordset!EmployeeName.Value
0
 

Author Comment

by:michaelleffew
Comment Utility
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
Comment Utility
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
Comment Utility
Ok,

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

Author Comment

by:michaelleffew
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Again, thank you SO much for your help!!!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@Nick,
Thanks for pitching in...
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now