problem: automatic send email on query

[ihere]

I have this db and for automtic sending emil based on query to send asingle record to specific contact email
but I have a problem with SELECT with a message said ( too few parameters . expected1) i do not know how to solve that

Private Sub Command6_Click()
'Dim MYDB As Database, RST11 As Recordset, rstemp As Recordset, mto As String, empstring As String, sqlstring As String, memp As string, med As string
Dim MYDB As dao.Database, RST11 As Recordset, rstemp As Recordset, mto As String, empstring As String, sqlstring As String, mco As String, memp As String, med As String
Dim Qdf As QueryDef
Dim prm As Parameter
Set MYDB = CurrentDb
empstring = "SELECT distinct payroll,id FROM allownceQuery where id = id"
Set rstemp = MYDB.OpenRecordset(empstring, dbOpenDynaset)
rstemp.MoveFirst
While Not rstemp.EOF
memp = rstemp("payroll")
med = rstemp(id)


Set Qdf = MYDB.QueryDefs("allownceQuery")
sqlstring = "SELECT allownce1.payroll,personal.al-name,personal.field,allownce1.allawnce, allownce1.amount,"
sqlstring = sqlstring & " allownce2.date, allownce2.id,balance.balance FROM (((personal inner join allownce1 on personal.payroll = allownce1.payroll),"
sqlstring = sqlstring & " inner join allownce2 on allownce1.id = allownce2.id)"
sqlstring = sqlstring & " inner join balance on allownce1.payroll = balance.payroll)"
sqlstring = sqlstring & " WHERE personal.email like '*Khalda*' and personal.payroll = " & memp & " and allownce1.id = '" & med & "'"
sqlstring = sqlstring & " ORDER BY personal.payroll"
Qdf.SQL = sqlstring
Qdf.Close
'For Each prm In Qdf.Parameters
'prm.Value = Eval(prm.NAME)
'Next prm
Set RST11 = Qdf.OpenRecordset
RST11.MoveFirst
'While Not RST11.EOF
'combo_emp.RowSource = "SELECT EMPID,EMPNAME FROM employee WHERE COMPANY = '" & RST11("COMPANY") & "' AND EMPID = " & RST11("EMPID")
mto = RST11("email")
RST11.Close
DoCmd.SendObject acSendQuery, "allownceQuery", acFormatXLS, mto, , , " allowances added to payroll", "attached is your allowances payment", False
rstemp.MoveNext
Wend
MsgBox "EMAIL PROCESS COMPLETE", vbInformation
MYDB.Close
End Sub

[burrina]

Did you Debug your code? On what line did you get the error? Check and verify to hunt it down.

HTH

[ItsMe]

The code you are using is not employing the Eval function because it is commented out.

'For Each prm In Qdf.Parameters
'prm.Value = Eval(prm.NAME)
'Next prm

You cannot use DAO on a parameterized query without first getting the values from the parameters and bringing the literal values into the SQL. Do the three lines looking for Parameters not work? You need to construct the parameters within your SQL within quotes.
https://www.accessforums.net/program...tml#post220651

[ihere]

I changed it completely and now i have problem " data type mismatch in criteria expression " at
Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)


Private Sub Command2_Click()
Dim mydb As DAO.Database
Dim rest As Recordset
Dim temp As String
Dim mpayroll As String
Dim mpage As String
Dim sqlstring As String
Dim qdf As QueryDef
Dim reset As Recordset
Dim mmail As String

Set mydb = CurrentDb
temp = " SELECT DISTINCT payroll, page FROM pay WHERE page = '" & scode & "'" & " ORDER BY payroll"
Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)
rest.MoveFirst
While Not rest.EOF

mpayroll = rest("payroll")
mpage = rest("page")
sqlstring = " SELECT payed2.payroll, payed2.type, payed1.page, payed2.amount, personal.namee, personl.email" & _
" FROM payed2 INNER JOIN payed1 ON payed2.page = payed1.page, INNER JOIN personal ON payed2.payroll = personal.payroll" & _
" WHERE payed2.payroll = '" & mpayroll & "' And payed2.Page = " & mpage & "" & _
" ORDER BY payed2.payroll "

