DoCmd.SendObject but be able to have user select email address to send to

In Access db when a user clicks the Mail Report button on a form an email is created and populated with the information to send to a Distribution List. I would like the user to be presented with a list of email addresses to be able to select from, how do I do that. VB newbie.  Below is my code for sending the form.

strDocName = "PReport"
    strFilter = "DQR ='" & Me!DQR & "'"
    DoCmd.OpenReport strDocName, acViewPreview, , strFilter
    DoCmd.SendObject acSendReport, "PReport", "PDF Format", "New-HIX-DQR@company.com;", "", , "New DQR", "Please see the latest DQR issued by HIA QA", , False
haradaindustryofamericaManager, Information SystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Where would the list of emails come from?
haradaindustryofamericaManager, Information SystemsAuthor Commented:
They are already setup in Office 365 as Distribution Lists so they exist, but as to where they would come from, I have no idea that is why I am asking the best possible way to accomplish my goal.


What would you suggest?
PatHartmanCommented:
This is a trivial problem as long as the distribution lists exist in a table so you can use a query to read the list and send an email for each item.  If the Lists are somewhere that Access can get to, you might be able to do this with VBA but it will be more code than you sound like you would be comfortable with.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gustav BrockCIOCommented:
This is not a trivial task.

For a static extraction of the members of the list, this previous question answers how:

How to export all members of a distribution list  from office 365

For a dynamic extraction - if members change frequently - you would have to call a similar script, read the members, and somehow have them listed for the user to mark and unmark.
Dale FyeOwner, Developing Solutions LLCCommented:
Or, you can simply change the last argument of the SendObject method to True.  This will actually open the email in your email application, and you can type in the select distro list (although this is not nearly as elegant as displaying some form of popup to select from distro lists which exist in Outlook).

Dale
haradaindustryofamericaManager, Information SystemsAuthor Commented:
@PatHartman - Would you be able to point me in the right direction to accomplish that?

@Gustav Brock - Not sure why it would need to extract the members of the Dist List as it does not do that now and works fine, I just want to be able to have the user select between two different Dist Lists.

@Dale Fye -  That may be the course I go, I was also thinking of duplicating the script and placing a second button on the form for them to select for the other Dist List
PatHartmanCommented:
I'd like to but I don't where your list is currently so I can't tell you how to import it.  Access does have the ability to link to Outlook and so if your list is in Outlook You can just link to it and use it like a table -- External Data/New Data Source/From Other Sources.

If you want the user to select more than one email, then you can bind the linked contact list to a multi-select listbox and that can be used to control the send loop.  If you just want the user to select one email, then use a combobox or listbox in single select.  That will not require a loop.  Your OutputTo command will just reference the combo/listbox for the single address.
haradaindustryofamericaManager, Information SystemsAuthor Commented:
I created a new table in the db called Email_Address and listed the two email addresses in there how can I query that and then present for selection?
PatHartmanCommented:
Add a combo to your form.  The RowSource of the combo would select the table with the email addresses.  You can use the BeforeUpdate event of the combo to run the OutputTo command.  Just reference the combo in the part where you need the email address.
haradaindustryofamericaManager, Information SystemsAuthor Commented:
I simply created a new action button, copied the contents for the command and set the new email address in the script, then I named each button for the email Dist List

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
That's fine but if you add a third address, my suggestion requires NO code or form changes.  However, your solution will require code and form changes.  Try to take off your spreadsheet hat and put on your relational database hat.  Additional data values should NEVER, EVER affect any design element in a properly designed application whereas in a spreadsheet, each time you add a new data value, you have to change all your formulas to accommodate the new value.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.