Hi,

MS Access level : Expert (basic programming)

Scenario : I have a query with a number of records which each contain an email address.  Ultimately, the form will send a customised email to each email address in the relevant field in the query.  I have created some basic code but and it will send as I wish, however it will only send the 1st record and not the rest.  I think the problem may lay in the UBound but I've reached the end of my ability.  The code is below, assume field 22 contains the email addresses:

Private Sub Cmd_option_Click()
         
   Dim rst As Recordset
   Dim data As Variant
   Dim c As Integer
   Dim sEmailAddress As String
   Dim sBody As String
   
       Set rst = CurrentDb.OpenRecordset("SELECT * FROM Contact")
           
       If rst.RecordCount = 0 Then
           MsgBox "I can't find any funds to send to, are you sure you've added some?" & vbNewLine & vbNewLine & _
           "If so, I may have broken, please seek assistance"
           End
       End If
       
       data = rst.GetRows(rst.RecordCount)
       
       For c = 0 To UBound(data, 2)
       
           sEmailAddress = data(22, c)
           
           sBody =  >>irrelevant body code here<<

SendOutlookEmail sEmailAddress, sBody
           
       Next c
 
End Sub


Thanks.

---

Answer from Richard Rost:

I wouldn't recommend doing it that way. I prefer starting at the beginning of the recordset and then looping until I hit the RS.EOF which is end of the records.

While Not RS.EOF
   E = rs!EmailAddress
   RS.MoveNext
Wend

See this tutorial for more help on recordsets and the way that I like to use them:

Recordsets are basically objects you can use in VBA code to edit, add, and delete records. They give you the power to do pretty much anything you want to your data using VBA code.

Here's a FREE video tutorial on how to use Recordsets:

http://599cd.com/tips/access/recordsets?key=BlFAlEx