473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

passing query results to SendObject

damonreid
114 Expert 100+
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*****

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendRpt2_Click()
  2. ' Attach Access Snapshot Report to Email, take addresses from combo box & Send 
  3.  
  4. Dim strTO As String
  5. Dim strCc As String
  6. Dim rptList As String
  7. Dim rptcount As Long
  8. Dim cnt As Long
  9.  
  10. 'Code commented out as it doesn't take the recordset
  11. '    Dim rst As DAO.Recordset
  12. '    Set rst = CurrentDb.OpenRecordset("stakeholders query", String("", ""), "", dbReadOnly)
  13. '    Do Until rst.EOF
  14. '    strEmail = strEmail & rst!EmailAddress & ";"
  15. '    rst.MoveNext
  16. '    Loop
  17. '    rst.Close
  18. '
  19. '    Set rst = Nothing
  20.  
  21.  
  22. 'Check to see if any e-mail addresses have been selected from the listbox   
  23. 'If not generate a MsgBox
  24. 'Else goto the next section
  25.     If Me.lstEMailAddresses.ItemsSelected.Count = 0 Then
  26.         MsgBox "No email addresses have been selected; please select one or more email addresses, and then try again."
  27.     Else
  28.  
  29. 'For each e-mail address add it to the list to send to
  30.         For cnt = 0 To Me.lstEMailAddresses.ItemsSelected.Count - 1
  31.         strTO = strTO & ";" & Me.lstEMailAddresses.Column(0, Me.lstEMailAddresses.ItemsSelected(cnt))
  32.         Next cnt
  33.  
  34.         strTO = Mid(strTO, 2)
  35.  
  36.  
  37.         ' Send Report using MS Access Snapshot
  38.         rptList = "REPORT NAME"
  39.         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
  40.     End If
  41.  
  42. End Sub

Any assistance in getting lstEMailAddresses to be populated from a query would be greatly appreciated.

Kind regards,
Damon Reid
Jul 5 '07 #1
25 3674
MMcCarthy
14,534 Expert Mod 8TB
What is the sql of "stakeholders query"?
Jul 7 '07 #2
damonreid
114 Expert 100+
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.


Expand|Select|Wrap|Line Numbers
  1. SELECT Stakeholders.[Stakeholder Key], Stakeholders.[Project Number], Stakeholders.Stakeholder, [Person List].[E-Mail Address]
  2. FROM [Person List] INNER JOIN Stakeholders ON [Person List].ID = Stakeholders.Stakeholder
  3. WHERE (((Stakeholders.[Project Number])=[Forms]![emailform]![Combo0]) AND (([Person List].[E-Mail Address]) Is Not Null));
Jul 9 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Try this ....

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendRpt2_Click()
  2. ' Attach Access Snapshot Report to Email, take addresses from combo box & Send
  3. Dim strEmail As String
  4. Dim rptList As String
  5. Dim rst As DAO.Recordset
  6.  
  7.     Set rst = CurrentDb.OpenRecordset("SELECT [E-Mail Address] As EmailAddress FROM [stakeholders query]")
  8.     Do Until rst.EOF
  9.         strEmail = strEmail & rst!EmailAddress & ";"
  10.         rst.MoveNext
  11.     Loop
  12.     rst.Close
  13.     Set rst = Nothing
  14.  
  15.     ' Send Report using MS Access Snapshot
  16.     rptList = "REPORT NAME"
  17.     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
  18.  
  19. End Sub
  20.  
Jul 9 '07 #4
damonreid
114 Expert 100+
Thank you for the help, I am still getting an error on the following line of code.


Expand|Select|Wrap|Line Numbers
  1.     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
Jul 10 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Thank you for the help, I am still getting an error on the following line of code.


Expand|Select|Wrap|Line Numbers
  1.     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.
Jul 10 '07 #6
damonreid
114 Expert 100+
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.
Jul 10 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
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.
Jul 10 '07 #8
damonreid
114 Expert 100+
I have Microsoft DAO 3.6 Object Library ticked.
Jul 10 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
OK try this ...

Expand|Select|Wrap|Line Numbers
  1. Set rst = CurrentDb.OpenRecordset("SELECT [Person List].[E-Mail Address] As EmailAddress " & _
  2.     "FROM [Person List] INNER JOIN Stakeholders " & _
  3.     "ON [Person List].ID = Stakeholders.Stakeholder " & _
  4.     "WHERE (((Stakeholders.[Project Number]) = [Forms]![emailform]![Combo0]) " & _ 
  5.     "AND (([Person List].[E-Mail Address]) Is Not Null));"
Jul 10 '07 #10
damonreid
114 Expert 100+
Sorry to annoy you again but do I remove the " & _ and make it all into a single line?
Jul 10 '07 #11
MMcCarthy
14,534 Expert Mod 8TB
You can do ...

Expand|Select|Wrap|Line Numbers
  1. 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));"
Jul 10 '07 #12
damonreid
114 Expert 100+
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)
Jul 10 '07 #13
MMcCarthy
14,534 Expert Mod 8TB
I think its a bracket problem. Try this ...

Expand|Select|Wrap|Line Numbers
  1. 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;")
Jul 10 '07 #14
damonreid
114 Expert 100+
It still appears to have the same problem... I ensured the project has a stakeholder.

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 10 '07 #15
MMcCarthy
14,534 Expert Mod 8TB
Is emailform open when you run this code?

If you run this query on its own what happens?

Expand|Select|Wrap|Line Numbers
  1. 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;
Jul 10 '07 #16
damonreid
114 Expert 100+
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.
Jul 10 '07 #17
MMcCarthy
14,534 Expert Mod 8TB
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.
Jul 10 '07 #18
damonreid
114 Expert 100+
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.
Jul 10 '07 #19
JKing
1,206 Expert 1GB
It still appears to have the same problem... I ensured the project has a stakeholder.

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. 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;")
  2.  
Jul 10 '07 #20
damonreid
114 Expert 100+
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbProjectNumbers_Change()
  2. Me.lstEMailAddresses2.Requery
  3. 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.
Jul 10 '07 #21
JKing
1,206 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. 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;")
  2.  
Jul 10 '07 #22
damonreid
114 Expert 100+
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.

Expand|Select|Wrap|Line Numbers
  1. 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;")
  2. Do Until rst.EOF
  3.         strEmail = strEmail & rst!EmailAddress & ";"
  4.         rst.MoveNext
  5.     Loop
  6.     rst.Close
  7.     Set rst = Nothing
Jul 10 '07 #23
JKing
1,206 Expert 1GB
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.
Jul 10 '07 #24
NeoPa
32,556 Expert Mod 16PB
Try taking the control out of the quotes.

Expand|Select|Wrap|Line Numbers
  1. 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;")
  2.  
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.).
Expand|Select|Wrap|Line Numbers
  1. 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;")
Jul 10 '07 #25
nico5038
3,080 Expert 2GB
It still appears to have the same problem... I ensured the project has a stakeholder.

Expand|Select|Wrap|Line Numbers
  1. 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)
Jul 10 '07 #26

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
5
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...
1
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!
0
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...
1
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...
1
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...
6
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...
3
hyperpau
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...
20
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...
0
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
1
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)...
0
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...
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.