send email BASED on query (individual)

[ihere]

i TRY TO SEND EMAIL SPECIFIC RECORD TO SPECIFIC EMAIL


THE PROBLEM IN RST

PLEASE FOR HELP

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  FROM payed query WHERE datee = '" & dodo & "'" & " ORDER BY payroll"
Set rst = mydb.OpenRecordset(tempst, dbOpenDynaset)
rst.MoveFirst
 
mpayroll = rst("payroll")
mdatee = rst("datee")
Set qdf = mydb.QueryDef("payed query")
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, payed.datee = mdatee " & _
    "ORDER BY payed.payroll"
qdf.SQL = rest
 
Set rst2 = qdf.OpenRecordset
rst2.MoveFirst
'While Not rst2.EOF
mto = rst2("email")
DoCmd.SendObject acSendQuery, "payed query", acFormatXLS, mto, , , "YOUR BALANCE", "YOUR BALANCE HERE", False
rst2.Close
rst2.MoveNext
  
mdb.Close
End Sub

 

[pr2-eugin]

Where id dodo defined? if datee is a Date/Time type make sure you cover the international date problem.

 

[ihere]

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


code
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

 

[pbaldy]

Duplicate post:

http://www.access-programmers.co.uk/forums/showthread.php?t=264425

Please don't post the same question twice.