EMailing Through Access

[mamta]

Hi,

Can someone help me understand where I am going wrong with my code for sending emails through access 2003
Public Sub Command0_Click()
Dim rs, rs1 As Recordset
Dim I, j As Integer
Dim s, str As String
'Pull single record from ASM
s = "Select * from ASMs"
Set rs = Data.Recordset(s)
'Getting one by one record from ASM
I = 0
Do
'Pulling all data from DATA table against salesperson
str = "Select * from DATA where salesperson='" & rs(0).Value & "'"
Set rs1 = CurrentDb.openrecrdset(str)

If rs1.RecordCount <> 0 Then
Public Sub SendEmail()
' Dim db As DAO.Database
' Dim MailList As ADODB.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim CNo As String
Dim CName As String
Dim distno As String
Dim distnm As String
Dim Adrs As String
Dim City As String
Dim ctype As String
Dim State As String
Dim Acctype As String
Dim ModelCode As String
Dim CustMult As String
Dim DistMult As String
Dim Exp As String
Dim SalesP As String

DbConn ("Select Customer#, Customer_Name, Distributor#, DistName, Address, City, Customer_Type, State , AcctType , ModelCode , CustMult , DistMult , Expiration , Salesperson from DATA ")
If Not (rst.EOF Or rst.BOF) Then

CNo = rst.Fields(0).Value
CName = rst.Fields(1).Value
distno = rst.Fields(2).Value
distnm = rst.Fields(3).Value
Adrs = rst.Fields(4).Value
City = rst.Fields(5).Value
ctype = rst.Fields(6).Value
State = rest.Fields(7).Value
Acctype = rest.Field(8).Value
ModelCode = rest.Field(9).Value
CustMult = rest.Field(10).Value
DistMult = rest.Field(11).Value
Exp = rest.Field(12).Value
SalesP = rest.Field(13).Value
'db = rst.Fields(7).Value

rst.Close

DbConn ("Select Email_ID,ASM From ASMs where ASM = '" & Salesperson & "'")

Set fso = New FileSystemObject
Subjectline$ = "CPA Expiry Notification"

BodyFile$ = "Hi ," & vbCrLf & _
" & str & " & vbCrLf & _
"Warm Regards, " & vbCrLf & "CPA Team"

Set MyOutlook = New Outlook.Application
' Set MailList = Db.OpenRecordset("db")

Do Until rst.EOF

Set MyMail = MyOutlook.CreateItem(olMailItem)

' MyMail.To = MailList("Select Email_ID From Distributormst where DistributorNumber = '" & dNo & "' and DISTRIBUTORNAME = '" & dName & "'")
MyMail.To = rst.Fields(0).Value
MyMail.CC = rst.Fields(1).Value

MyMail.Subject = Subjectline$

MyMail.Body = BodyFile$

MyMail.Send

rst.MoveNext
Loop
' Loop Until MailList.EOF

Set MyMail = Nothing
Set MyOutlook = Nothing

rst.Close

Set rst = Nothing



' Db.Close

'Set Db = Nothing
End Sub
End If
rs1.Close
'Go to next record of ASM
I = I + 1
rs.MoveNext
Loop While I < rs.RecordCount

End Sub

[pbaldy]

It would help if you told us what error you get, what happens that shouldn't happen, etc. Right off, you appear to have one sub inside another, which is a no-no.

[mamta]

As per you suggestion I changed the code a bit and gave a call command to a sub instead of a sub in sub. Now I am getting the error of Compile Error :Method or Data Not Found. It is at the line "CurrentDb.Recordset (s).

Can you some one guide me with this.


Private Sub CommandButton1_Click()
Dim rs, rs1 As Recordset
Dim I, j As Integer
Dim s, str As String
'Pull single record from ASM
s = "Select * from ASMs"
Set rs = CurrentDb.Recordset(s)
'Getting one by one record from ASM
I = 0
Do
'Pulling all data from DATA table against salesperson
str = "Select * from DATA where salesperson='" & rs(0).Value & "'"
Set rs1 = CurrentDb.openrecrdset(str)

If rs1.RecordCount <> 0 Then
Call SendEmail


End If
rs1.Close
'Go to next record of ASM
I = I + 1
rs.MoveNext
Loop While I < rs.RecordCount

End Sub
Public Sub SendEmail()
' Dim db As DAO.Database
' Dim MailList As ADODB.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim CNo As String
Dim CName As String
Dim distno As String
Dim distnm As String
Dim Adrs As String
Dim City As String
Dim ctype As String
Dim State As String
Dim Acctype As String
Dim ModelCode As String
Dim CustMult As String
Dim DistMult As String
Dim Exp As String
Dim SalesP As String
DbConn ("Select Customer#, Customer_Name, Distributor#, DistName, Address, City, Customer_Type, State , AcctType , ModelCode , CustMult , DistMult , Expiration , Salesperson from DATA where SalesPerson = " & txtSalesPerson & "'")
If Not (rst.EOF Or rst.BOF) Then

CNo = rst.Fields(0).Value
CName = rst.Fields(1).Value
distno = rst.Fields(2).Value
distnm = rst.Fields(3).Value
Adrs = rst.Fields(4).Value
City = rst.Fields(5).Value
ctype = rst.Fields(6).Value
State = rest.Fields(7).Value
Acctype = rest.Field(8).Value
ModelCode = rest.Field(9).Value
CustMult = rest.Field(10).Value
DistMult = rest.Field(11).Value
Exp = rest.Field(12).Value
SalesP = rest.Field(13).Value
'db = rst.Fields(7).Value

rst.Close

DbConn ("Select Email_ID,ASM From ASMs where ASM = '" & Salesperson & "'")

Set fso = New FileSystemObject
Subjectline$ = "CPA Expiry Notification"

BodyFile$ = "Hi ," & vbCrLf & _
" & str & " & vbCrLf & _
"Warm Regards, " & vbCrLf & "CPA Team"

Set MyOutlook = New Outlook.Application
' Set MailList = Db.OpenRecordset("db")

Do Until rst.EOF

Set MyMail = MyOutlook.CreateItem(olMailItem)

' MyMail.To = MailList("Select Email_ID From Distributormst where DistributorNumber = '" & dNo & "' and DISTRIBUTORNAME = '" & dName & "'")
MyMail.To = rst.Fields(0).Value
MyMail.CC = rst.Fields(1).Value

MyMail.Subject = Subjectline$

MyMail.Body = BodyFile$

MyMail.Send

rst.MoveNext
Loop
' Loop Until MailList.EOF

Set MyMail = Nothing
Set MyOutlook = Nothing

rst.Close

Set rst = Nothing

' Db.Close

'Set Db = Nothing
End If

End Sub

[pbaldy]

Try:

Set rs = CurrentDb.OpenRecordset(s)

By the way, in VBA these declare:

Dim rs, rs1 As Recordset '1 Variant and 1 Recordset
Dim I, j As Integer '1 Variant and 1 Integer

You want

Dim I As Integer, j As Integer

[mamta]

I am sorry PBaldy, It is not working I am still getting the same error.

[pbaldy]

You should also disambiguate your references:

Dim rs As DAO.Recordset, rs1 As DAO.Recordset