Vba Bulk Email code with ADO recordset

[sam1fletcher]

Hey I have this code which works in another database to send a bulk email collecting data from a query.

not sure where the code goes wrong as it works in another program

The error is Expected SQL statement SELECT etc

Code:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strEmail As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "qryBookingDetails", cn 'Enter query name (error flags up this line)
'This code adds each feild to one string
With rs
Do While Not .EOF
strEmail = strEmail & .Fields("EmailAddress") & ";" 'enter feild name
.MoveNext
Loop
.Close
End With

 

[pr2-eugin]

Hello Sam, Welcome to AWF..

Does it really need a connection object here? Will it not be sufficient for you to use DAO library? Thus changing your code as..

Public Sub somethingHere()
    Dim rs As DAO.Recordset
    Dim strEmail As String
    Set rs = CurrentDb.OpenRecordset("qryBookingDetails")
    With rs
        Do While Not .EOF
            strEmail = strEmail & .Fields("EmailAddress") & ";" [COLOR=Green]'enter feild name[/COLOR]
            .MoveNext
        Loop
        .Close
    End With
End Sub

 

[sam1fletcher]

Thanks for the reply pr2 tried the DAO libary.
flags up the same line " set rs = CurrentDB...." too few parameters. does it need is it a dynaset or something like tht??

 

[pr2-eugin]

Does the Query "qryBookingDetails" require a parameter that need to be supplied??

 

[sam1fletcher]

the query is already open in form veiw. the code is to get email addresses off the form/query add them to the string strEmail then use the string as the To: bit wen seeting up an email. as i said before i have this working in another database using ADO libary but not sure why the code doesnt work in my new DB? dont know why it says expected SQL when its clear that thats not anything to do wil sql yet.

 

[pr2-eugin]

the query is already open in form veiw.

In that case, try using the Form's Recordset to that object..

Public Sub somethingHere()
    Dim rs As DAO.Recordset
    Dim strEmail As String
    Set rs = Forms![COLOR=Blue]formName[/COLOR].RecordsetClone
    With rs
        Do While Not .EOF
            strEmail = strEmail & .Fields("EmailAddress") & ";" [COLOR=Green]'enter feild name[/COLOR]
            .MoveNext
        Loop
        .Close
    End With
End Sub

where formName being the name of the Form that you have opened..

as i said before i have this working in another database using ADO libary but not sure why the code doesnt work in my new DB? dont know why it says expected SQL when its clear that thats not anything to do wil sql yet.

Maybe Microsoft help might answer few questions?

 

[sam1fletcher]

Cheers pr2 for the reply that bit of the code seems to work but in the first line of the loop not sure if its the rite call: .Fields("EmailAddress") i made sure it was calling the rite textbox name.

sam

 

[sam1fletcher]

PR2!!! jiggled it about got it to work You are a legend thanks so much!

 

[sam1fletcher]

This is the code for anyone else wanting to do something similar:

Private Sub Command25_Click()

Dim rs As DAO.Recordset
Dim strEmail As String
Set rs = Forms!frmBookingDetails.RecordsetClone 'Form Name
With rs
Do While Not .EOF
strEmail = strEmail & .Fields("Email Address") & ";" 'enter feild name
.MoveNext
Loop
.Close
End With

strEmail = Left(strEmail, Len(strEmail) - 1)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This Sets up the email and adds the email list

Dim oOApp As Outlook.Application
Dim oOMail As Outlook.MailItem
Set oOApp = CreateObject("Outlook.Application")
Set oOMail = oOApp.CreateItem(olMailItem)


With oOMail
.To = strEmail
'.Subject = "email subject"
'.Body = "email message"
.Display
'.Send
End With
End Sub

 

[tranchemontaigne]

jumping back to the original ADO question...I create ADO recordset objects with the following syntax

strSQL = "SELECT ...."
 
set rst1 = New ADODB.recordset
rst1.Open strsql, CurrentProject.Connection, adOpenForwardOnly,adLockOptimistic

 

[tranchemontaigne]

...writing to interface with Exchange Server is so much simplier than interfacing with Lotus Domino...for reference, anyone who wants to generate and send mail through Domino I would highly recommend reviewing the following URL:

http://www.fabalou.com/vbandvba/lotusnotesmail.asp

This is the best and most clear explanation I have found.