Loop through query results to output emails

[knarlyd@hotmail.com]

I am a complete novice at scripting so please bear with me... ;-)

I have a query that randomly selects records from my table:
SELECT TOP 6 *
FROM SM_Import
ORDER BY rnd(INT(NOW*id)-NOW*id);

I would like to loop through those results, and create emails

I can get one email formed (displayed) with the code below but don't know how to get both working together to display the (6) emails:

Private Sub Command3_Click()
Dim ol As Object
Dim CaSubject As String
Dim rst As Recordset
Dim db As Database
Dim myVar As String
Dim rst1 As Recordset
Dim myVar1 As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Email FROM SM_Import")
myVar = rst!Email
With rst
.MoveFirst
Email = .Fields(0)
End With

Set rst1 = db.OpenRecordset("SELECT Subject FROM SM_Import")
myVar1 = rst1!Subject
With rst1
.MoveFirst
Subject = .Fields(0)
End With

Set ol = CreateObject("Outlook.Application")
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.To = Email
.Subject = "My Subject"
.HTMLBody = "My Body"
.Display
End With
Dialog.Box MailOutLook.Body
Exit Sub
email_error:
Dialog.Box "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

 

[pbaldy]

Here's my template code for opening and looping a recordset (you can replace the SQL with a named query if you have that saved):

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'your code here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

You'd put all your email code inside the loop.

 

[pbaldy]

Oh, and you appear to have doubled up these lines, unless it's a copy/paste issue:

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

 

[knarlyd@hotmail.com]

Thanks so much for your assistance!
Still trying to figure things out but I'm a whole lot closer

Oh, and you appear to have doubled up these lines, unless it's a copy/paste issue:

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

 

[pbaldy]

No problem, post back if you get stuck.