I administer a recognition database that we use at work that allows employees to send "Thank You" emails to employees who have helped them with various work related issues. I created a form Called Frm PennyThought. The submitter will complete the following fields on the form (submittername,recipient,manager,etc.). I have a button on the form and I have a send object macro attached to the button, that "on click", an email is sent to the recipient. An attachment of a certificate is also included in the email.

The issue I am having is that the form only allows one recipient. I have been asked to allow the form to be sent to multiple recipients. The recipient field on FrmPennyThoughts is a combobox that is linked to a table called TblName, which lists all of the employees in my division.

I could just create multiple recipient fields on the form, but I would prefer not to go that route. I would like the user to be able to choose names from the recipient field and then have that name append to another field on the same form. In doing this, the user would be able to choose as many names from the list as they would like and have all of the names append to 1 field on the form. I tried using the sertvalue macro, but everytime I chose another name, it replaced the name in the field that the alue appeneded to. I DO NOT want the field to be replaced with a new value or go to a new record until I click the send button at the bottom of the form.

Can someone help me with this.

You'll need to do a couple of things to implement this in your application.

First, create a list box on the form to replace the combo box. Use the same recordsource for the list box that you now have for the combo.

Next, change the "Multi-Select" property of the list box to either "Simple" or "Extended. Try them both to see which behavior suits your needs better. This property is found on the "Other" tab of the form's property sheet.

Now, when you want to send multiple emails, you can select them in the list and use a piece of VBA to concateneate the selected email addresses into a string for the "To" line in the email code you now have.

It should look something like this (aircode, not tested):



This is only a fragment of the code. I assume you already have the VBA set up to send the emails. You'll need to insert this at the appropriate point to get the concatenated email string.

HTH

George

I was able to update the combobox to a listbox (which was the easy part). I am not well versed in VBA at all. However it looks like part of the code is adding a semicolon to the name. The record source that I am using already includes a semi-colon at the end of each name. I've added an attachment so that you can see the part of the form that I need help with, in design view and datasheet view.

I am using a macro to run my SendObject event (which is also attached). The SendObject macro is attached to a send button on the form. Would I have to change my sendobject macro to VBA and then include the code you provided or would I use the code you provided as a separate event and attach that to a different button on the form?

Here is the attachment

Here re the other 2 attachments. Sorry

They didn't attach

Design view:

You'll need to use VBA. Macros, while simple to get started with, are not powerful enough to do very complex tasks.

If your current email list already has the semi-colon as part of the email itself, then you need not append anohter.

Unfortunately, I'm swamped at work and can't spend a lot of time today on this. Perhaps we can get help from someone.


BTW: when we are talking about tables, code, queries, etc., screenshots are not all that useful. We need to see the actual workings of the forms and the code behind them. Can you upload a copy of the database, Zipped to under 500KB?

Thanks.

Edited by: GroverParkGeorge on Tue Mar 10 12:38:25 EDT 2009.