Bulk Emailing Nightmare Please Help!!!!!!!!! 1

[Shadez]

Ok I am sure for some of you this will be an easy question. For those of you that think this please respond.

I have a project form that has a sub_form called members. The subform is filtered to show only cutomers interested in the currently displayed project. I am using a microsoft SQL 7 back end to hold all the data. The following code does not filter the records. The bigger problem is that as soon as I switched it from the access test app I tried it on to the live SQL info, I began recieving errors. Then the program no longer put in any info to a new email message instead I got

"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. (Error 3622)"

Please help with this. I am in real dire needs here. The following is the code I was using if you need more info please feel free to ask.


Thanks in Advance.


________________________________________
Private Sub Email_Click()
Set db = CurrentDb
SQL = "SELECT * FROM dbo_Members;"
Set rs = db.OpenRecordset(SQL)


rs.MoveFirst

Do While rs.EOF = False
email = email & rs!email & ";"
rs.MoveNext
Loop


If email = "" Then
MsgBox "There are currently no client records listed under this category", vbExclamation, "E-Mail Error"
Exit Sub
End If

DoCmd.SendObject acSendNoObject, , , , , email, , , , True

End Sub
____________________________________________

 

[KarMac21]

Hey Shadez - Try this:

Private Sub Email_Click()
Dim rs as Recordset

Set rs = CurrentDB.OpenRecordSet("dbo_Members&quot


With rs
Do While Not .EOF
email = email & IIf(Len(email)>0, ";","&quot & rs!email
.MoveNext
Loop


If email = "" Then
MsgBox "There are currently no client records listed under this category", vbExclamation, "E-Mail Error"
Exit Sub
End If

DoCmd.SendObject acSendNoObject, , , , , email, , , , True

End Sub

Hope this works - Shane

 

[Shadez]

Compile Error:

Expected End With


....That was the result of the running that code. On the brighter side I did not Get the DBseechanges error. Any clue on this?

 

[DougP]

Yes they left off the
"End with" put it below uh not sure

With rs
Do While Not .EOF
email = email & IIf(Len(email)>0, ";","&quot & rs!email
.MoveNext
Loop

End with

If you start a "with" you have to "end" it

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Shadez]

Modified code to this. Getting Runtime Error 13 Type Mismatch Now For this line. "Set rs = CurrentDb.OpenRecordSet("dbo_Members&quot
"


Private Sub Command181_Click()
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordSet("dbo_Members&quot


With rs
Do While Not .EOF
email = email & IIf(Len(email) > 0, ";", "&quot & rs!email
.MoveNext
Loop
End With

If email = "" Then
MsgBox "There are currently no client records listed under this category", vbExclamation, "E-Mail Error"
Exit Sub
End If

DoCmd.SendObject acSendNoObject, , , , , email, , , , True


End Sub

 

[KarMac21]

Are you using Access 2000 or 97?

 

[Shadez]

Access 200, With MS SQL 7 Backend

 

[Shadez]

2000 Even

 

[KarMac21]

Replace Dim rs as recordset with Dim rs as adodb.recordset

 

[KarMac21]

Sorry I hit submit before I was done:

Next replace:
Set rs = currentdb.openrecordset("dbo_members&quot
With:
Set rs = New Adodb.Recordset
rs.Open "dbo_Members",CurrentProject.Connection,,,adCmdTable


This should do the trick

 

[Shadez]

First let me thank all that have responded for the help they have provided so far. I hope I continue to get this type of help, seems that definate prgress is being made here. Alas as I change to the newest version of code I get a "Runtime Error 2295-- Unkown Message Recipients The Message was not sent".

Hitting Debug brings me to this line being highlighted.

"DoCmd.SendObject acSendNoObject, , , , , email, , , , True"

Once again thanks for all the help, I have actuallyy been making progress with learning about the more intricate details of programming for access. This is really stumping me

 

[KarMac21]

Try this line:

Docmd.SendObject acSendNoObject,,acFormatText,email,,,"Your Subject Line","Body of Text",0

Hope this works for ya

 

[Shadez]

this is truly odd. before all the changes it would work with and access back end. I never relized there was that big of a difference.

Oh and same error with that new line.

 

[rccline]

I am trying to learn how to read the above code and apply it to the results of a query. How do I relate this to my own specific field names and table or query names?

1. Does "dbo_Members" refer to any string? Or does it refer to a table name or a field name?
E.g., as in the above line: Set rs = CurrentDb.OpenRecordSet("dbo_Members&quot

2. When I create a module and run the code [Private Sub Command181_Click()], I am advised that I must declare a variable for EMAIL. How do I do this for my own db or rs? Is "Email" a field name?

Thanks.

Robert