create an array from access table vba

Question

I am trying to pull a list of names and email addresses from a tables in access and use that information to send out personalized emails. So I am trying to call 2 columns from access into an array and then using a loop to go through each row. I think this will be the best way to do it but i am open to other ideas. I have been able to generate code that produces one email with the first record, but i have no idea how to go about getting the rest of the records:

Private Sub SMT()
Application.Run "VariablesForRanges"
strDB1 = ThisWorkbook.Path & "\database.accdb"
strqry1 = "SELECT table1.name, table2.Email"
strqry1 = strqry1 & " FROM table2 inner join table1 on table1.FULL_NAME=table2.Name"
strqry1 = strqry1 & " group by table1.name, table2.Email;"

Set cn1 = New ADODB.Connection
cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB1 & ";"
Set rs1 = New ADODB.Recordset

With rs1
    Set .ActiveConnection = cn1   
    .Open strqry1
End With

If rs1.BOF = True Then
    MsgBox "There Are No Records To Import", vbInformation
         Else
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

strbody = "Hi " & rs1("name") & "," & vbNewLine & vbNewLine & _
"test" & vbNewLine & vbNewLine & _
"Best regards," & vbNewLine & _

On Error Resume Next
    With OutMail
    .To = rs1("Email")    
End With

On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End If
End Sub

Any help with creating an array and loop would be appreciated. Thanks.

user4736025 · Accepted Answer · 2016-05-09 14:49:29Z

2

Why not just loop through the record set?

Do While Not rs1.EOF
' create emails
    rs1.MoveNext
Loop

answered May 9, 2016 at 14:49

user4736025

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create an array from access table vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related