Send Auto Email

[Aliffi]

Dear All,

I have created this code to alert users with automatic email

this is a function which i call to a click button.

I am using SQL SERVER back and ADP front
i face a problem
'gives me error on the below line (SET rs)it says
'Runtime-error "91"
'Object variable or with block variable not set"

'-------------------------------------------------------

Private Function fEmailNotification()


Dim rs As DAO.Recordset
Dim strEmailTo As String
Dim strSubject As String
Dim strMessage As String

strSubject = "Your Package Has Arrived"
strMessage = "Please come and pick up your package in the mailroom. Thanks"

'email notification


Set rs = CurrentDb.OpenRecordset("SELECT dbo.Tasks.ID, dbo.Tasks.Cris, dbo.Tasks.TaskM, dbo.Tasks.Date_Planed, dbo.Tasks.Task, dbo.Tasks.Date_Done, dbo.Tasks.Remarks,dbo.Tasks.Pending , dbo.Tasks.Next, dbo.Emails.Email FROM dbo.Tasks INNER JOIN dbo.Emails ON dbo.Tasks.TaskM = dbo.Emails.Nick_Name Where date_planed >= { fn NOW() }")
rs.MoveFirst
While Not rs.EOF
'Just in case there are multiple records that have been flagged...
If strEmailTo <> "" Then
strEmailTo = strEmailTo & "; " & rs!email
Else
strEmailTo = rs!email
End If
rs.MoveNext
Wend

DoCmd.SendObject , "", "", strEmailTo, "", "", strSubject, strMessage, False, ""

End Function
'-----------------------------------------------------

any idea

 

[AlexCuse]

I think instead of set rs your code should read

(pseudo)
Rs.Open (SQL Statement), (CurrentProject.Connection)

Hope this will help.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[PHV]

I don't think it's a good idea to play with DAO in an ADP.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Aliffi]

it still gives me that error of

Runtime error 19
Object variable or with block variable not set

any idea
here is my final code
this time it is on button click
'------------------------------------------------

Private Sub Command18_Click()
Dim rs As ADODB.Recordset
Dim strEmailTo As String
Dim strSubject As String
Dim strMessage As String

strSubject = "Your Package Has Arrived"
strMessage = "Please come and pick up your package in the mailroom. Thanks"

'email notification

rs.Open ("SELECT dbo.Tasks.ID, dbo.Tasks.Cris, dbo.Tasks.TaskM, dbo.Tasks.Date_Planed, dbo.Tasks.Task, dbo.Tasks.Date_Done, dbo.Tasks.Remarks,dbo.Tasks.Pending , dbo.Tasks.Next, dbo.Emails.email FROM dbo.Tasks INNER JOIN dbo.Emails ON dbo.Tasks.TaskM = dbo.Emails.Nick_Name Where date_planed >= { fn NOW() }"), CurrentProject.Connection

'Set rs = OpenRecordset("SELECT dbo.Tasks.ID, dbo.Tasks.Cris, dbo.Tasks.TaskM, dbo.Tasks.Date_Planed, dbo.Tasks.Task, dbo.Tasks.Date_Done, dbo.Tasks.Remarks,dbo.Tasks.Pending , dbo.Tasks.Next, dbo.Emails.Email FROM dbo.Tasks INNER JOIN dbo.Emails ON dbo.Tasks.TaskM = dbo.Emails.Nick_Name Where date_planed >= { fn NOW() }")
rs.MoveFirst
While Not rs.EOF
'Just in case there are multiple records that have been flagged...
If strEmailTo <> "" Then
strEmailTo = strEmailTo & "; " & emails!email
Else
strEmailTo = emails!email
End If
rs.MoveNext
Wend

DoCmd.SendObject , "", "", strEmailTo, "", "", strSubject, strMessage, False, ""
End Sub

'----------------------------------------------------

 

[PHV]

You haven't instantiated rs:
Set rs = New ADODB.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886