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
25 3674
What is the sql of "stakeholders query"?
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));
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
-
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
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.
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.
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.
I have Microsoft DAO 3.6 Object Library ticked.
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));"
Sorry to annoy you again but do I remove the " & _ and make it all into a single line?
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));"
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)
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;")
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.
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;
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.
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.
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.
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;")
-
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.
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;")
-
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
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.
NeoPa 32,556
Expert Mod 16PB
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;")
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: cronept |
last post by:
Hi, there,
I have a form with 2 email buttons both with docmd.sendobject code.
The 1st one send a report which is working properly. The 2nd one send
a query in xls format which is always get an...
|
by: Mark |
last post by:
Hi Guru's,
I have a login form which when an incorrect username or password is
entered more then 3 times, a mail is sent containing the xp username, the
username they have tried to open the...
|
by: Matt |
last post by:
I need to email query results from Access similar to the way I do in
SQL using xp_sendmail. What is the best way to accomplish this?
Thanks!
|
by: kencana |
last post by:
hi All,
I got problem in passing data (more than one) from soap client to the soap server. but if i only passing one data at a time, it works successfully..
The following is the error message i...
|
by: noel.tock |
last post by:
Hi all,
Thank you already for the amount of information that has collected in
this group, very helpful indeed. I have a question in regards to
setting up individual emails to be sent based on a...
|
by: edmundstephan |
last post by:
Hi all. how do I write a macro or module to send the results (excel) of a query only if it has data in it? It must not send the file if no results were returned.
I currently have a few Sendobject...
|
by: jej1216 |
last post by:
I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect...
|
by: hyperpau |
last post by:
Hi there guys!
I have a Form where there are three comboboxes. This comboboxes are used as references for the parameter of 3 fields in a query. when I hit a command button in my form, it opens...
|
by: raddrummer |
last post by:
Hi there,
I'm woking on a function that takes the input from a form (including Payroll Contact), uses it as a query parameter, runs the query, and then emaills out a custom .xls file using the...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |