Email Multiple Recipients with table

G

Guest

I am trying to send out an email to all of the email addresses in a list.
What am I doing wrong? Do I need to activate a reference for the Getrows
function to work, that is the part that I seem to be getting an error on?
Thanks in advance.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select From [EmailList]")
rs.MoveLast
sAddress = Join(rs.GetRows(rs.RecordCount), ";")
MsgBox sAddress
 
C

Carl Rapson

meyerryang said:
I am trying to send out an email to all of the email addresses in a list.
What am I doing wrong? Do I need to activate a reference for the Getrows
function to work, that is the part that I seem to be getting an error on?
Thanks in advance.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select From [EmailList]")
rs.MoveLast
sAddress = Join(rs.GetRows(rs.RecordCount), ";")
MsgBox sAddress
[/QUOTE]

Join takes a one-dimensional array as its input, but GetRows returns a
two-dimensional array. You could copy the first dimension of the array
returned by GetRows into a one-dimensional array first.

Carl Rapson
Click to expand...
 
G

Guest

Can you please give me an example. I am struggling with the getrows
function.

Carl Rapson said:
meyerryang said:
I am trying to send out an email to all of the email addresses in a list.
What am I doing wrong? Do I need to activate a reference for the Getrows
function to work, that is the part that I seem to be getting an error on?
Thanks in advance.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select From [EmailList]")
rs.MoveLast
sAddress = Join(rs.GetRows(rs.RecordCount), ";")
MsgBox sAddress
[/QUOTE]

Join takes a one-dimensional array as its input, but GetRows returns a
two-dimensional array. You could copy the first dimension of the array
returned by GetRows into a one-dimensional array first.

Carl Rapson
[/QUOTE]
Click to expand...
Click to expand...
 
C

Carl Rapson

GetRows returns a 2-dimensional array. The first dimension is the field and
the second dimension is the row; both are zero-based. So, if you want the
second field in the third row you would use

rs.GetRows(1,2)

Since your SELECT statement only has a single field, you would always use a
value of zero in the first dimension of your array. So, you might try
something like this:

Dim arr() As String, i As Integer, vnt As Variant
vnt = rs.GetRows(rs.RecordCount)
Redim arr(UBound(vnt)+1,2) As String
For i=0 To UBound(arr)
arr(i) = vnt(0,i)
Next
sAddress = Join(arr, ";")

Disclaimer: I haven't actually tried this; I gleaned this information from
Access help. You might want to look there to do more research on GetRows and
Join.

Carl Rapson

meyerryang said:
Can you please give me an example. I am struggling with the getrows
function.

Carl Rapson said:
meyerryang said:
I am trying to send out an email to all of the email addresses in a
list.
What am I doing wrong? Do I need to activate a reference for the
Getrows
function to work, that is the part that I seem to be getting an error
on?
Thanks in advance.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select From [EmailList]")
rs.MoveLast
sAddress = Join(rs.GetRows(rs.RecordCount), ";")
MsgBox sAddress
[/QUOTE]

Join takes a one-dimensional array as its input, but GetRows returns a
two-dimensional array. You could copy the first dimension of the array
returned by GetRows into a one-dimensional array first.

Carl Rapson
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Carl, I appreciate the help. I still seem to have errors come up with the
getrows function so I took your advice and found an alternate solution. For
anyone elses reference, provided is the complete code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select EmailField From EmailTable")

With rs
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
sAddress = .Fields("EmailField")
.MoveNext
End If
If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
sAddress = sAddress & "; " & .Fields("EmailField")
.MoveNext
Loop
End If
.Close
End With

DoCmd.SendObject acSendNoObject, , , sAddress, , , "Title",
“Informationâ€,True, ""


Carl Rapson said:
GetRows returns a 2-dimensional array. The first dimension is the field and
the second dimension is the row; both are zero-based. So, if you want the
second field in the third row you would use

rs.GetRows(1,2)

Since your SELECT statement only has a single field, you would always use a
value of zero in the first dimension of your array. So, you might try
something like this:

Dim arr() As String, i As Integer, vnt As Variant
vnt = rs.GetRows(rs.RecordCount)
Redim arr(UBound(vnt)+1,2) As String
For i=0 To UBound(arr)
arr(i) = vnt(0,i)
Next
sAddress = Join(arr, ";")

Disclaimer: I haven't actually tried this; I gleaned this information from
Access help. You might want to look there to do more research on GetRows and
Join.

Carl Rapson

meyerryang said:
Can you please give me an example. I am struggling with the getrows
function.

Carl Rapson said:
I am trying to send out an email to all of the email addresses in a
list.
What am I doing wrong? Do I need to activate a reference for the
Getrows
function to work, that is the part that I seem to be getting an error
on?
Thanks in advance.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select From [EmailList]")
rs.MoveLast
sAddress = Join(rs.GetRows(rs.RecordCount), ";")
MsgBox sAddress


Join takes a one-dimensional array as its input, but GetRows returns a
two-dimensional array. You could copy the first dimension of the array
returned by GetRows into a one-dimensional array first.

Carl Rapson
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
C

Carl Rapson

Congratulations. I'm glad you found a solution. That's probably how I would
have done it to begin with, although I don't know why the other method
didn't work. But hopefully, you learned a few things along the way, and
that's always good.

Carl Rapson

meyerryang said:
Carl, I appreciate the help. I still seem to have errors come up with the
getrows function so I took your advice and found an alternate solution.
For
anyone elses reference, provided is the complete code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select EmailField From EmailTable")

With rs
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
sAddress = .Fields("EmailField")
.MoveNext
End If
If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
sAddress = sAddress & "; " & .Fields("EmailField")
.MoveNext
Loop
End If
.Close
End With

DoCmd.SendObject acSendNoObject, , , sAddress, , , "Title",
"Information",True, ""


Carl Rapson said:
GetRows returns a 2-dimensional array. The first dimension is the field
and
the second dimension is the row; both are zero-based. So, if you want the
second field in the third row you would use

rs.GetRows(1,2)

Since your SELECT statement only has a single field, you would always use
a
value of zero in the first dimension of your array. So, you might try
something like this:

Dim arr() As String, i As Integer, vnt As Variant
vnt = rs.GetRows(rs.RecordCount)
Redim arr(UBound(vnt)+1,2) As String
For i=0 To UBound(arr)
arr(i) = vnt(0,i)
Next
sAddress = Join(arr, ";")

Disclaimer: I haven't actually tried this; I gleaned this information
from
Access help. You might want to look there to do more research on GetRows
and
Join.

Carl Rapson

meyerryang said:
Can you please give me an example. I am struggling with the getrows
function.

:

I am trying to send out an email to all of the email addresses in a
list.
What am I doing wrong? Do I need to activate a reference for the
Getrows
function to work, that is the part that I seem to be getting an
error
on?
Thanks in advance.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Select From [EmailList]")
rs.MoveLast
sAddress = Join(rs.GetRows(rs.RecordCount), ";")
MsgBox sAddress


Join takes a one-dimensional array as its input, but GetRows returns a
two-dimensional array. You could copy the first dimension of the array
returned by GetRows into a one-dimensional array first.

Carl Rapson
[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Email Multiple Recipients 2
What is wrong with this code? 2
Loop statement not working 2
Opening a recordset with parameters 4
record count mismatch 5
Access Module 15
Trouble aborting a loop 5
Transfer data 14

Top