qdf.SQL = sqlstring
qdf.Close

Set reset = qdf.OpenRecordset
reset.MoveFirst
mmail = ("email")
reset.Close
DoCmd.SendObject acSendQuery, "pay", acFormatXLS, mmail, , , "here balance", " your balance here", False
rest.MoveNext
Wend
MsgBox "EMAIL PROCESS COMPLETE", vbInformation

mydb.Close

End Sub

[June7]

If page is number type field, then remove the apostrophe delimiters from around scode.

scode is not declared as variable in this procedure. Is it a field or control on the form? If so, use qualifier prefix to make this clear:

Me.scode or Me!scode

Also, must reference recordset to pull the email address.

mmail = reset("email")

[ItsMe]

You can not open a Dynaset on an SQL that uses SELECT DISTINCT

SELECT DISTINCT is not an updateable query.

What is it you are trying to do, from a business rules perspective?

[ihere]

I want to send email to each employee each time I register his bill

[ItsMe]

I am going to guess that there is an Employee ID that can be used as a key to find the employee's email address. I am going to guess that this same key value is available as you "register his bill".

I would suggest that you create a report Grouped on the Key value for the "register his bill" event. Use another grouping level with the Employee ID field. Don't worry about how many records the report pulls up. If it retrieves all the records in the DB, that is OK. Have the report operate autonomously, that is, it does not need to be related to anything other than its query. The important thing is to include grouping on the two items I mentioned.

From there, you can use where criteria available in your form to open the report and display the relevant record(s). Additional code can employ the Employee ID to retrieve the email address. Use the report generated with the Where criteria as an attachment to a new email that is generated using the current EMail address retrieved.

If the "register his bill" action is automated and happens all at once, meaning many bills are registered simultaneously, this automation could change a yes no field in a table to indicate an email is needed. Then you could run a second procedure that cycled the relevant records to batch email. THis may be preferable over combining the two automated procedures.

[ihere]

that is right
but
"Then you could run a second procedure that cycled the relevant records to batch email"
could I send all emails once automatically

[June7]

Can attach a report (you were trying to attach a query output) as described by ItsMe or open recordset of relevant data and put data into actual body of email so users don't have to bother viewing an attachment.

Also, you have Personal as table name - should that be Personnel?

Sending emails is common topic in forum.

Example of a simple procedure to send multiple individual emails.

Sub SendEmail()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT {sql statement here};", dbOpenSnapshot)
rs.MoveFirst
While Not rs.EOF
DoCmd.SendObject , , , rs!email, , , " allowances added to payroll", "attached is your allowances payment of " & rs!fieldname, False
rs.MoveNext
Wend
End Sub

[ItsMe]

As far as that first error is concerned, you can get passed it by changing
Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)
to
Set rest = mydb.OpenRecordset(temp, dbOpenSnapshot)

...could I send all emails once automatically...

Sure. You just need to know which records and the relevant email addresses. It seems this is what you are trying to do with the code you have posted. It just does not seem to be working for you right now. I thought maybe getting a process in place that works and is not fully automated may help you to then automate.



It is confusing to me now. I can not make out the data structure and what you are trying to include in the email, what the criteria is. If it was more clear to you and you posted small steps that worked or maybe did not work, we could help walk you through it.

Also, like June mentioned, you can include literal text retrieved from tables in the body of an email.

Taking baby steps like creating a report object that depends on its own query object may help. The report object will have grouping and won't need a complex query to support it. You could then take said query object and start applying criteria. Then you can view the SQL and bring it into your VBA.

If you already have some SQL that generates the data you want to include in the emails, good. Work on another component.

[ihere]

to clarify I have a table called payed2 (payroll, type , amount (of bill), ) and another table payed1 (page (number as id ) , datee)
payed2 attached to payed1 by page
another table personal (payroll, namee, email)
In my job I have my be 100 bill per day to be registered on system each on each employee (payroll)
I need when this step has done to send email notification to each employee to tell him that the bill with its (amount =.... ) as report , has been approved and your balance (specifically ) now is .....

[June7]

Did you review example in post 10? Or my other comments in post 5?