Concatenate Email Addresses as a Query Result

[crwilliams]

Hello,

I have read through so many threads and google posts my eyes are bugging out! I am beginner access user with no SQL experience.

Here is what I am trying to accomplish:

I have a query which runs and produces a result of

Name, Department, Region,Phone, Email,

I have a parameter set on the query at run which filters by Region
i.e. the box pops up and they enter a criteria

I would like to have a field entered next to Email that would take all the Email Addresses in my query result and concatenate them so essentially I have an email list I can pop in Outlook.

Or alternatively, a solution to allow access to these email lists via a button on a form. It has to be something very simple for an end user to access. For instance right now, to get there list they hit a button to run the query then enter the parameter then if desired, hit another button to export to excel. Today, they use Excel for everything and are used to having the concatenated email addresses in one cell in excel. For this reason, I am trying to have the email addresses concatenated in the query results to mimic what they are used to today. But I am not sold on it if there is a better way that is easy for them.

Thank you in advance for any help or suggestions.

 

[GinaWhipp]

How about this?
http://www.access-diva.com/vba16.html

 

[crwilliams]

Hi Gina,

Thank you for the information. Unfortunately, I have no idea what I am looking at on this page nor where or how I would begin to set this up to try it. Can you explain what the end result might be? i.e. does it actually create an email or just concatenate the result

 

[GinaWhipp]

What it does is pull the eMails to insert into Outlook to eMail. The code does automatically send but you can set it up to just open so you can type additional data.

If you would like to give it a try we can help OR you can hold out and see if someone posts with an easier way, up to you...

 

[crwilliams]

So just so I'm understanding this correctly, I would run the query via the button. I would have a button next to it that would automatically send an email to all the recipients listed in the query result. Oh, and I would absolutely not want it to automatically send as there would be no text in my email -but I may be interested in having it open the email with the addresses populated. Am I understanding correctly?

 

[GinaWhipp]

Yes, that is correct...

 

[crwilliams]

Thank you Gina.

So, is this as simple as creating a button. So here is what I'm thinking. I add a button on my form. I go to "Build" and I choose, "Code Builder". Then I would paste the code. Is this correct?

If yes, then I would just need to update the code, changing it to open email only and putting in my relevant information which I do not know how to do. First, I'm not sure where in the code needs changed, i.e., where do I need to update the code to make it relevant to my database and how it needs to be formatted i.e. with brackets, exclamation points etc. It's scary to me but I am willing to try.

 

[GinaWhipp]

WOW, you even make it sound scary to me! Calm down, we'll be here to step you thru.

What you posted is basically the steps and we'll you thru modifying the code. In the meantime, create your Command Button and get your query ready. To create the query just make sure to include the table that has the field that has the eMail Address. Oh, and please post the name of the field, the name of the Report and the name of query here when you are done.

If you want to go further... just follow the steps on the page and post back with any issues you have.

 

[crwilliams]

The name of my query is qry_Project_Team_By_Region
The name of the field is SME Email.
I do not have a report.
I have a form which has buttons only. One of the buttons is set up to run my query after entering the parameter. I created the button next to it to open the emails with the addresses populated. Now all i need is the code to put in the build, right?

 

[GinaWhipp]

Okay, now you need to put the code in a Module first, then rename the items indicated and post here for review. (Please use code tags...) We will get to the Build.

 

[crwilliams]

well, the code is not allowing me to put in what I think are my values. It keeps giving me errors telling me I'm doing it wrong and I have tried () and _ and many variations. Secondly, when I tried to post the code here, it says I do not have permissions. Also, what do you mean by tags?

 

[GinaWhipp]

Please post what you have here... Tags, click Go Advanced and click the pound key (#) on the Menu Bar

 

[crwilliams]

Private Sub SendeMail()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry_Project_Team_By_Regions ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs! SME Email ) Then
vRecipientList = vRecipientList & rs! SME Email & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

vMsg = " Your Message here... "
vSubject = " Your Subject here... "

DoCmd.SendObject acSendReport, " [NO REPORT [/I] ", acFormatPDF, vRecipientList, , , vSubject, vMsg, False
MsgBox ("Report successfully eMailed!")

Else
MsgBox "No contacts."
End If

 

[GinaWhipp]

Okay, I see some extra spaces and a few missing brackets because of spaces on feild names, I cleaned them up, so try...

Private Sub SendeMail()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String
 
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry_Project_Team_By_Regions")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs![SME Email]) Then
vRecipientList = vRecipientList & rs![SME Email] & ";"
rs.MoveNext
Else
rs.MoveNext
End If
 
Loop Until rs.EOF
 
vMsg = " Your Message here... "
vSubject = " Your Subject here... "
 
DoCmd.SendObject , , , vRecipientList, , , vSubject, vMsg, True
'MsgBox ("Report successfully eMailed!")
 
Else
MsgBox "No contacts."
End If

 

[Uncle Gizmo]

You may find this example useful:-

http://www.access-programmers.co.uk/forums/showpost.php?p=427023&postcount=6

 

[crwilliams]

To confirm, where am I putting the code?

I go to my "Create Email" button and go to Build Event then go to "Code Builder", is that right?

 

[crwilliams]

I looked at the example and I understand it just fine but I'm in 2013 and I don't have a modules section under reports, instead, I have a macros section which has a macros export in it. Where do I put the code?

 

[crwilliams]

ok, I found the VB and created a module and pasted the revised code you sent. Is the code set to open the email only? I assume the next step would be to attach it to the button, is that correct? How do I do that? Do I go into the property sheet or do I have to do it via the Code Builder?

 

[GinaWhipp]

You have to look on the Create tab and then look for the icon to create a new one. Once one is created it will show.

 

[GinaWhipp]

Yes, I changed it to open the eMail. Yes, now put your button on your Form.