Syntax error

[Al]

Hi Could someone tell me what is wrong with the code below. I am getting
an error 91 at line startingt rsEmailrecs.Open

Thanks

Public Sub Email()

Dim rsEmailrecs As ADODB.Recordset
Dim objOutlook As New Outlook.Application
Dim objMessage As MailItem
Dim strMessage As String
Dim strSQL As String

rsEmailrecs.Open "QryFollowUp", CurrentProject.Connection

With rsEmailrecs
While Not rsEmailrecs.EOF
strMessage = "Dear " & rsEmailrecs(("Title") & " " & ("Surname")) &
_
vbCrLf & vbCrFl & _
"test text"
If Not IsNull(rsEmailrecs("tblContacts.email")) Then
With objMessage
.to = "name@domain"
.Subject = "Subject Line here"
.body = strMessage
.send
End With
End If
rsEmailrecs.MoveNext
Wend
End With

'rsEmailrecs.Close
'Set rsEmailrecs = Nothing
'Set objOutlook = Nothing
'Set objMessage = Nothing

End Sub

 

[Van T. Dinh]

Try adding the statement:

Set rsEmailRecs = New ADODB.Recordset

before the Open statement.

 

[Al]

Thanks for that - I now get an Invalid SQL error. I assume that this means
I have to run an SQL query in the code, as opposed to just calling my
existing query? if so where doI put it please

I dont suppose you know of a tutorial with an example for setting up
emailing from within Access do you - I think the one I have is very out of
date

thanks

Al

 

[Van T. Dinh]

Arvin Meyer has a few sample databases on e-mailing. Check out his
downloads:

http://www.datastrat.com/Download2.html

Also, check Access VB Help on the Open method of ADO Recordset object.

 

[Graham R Seach]

Al,

There is also a syntax error on the following line:
vbCrLf & vbCrFl & _

Change "vbCrFl" to "vbCrLf"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html