Run code till end of a record set

[kenn0380]

Ok I have some code that I want to run on each record in a specific record set. Any ideas on How I can do that? Would it involve the EOF????

[Kirstin]

Ok I have some code that I want to run on each record in a specific record set. Any ideas on How I can do that? Would it involve the EOF????

Yes

Dim RS As DAO.Recordset
 
Set RS = 
...
 
Do Until RS.EOF
 
 code you want to run on all records
 
Loop
 

I hope that helps.

[ADezii]

Ok I have some code that I want to run on each record in a specific record set. Any ideas on How I can do that? Would it involve the EOF????

Here is the code for a DAO Recordset, it's a little simpler than ADO:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblEmployees", dbOpenDynaset)
 
MyRS.MoveFirst
 
Do While Not MyRS.EOF
  'processing goes here, within the Loop
  Debug.Print MyRS![LastName]    'Print each Last Name in Recordset
    MyRS.MoveNext
Loop
 
MyRS.Close

[kenn0380]

can I put that code directly into access for a button on click command?

[ADezii]

can I put that code directly into access for a button on click command?

Yes, you can place the code in any Event Procedure, Function, Sub-Routine, etc.

[kenn0380]

Yes, you can place the code in any Event Procedure, Function, Sub-Routine, etc.

Ok so say I had a query of a table that gave me a subset of data called query1 for simplicity sake. I am trying to setup an automatic email follow up to these inquiries. I have email code that works but It only sends that one record. the code i have is below: I am sorry to be a pain but it would be so much easier to get this to work through a group of emails rather than use mail merge, just an extra step for people that I am trying to eliminate. If I wrap your code around this will it send the email out to that query set?

Private Sub Command20_Click()

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.Email_Address
.Subject = Me.Mess_Subject
.HTMLBody = Me.Mess_Text
If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
'.DeleteAfterSubmit = True 'This would let Outlook send th note without storing it in your sent bin
.Send
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

[ADezii]

Ok so say I had a query of a table that gave me a subset of data called query1 for simplicity sake. I am trying to setup an automatic email follow up to these inquiries. I have email code that works but It only sends that one record. the code i have is below: I am sorry to be a pain but it would be so much easier to get this to work through a group of emails rather than use mail merge, just an extra step for people that I am trying to eliminate. If I wrap your code around this will it send the email out to that query set?

Private Sub Command20_Click()

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.Email_Address
.Subject = Me.Mess_Subject
.HTMLBody = Me.Mess_Text
If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
'.DeleteAfterSubmit = True 'This would let Outlook send th note without storing it in your sent bin
.Send
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

kenn, I'm a little confused. Are you trying to send the same E-Mail to multiple Recipients as defined in your Query? Please be more specific and supply additional details such as Posting the SQL of the Query, what Field(s) you are using in the Recordset, and any additional information that comes to mind.

[kenn0380]

kenn, I'm a little confused. Are you trying to send the same E-Mail to multiple Recipients as defined in your Query? Please be more specific and supply additional details such as Posting the SQL of the Query, what Field(s) you are using in the Recordset, and any additional information that comes to mind.

Ok This might be long winded so please forgive me.

The tool is being used by support rep's that need to send an confirmation email out to all there contacts for the day.

I am trying to get the access tool to automaticly send the emails when they click on there send email button.

The content of the email body and other parts send to and subject are kept in fields in a table. IE Body, EmailAddress, Subject.

The body of the email is generated by choices they make in the tool, whether the dispatch is next day or a 2 hour service call. I have those mechanics worked out.

I have a querry that gives the information required for the emails and I just want to send out all those records in the querry using the information from the fields that are included.

Hope that makes more sense.

[ADezii]

Ok This might be long winded so please forgive me.

The tool is being used by support rep's that need to send an confirmation email out to all there contacts for the day.

I am trying to get the access tool to automaticly send the emails when they click on there send email button.

The content of the email body and other parts send to and subject are kept in fields in a table. IE Body, EmailAddress, Subject.

The body of the email is generated by choices they make in the tool, whether the dispatch is next day or a 2 hour service call. I have those mechanics worked out.

I have a querry that gives the information required for the emails and I just want to send out all those records in the querry using the information from the fields that are included.

Hope that makes more sense.

Assumptions:

1. Your Query Name is qryEMail (very original isn't it?). You can change it if you like.
2. qryEMail contains your specified Fields, namely: [IE Body], [EMailAddress], and [Subject].
3. I listed only the relevant portion of code, contained within the With..End With construct and the Declarations and Statements necessary for creating the Recordset.
   

   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset("qryEMail", dbOpenForwardOnly)
    
   With MailOutLook
     .BodyFormat = olFormatRichText
     Do While Not MyRS.EOF
       .To = MyRS![EMailAddress]
       .Subject = MyRS![Subject]
       .HTMLBody = MyRS![IE Body]
          If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
            .Attachments.Add (Me.Mail_Attachment_Path)
          End If
       .Send
         MyRS.MoveNext
     Loop
   End With
    
   MyRS.Close

4. That would be the logic as I see it, the details I'll leave for you.