Query to find applicable contact names to provide for emails

[IncidentalProgrammer]

I just want to preface this again with the fact that I am not an IT person; I've literally only been doing Access since this June, and VBA since August. My technical terms are improving, but still weak, and I am learning Access and programming in the process of building a database from the ground up for my department. Please pardon my ignorance!

So I have another tall order from the team. I've done some research and it looks doable, but complicated. The idea is a button on the (insurance) policy view form that leads into options to send an email to the agent, company, or client, and auto-fills the subject line and email addresses for everyone who should be included. I am building a small form that's meant to set the email data that will be used. The form is divided into 3 sections: the options for emails going to agents, emails to clients, and emails to the company. In each section, I'm trying to use a combobox that will contain all relevant contacts for the subject insurance policy, that the user can make multiple selections from to populate a list box; the list box would then be the list of contacts that the email would address to (however, I am open to suggestions, if anyone has a better way of doing this). I know I need a query to filter through all the data across multiple tables, and pull what I need, but I'm still struggling with queries, and can't quite get it right.

The comboboxes all need to show ALL contacts associated with the insurance policy, regardless of whether those contacts are agents, companies, or clients (for instance, some agents want to be included on all emails regarding their client, so I'd need to be able to have that agent in all 3 listboxes). My contact names are broken down in the table as Salutation (Mr. Ms.), Primary Name, Secondary Name, and Suffix. Primary and Secondary, because contacts may be people, or they may be something like "Billing Department", or "Main Line". The Contact table is connected to a table that holds contact information specifically meant for emails from the policy that includes a single field for the contact name, so I mean to concatenate the name for that field, where the contact is a person rather than not (and I have a field in the Contact table that marks whether the contact is a person). I then want the Name, the Entity they belong to (be it the client, producer, or agency's name), and a note (this field is included in a table already) to appear in the comboboxes and listboxes. However, they also need to carry along their email addresses, of course, a tag to show which type they were selected under (agent, company, or client), and their status (active or inactive because inactive contacts need to cause an error that prompts users to replace them); these fields will be invisible.

Does anyone have experience or resources on this type of function? I've found a guide for adding all the information into emails, it's just the query part that's kicking my butt!

So I'm thinking one query to pull the names and concatenate them for the secondary table, and then another to do the rest?

[June7]

AFAIK, the only way for a combobox to be multi-select is for it to be bound to a multi-value field. However, a listbox can be multi-select whether bound or unbound http://www.fontstuff.com/access/acctut11.htm.

Code can use the selected items to UPDATE a table field for only those records OR add items to another combobox or listbox list (if those are set for ValueList) OR build a string of email addresses.

[ranman256]

Sending emails, would work better with a REPORT, not a form. Tho, yes the data needs the form.

Youd have a form with the list of emails,
then scan the list, get email, and report, then sending the report via, DoCmd.SendObject acSendReport

Sub SendListEmails()
Dim vTo, vBody, vSubj, vRpt
For i = 0 To lstBox.ListCount - 1
   lstBox = lstBox.ItemData(i)
   vRpt = lstBox
   vSubj = "Subject:" & lstBox.Column(1)
   vTo = lstBox.Column(2)
   vBody = "message body"
  
  DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTo, , , vSubj, vBody
Next
End Sub

[IncidentalProgrammer]

Sorry, June7! Let me be more specific. I mean for the comboboxes to draw from a query of all contacts associated with the policy, and then users can select one, and that adds it to the listbox, select another in the combobox to add that to the listbox, and so on, and build up that listbox just by picking names out of the combobox one after another. The combobox would not be bound. The listbox would be bound to a table that stores the names and email addresses.

Thanks for the tip and the code, ranman256! I'm not at all opposed to using a report to send the emails, I just need the form for the users to update the data. Employees and roles can change pretty regularly at all three destinations, so the contact emails have to be easy for our people to change.

[June7]

Yes, I did indicate that is one possible use of combobox - add items to listbox. Use AddItem and RemoveItem methods of listbox.

[IncidentalProgrammer]

Woops! Sorry, coffee pot wasn't working this morning, hence, neither was my brain!