passing query results to SendObject  | Expert | | Join Date: Jul 2007
Posts: 113
| |
Hey,
I am trying to take the results of a query to auto-email people reports only relating to themselves.
I can take e-mail addresses from a ListBox and pass them to the SendObject command but I want to take a record set (gained from a query) and pass instead.
'*****CURRENT CODE***** - Private Sub cmdSendRpt2_Click()
-
' Attach Access Snapshot Report to Email, take addresses from combo box & Send
-
-
Dim strTO As String
-
Dim strCc As String
-
Dim rptList As String
-
Dim rptcount As Long
-
Dim cnt As Long
-
-
'Code commented out as it doesn't take the recordset
-
' Dim rst As DAO.Recordset
-
' Set rst = CurrentDb.OpenRecordset("stakeholders query", String("", ""), "", dbReadOnly)
-
' Do Until rst.EOF
-
' strEmail = strEmail & rst!EmailAddress & ";"
-
' rst.MoveNext
-
' Loop
-
' rst.Close
-
'
-
' Set rst = Nothing
-
-
-
'Check to see if any e-mail addresses have been selected from the listbox
-
'If not generate a MsgBox
-
'Else goto the next section
-
If Me.lstEMailAddresses.ItemsSelected.Count = 0 Then
-
MsgBox "No email addresses have been selected; please select one or more email addresses, and then try again."
-
Else
-
-
'For each e-mail address add it to the list to send to
-
For cnt = 0 To Me.lstEMailAddresses.ItemsSelected.Count - 1
-
strTO = strTO & ";" & Me.lstEMailAddresses.Column(0, Me.lstEMailAddresses.ItemsSelected(cnt))
-
Next cnt
-
-
strTO = Mid(strTO, 2)
-
-
-
' Send Report using MS Access Snapshot
-
rptList = "REPORT NAME"
-
DoCmd.SendObject acReport, rptList, "SnapshotFormat(*.snp)", lstEMailAddresses, strCc, "", "E-mail heading to be agreed upon", "Key Information to be added" & vbCrLf & vbCrLf & "Text Body to be added", True
-
End If
-
-
End Sub
Any assistance in getting lstEMailAddresses to be populated from a query would be greatly appreciated.
Kind regards,
Damon Reid
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
What is the sql of "stakeholders query"?
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
It is simply a list of e-mail addresses pulled when a project number is equal to a control toolbox on the same form.
So if you pick a project number if should e-mail the report of that project to just the people working on that project. I think I need to open the query, make a variable array to pull all the values and then close the query. I just can't get it to work.
I am working in XP with Access 2003. - SELECT Stakeholders.[Stakeholder Key], Stakeholders.[Project Number], Stakeholders.Stakeholder, [Person List].[E-Mail Address]
-
FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder
-
WHERE (((Stakeholders.[Project Number])=[Forms]![emailform]![Combo0]) AND (([Person List].[E-Mail Address]) Is Not Null));
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
Try this .... -
Private Sub cmdSendRpt2_Click()
-
' Attach Access Snapshot Report to Email, take addresses from combo box & Send
-
Dim strEmail As String
-
Dim rptList As String
-
Dim rst As DAO.Recordset
-
-
Set rst = CurrentDb.OpenRecordset("SELECT [E-Mail Address] As EmailAddress FROM [stakeholders query]")
-
Do Until rst.EOF
-
strEmail = strEmail & rst!EmailAddress & ";"
-
rst.MoveNext
-
Loop
-
rst.Close
-
Set rst = Nothing
-
-
' Send Report using MS Access Snapshot
-
rptList = "REPORT NAME"
-
DoCmd.SendObject acReport, rptList, "SnapshotFormat(*.snp)", strEmail, , , "E-mail heading to be agreed upon", "Key Information to be added" & vbCrLf & vbCrLf & "Text Body to be added", True
-
-
End Sub
-
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
Thank you for the help, I am still getting an error on the following line of code. - Set rst = CurrentDb.OpenRecordset("SELECT [E-Mail Address] As EmailAddress FROM [stakeholders query]")
Msgbox:
Run-time error '3061':
Too few parameters. Expected 1.
I have looked at the help files around the OpenRecordset command but can't make head or tail of the example given. The only thing I can find is that the "As EmailAddress" is not a keyword on the help file and I am not sure how to add object library to Access.
Any help would be greatly appreciated.
Damon Reid
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
Quote:
Originally Posted by damonreid Thank you for the help, I am still getting an error on the following line of code.
- Set rst = CurrentDb.OpenRecordset("SELECT [E-Mail Address] As EmailAddress FROM [stakeholders query]")
Msgbox:
Run-time error '3061':
Too few parameters. Expected 1.
I have looked at the help files around the OpenRecordset command but can't make head or tail of the example given. The only thing I can find is that the "As EmailAddress" is not a keyword on the help file and I am not sure how to add object library to Access.
Any help would be greatly appreciated.
Damon Reid Is [E-Mail Address] the name of the field holding the email addresses and is [stakeholders query] the name of the query. Check spelling etc.
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
Thank you for the (very) quick response.
I have checked the spelling of the query and the field (rename, copy and then paste into the VB code) and I am still getting the same error.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
Quote:
Originally Posted by damonreid Thank you for the (very) quick response.
I have checked the spelling of the query and the field (rename, copy and then paste into the VB code) and I am still getting the same error. Have you got a reference to the DAO library?
In the VBA Editor window go to Tools - references and make sure that the Microsoft DAO library is ticked on the references list.
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
I have Microsoft DAO 3.6 Object Library ticked.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
OK try this ... -
Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress " & _
-
"FROM [Person List] INNER JOIN Stakeholders " & _
-
"ON [Person List].ID = Stakeholders.Stakeholder " & _
-
"WHERE (((Stakeholders.[Project Number]) = [Forms]![emailform]![Combo0]) " & _
-
"AND (([Person List].[E-Mail Address]) Is Not Null));"
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
Sorry to annoy you again but do I remove the " & _ and make it all into a single line?
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
You can do ... -
Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE (((Stakeholders.[Project Number]) = [Forms]![emailform]![Combo0]) AND (([Person List].[E-Mail Address]) Is Not Null));"
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
I am getting the following error.
Compile Error:
Expected: list separator or )
It appears to think the entire list to that point is only the first arguement, I think.
Set recordset = object.OpenRecordset (source, type, options, lockedits)
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
I think its a bracket problem. Try this ... -
Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = [Forms]![emailform]![Combo0] AND [Person List].[E-Mail Address] Is Not Null;")
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
It still appears to have the same problem... I ensured the project has a stakeholder. - Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = [Forms]![emailform]![Combo0] AND [Person List].[E-Mail Address] Is Not Null;")
Msgbox:
Run-time error '3061':
Too few parameters. Expected 1.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
Is emailform open when you run this code?
If you run this query on its own what happens? -
SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = [Forms]![emailform]![Combo0] AND [Person List].[E-Mail Address] Is Not Null;
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
When I enter that as a query it returns just the relevant e-mail addresses, just can't appear to pass it to the SendObject.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: passing query results to SendObject
Quote:
Originally Posted by damonreid When I enter that as a query it returns just the relevant e-mail addresses, just can't appear to pass it to the SendObject. My concern is you are getting an error on the openrecordset. That doesn't make any sense. If you enter the recordset exactly as I've given it to you, in other words the same as you've just run then you shouldn't be getting that error.
I can't figure out what is going wrong. All I can suggest is you do it again in case there was some kind of error in entering it the first time.
Edit: Drop the semi colon. shouldn;t make any difference but worth a try.
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
No it didn't change the problem, what I will do is redo the form and code from scratch, perhaps there is a problem I just can't see because I have been looking at the same code for days.
Thank you for all your help.
|  | Moderator | | Join Date: Jun 2007 Location: Niagara Falls, Ontario
Posts: 557
| | | re: passing query results to SendObject
Quote:
Originally Posted by damonreid It still appears to have the same problem... I ensured the project has a stakeholder.
- Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = [Forms]![emailform]![Combo0] AND [Person List].[E-Mail Address] Is Not Null;")
Msgbox:
Run-time error '3061':
Too few parameters. Expected 1. Try taking the control out of the quotes. -
Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = " & [Forms]![emailform]![Combo0] & " AND [Person List].[E-Mail Address] Is Not Null;")
-
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
That gives me a syntax error;
Run-time error '3075'
Syntax error (missing operator) in query expression 'Stakeholders.[Project Number] = AND [Person List].[E-Mail Address] Is Not Null'.
I have cheated somewhat and made a list box so then when a project is selected it populates that box with the e-mail addresses - Private Sub cmbProjectNumbers_Change()
-
Me.lstEMailAddresses2.Requery
-
End Sub
I have then used the code to take the values selected in list box (once someone selects them) and pass that to the SendObject. It isn't perfect but it works.
Thanks again for all the help, I will keep working on the automation of this and will post the results here if I crack it.
| | Moderator | | Join Date: Jun 2007 Location: Niagara Falls, Ontario
Posts: 557
| | | re: passing query results to SendObject
Hmm... did you actually remove the control from the string? Or concatenate it with & control & as I had shown?
Another alternative that should get rid of the parameter error is using the Eval function in the string. This should produce the query results you are looking for. -
Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = Eval(""[Forms]![emailform]![Combo0]"") AND [Person List].[E-Mail Address] Is Not Null;")
-
| | Expert | | Join Date: Jul 2007
Posts: 113
| | | re: passing query results to SendObject
I tried this, when running the query on its own I get 5 e-mail addresses returned however this code opens the e-mail without any pre-selected e-mail addresses. - Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = Eval(""[Forms]![emailform]![Combo0]"") AND [Person List].[E-Mail Address] Is Not Null;")
-
Do Until rst.EOF
-
strEmail = strEmail & rst!EmailAddress & ";"
-
rst.MoveNext
-
Loop
-
rst.Close
-
Set rst = Nothing
| | Moderator | | Join Date: Jun 2007 Location: Niagara Falls, Ontario
Posts: 557
| | | re: passing query results to SendObject
Is the query now returning the appropriate records based on the form control?
I'm not quite sure what you mean by the email opens but there are no pre-selected email addresses.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: passing query results to SendObject
Quote:
Originally Posted by JKing Try taking the control out of the quotes.
-
Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = " & [Forms]![emailform]![Combo0] & " AND [Person List].[E-Mail Address] Is Not Null;")
-
Is Stakeholders.[Project Number] a text or anumeric field? If text, then you need to enclose the value in (') (See Quotes (') and Double-Quotes (") - Where and When to use them.). - Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = '" & [Forms]![emailform]![Combo0] & "' AND [Person List].[E-Mail Address] Is Not Null;")
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: passing query results to SendObject
Quote:
Originally Posted by damonreid It still appears to have the same problem... I ensured the project has a stakeholder.
- Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder WHERE Stakeholders.[Project Number] = [Forms]![emailform]![Combo0] AND [Person List].[E-Mail Address] Is Not Null;")
Msgbox:
Run-time error '3061':
Too few parameters. Expected 1. The "Too few parameters" message indicates that Access can't find the field in the table/query and thus expects it to be a parameter.
I've experienced that Access 2003 has some trouble with fieldnames in tables containing a hyphen ("-") or other special characters.
Just try the query/table with a fieldname without spaces and special characters.
Nic;o)
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,366 network members.
|
