Loop through Access recordset to find Outlook Recipients 2

[TomYC]

I believe this post belongs here--
I have code in Access to generate a .snp file and name it, etc., and now I would like to email it to one or more recipients at the same time. If I hard code the .To line to read .To = "me@myaddress.com" it works fine. Of course I need a dynamic solution.
On my form [f_rpt] I have a textbox [txtRptRecipient] whose data comes from a query [q_Team_RR].
Is that enough information?

 

[dhookom]

I'm not at all sure what you are looking for but a wag suggests you might want to combine multiple emails addresses into a single value. If so, consider this faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

Thank you for that concatenation FAQ, dhookom!
It's not working for me yet, however.
To try and make a test work, I created a table, tblEmail
with one field, Email, and put some test items in it.

Here is the beginning code I have under a command button:
Private Sub cmdSendReport2_Click()
On Error GoTo Err_cmdSendReport2_Click
'
'Here is the problem code:
SELECT Email,
Concatenate("SELECT Email FROM tblEMAIL
WHERE Email = """ & & """") as EmailAddresses"
FROM tblEmail
MsgBox "Recipients are " & EmailAddresses
'End of problem code
+++++++
This code returns a syntax error (by the way, that " after EmailAddresses keeps returning when I save the code!).
I'm obviously trying to learn Function calls by doing them, but I also need to learn how to make some sql work inside a command button.
Any idea here?
Tnx, Tom

My function is in a module called ConcatenateEmail, and that is:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "; ") _
As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[dhookom]

The function in the FAQ should be pasted into a standard module and save the module as "modStringFunctions". You can then call the function in a query or control source or code like you would other functions. For instance, you could add a large text box to a form and set its control source to:
[tt][blue]
=Concatenate("SELECT Email FROM tblEMAIL",";")
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

That got me far, dhookom, so I thank you!
I have a "clean" function sitting in a module so named, and I have even successfully sent emails to recipients with addresses in tblEMAIL, as per my test example. I also have the recipient list displayed in a text box on my form, as txtEmail, with names separated by semicolons in a way that Outlook works with.
Now to REALLY implement this, I need for the list of recipient email addresses to be query-specific, relating to a combo box on my form.
Here is the query that returns the correct email addresses:

SELECT DISTINCT t_wr_main.unit
FROM t_LU_issue INNER JOIN ((t_lu_team INNER JOIN (t_lu_unit INNER JOIN (t_wr_main INNER JOIN (t_issue INNER JOIN t_feedBack ON t_issue.issue_id = t_feedBack.issue_id) ON t_wr_main.id = t_issue.id) ON t_lu_unit.unit = t_wr_main.unit) ON t_lu_team.team = t_wr_main.team) INNER JOIN t_LU_eval ON t_feedBack.eval = t_LU_eval.eval_code) ON (t_LU_issue.topic = t_issue.topic) AND (t_LU_issue.issue = t_issue.issue)
WHERE (((t_wr_main.unit) Is Not Null) AND ((t_wr_main.w_date) Between [forms]![f_rpt]![cbFromDate] And [forms]![f_rpt]![cbToDate]))
ORDER BY t_wr_main.unit;

I have named that query "q_Team_RR" -- Sorry that it appears more complicated than it is!

I have not yet been able to slide this query in to the place currently held by the simple tblEmail example. That is, where you wrote me: "=Concatenate("SELECT Email FROM tblEMAIL"... I tried to put the query, and this failed.
I can display the results of this query in a list box ("lstEmail"), but when I try to put the same query into my Concatenate statement I get a run time error (3061, too few parameters?) as soon as I return to form view, with debug pointing at Set "rs = db.OpenRecordset(pstrSQL)".
I know I'm close, but I'm sticking here!
T

 

[PHV]

A starting point:

...
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("q_Team_RR")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TomYC]

That worked, PHV, as soon as I realized that the one Set rs statement replaces the earlier one. I guess it also makes this concatenate function specific to that single query (q_Team_RR).

Now my Concatenate statement reads: =Concatenate("SELECT [q_Team_RR].Email FROM [q_Team_RR]",";") as the control source.

Beyond that I don't understand it, and in particular how such changes were required in changing over from a simple table as the data source for the Email addresses, to a query as the source for them. If there is a FAQ on parameters and querydefs, I will look at it.
But with success, I thank you both.
T

 

[TomYC]

I knew this would happen, PHV--
your specification of a particular query "q_Team_RR" in the Concatenate Function has worked splendidly for a month. But now if I also want to use Concatenate() with a different query as well, I don't get the new results, I get the old results.
So--
is there a way around this line in the Function:
Set qdf = db.QueryDefs("q_Team_RR")
such that I can determine the query dynamically, or by choice, etc.?
Tnx,Tom

 

[dhookom]

I generally use "clean" table/query in the SQL statement and provide filtering with the WHERE clause. Can't you do the same? I don't see where your q_Team_RR returns any field other than [Unit]. Where's the email?

You could change the Concatenate() function to add an optional argument of the query name.

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = "; ", _
        Optional pstrQueryName As String = "") _
            As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter

    Set db = CurrentDb

    Dim strConcat As String 'build return string
    If pstrQueryName <> "" Then
        Set qdf = db.QueryDefs(pstrQueryName)
        For Each prm In qdf.Parameters
          prm.Value = Eval(prm.Name)
        Next prm
        Set rs = qdf.OpenRecordset
      Else
        Set rs = db.OpenRecordset(pstrSQL)
    End If
    
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
        Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

That did it! Now I have query flexibility. Thank you!