send email on query (individual)

[ihere3010]

need to send email based on query (specific record) to specific one
I have erorr " object required " and " Object variable or with block variable not set" I do not know what is the problem
please some help
thanks



code here

Option Compare Database
Private Sub Command0_Click()
Dim mydb As DAO.Database
Dim rst As DAO.Recordset
Dim tempst As String
Dim qdf As QueryDef
Dim rest As String
Dim rst2 As Recordset
Dim mdatee As Date
Dim mpayroll As String
Dim mto As String
Set mydb = CurrentDb
tempst = "SELECT DISTINCT payroll, datee, email FROM payed query WHERE datee = '" & dodo & "'" & " ORDER BY payroll"
Set rst = mdb.OpenRecordset(tempst, dbOpenDynaset)
rst.MoveFirst
While Not rst.EOF
mpayroll = rst("payroll")
mdatee = rst("datee")
Set qdf = mydb.QueryDefs("payedQuery")
rest = "SELECT payed.payroll,personal.namee,payed.type,payed.amou nt,personal.email,payed.datee" & _
"FROM payed inner join personal on payed.payroll = personal.payroll" & _
"WHERE payed.payroll= mpayroll, payed.datee = mdatee " & _
"ORDER BY payed.payroll"
qdf.SQL = rest
qdf.Close
Set rst2 = qdf.OpenRecordset
rst2.MoveFirst
'While Not rst2.EOF
mto = rst2("email")
DoCmd.SendObject acSendQuery, payedQuery, acFormatXLS, mto, , , "ÞíãÉ ÇáÚáÇÌ ÇáÇÓÑí æÇáÔÎÕí ", "ÞíãÉ ÝæÇÊíÑ ÇáÚáÇÌ ÇáÔÎÕí æÇáÇÓÑí ÇáÊì ÎÑÌÊ ãä ÇáØÈíÉ", False
rst2.Close
rst2.MoveNext
Wend
mdb.Close
End Sub

[Seth Schrock]

First error is on line 14, you declared the variable as mydb, but then used it in line 14 as mdb. This very well could account for both errors.

You will also encounter an error when your SQL string in lines 20 - 23 gets concatenated because you are missing spaces at the end of lines 20 and 21 inside the quote. You got it right on lines 22.

Also, if the field datee is a date/time field, then your line 13 will fail. You need to surround a date value with hashmarks (#) instead of the single quote (').

Give that a try and let us know what happens.

[ihere3010]

"syntax error " here

rest = "SELECT payed.payroll, personal.namee, payed.type, payed.amount, personal.email, payed.datee" &_
    " FROM payed inner join personal ON payed.payroll = personal.payroll" & _
    " WHERE payed.payroll = mpayroll and payed.datee = mdatee" & _
    " ORDER BY payed.payroll"

[Seth Schrock]

On your first line you need a space between the ampersand and the underscore at the end of the line. You got it right on the others.

[zmbd]

OK,
We are off topic now.
Per forum guidlines - we ask that each thread stay to one topic/question.
Usually a closely related follow-up or a "quick" to solve question is allowed - but now we're on to the third series of troubleshooting .


The original question was...

object required " and " Object variable or with block variable not set" I"

Appears to have been solved in Post#4


Post #5 starts a second question - as such I will be splitting the thread at that point. Second Question

Post #15 asks yet a third question... and I'll start a split there. Third Question

Please do not post any further Q&A to this thread unless it relates to the original post.