I am attempting to read a list of email addresses into a variable and then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the variable and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing Code
Only"

Any help is appreciated.

Mr B

Dim v() as String
Redim v(0 to 0)
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
v(ubound(v)) = Sheets("Email Recipients") _
.Range("A" & varCurRow).Value
redim preserve v(ubound(v) + 1)
varCurRow = varCurRow + 1
Wend
Redim Preserve v(ubound(v) - 1)
ActiveWorkbook.SendMail v, "Just a Test", "Testing Code


--
Regards,
Tom Ogilvy

"Mr B" wrote in message
...
I am attempting to read a list of email addresses into a variable and then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the variable
and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing Code
Only"

Any help is appreciated.

Mr B

Tom,
Thanks for the reply and the code.

I tried it and I still get the following message:

Run-time error '1004':
Application-defined or object-defined error.

Again, if I just provide the list as part of the array:

ActiveWorkbook.SendMail ", ")

it works, but using the variable it does not.

Mr B


"Tom Ogilvy" wrote:

Dim v() as String
Redim v(0 to 0)
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
v(ubound(v)) = Sheets("Email Recipients") _
.Range("A" & varCurRow).Value
redim preserve v(ubound(v) + 1)
varCurRow = varCurRow + 1
Wend
Redim Preserve v(ubound(v) - 1)
ActiveWorkbook.SendMail v, "Just a Test", "Testing Code


--
Regards,
Tom Ogilvy

"Mr B" wrote in message
...
I am attempting to read a list of email addresses into a variable and then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the variable
and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing Code
Only"

Any help is appreciated.

Mr B

Hi,

I'm not an ace on this but just a couple things I noticed:

What you are copying your data to is a string not an array, and also, aren't
different recipients separated by a semicolon rather than a comma (or will
either work?).

Try changing:
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing Code

To this:
ActiveWorkbook.SendMail strRecipients, "Just a Test", "Testing Code

If it still fails try changing:
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value

To This (comma to semicolon):
strRecipients = strRecipients & """; """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value

Hope this may help.

"Mr B" wrote:

I am attempting to read a list of email addresses into a variable and then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the variable and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing Code
Only"

Any help is appreciated.

Mr B

My fault, I didn't check your sendmail line - just foolishly assumed you
knew what you were talking about - so only changed the array part of it.
but, sadly, You hosed it up.

Once I fixed the bad argument you had, it ran fine.

Sub ABCDEFG()
Dim v() As String
Dim varLastRow As Long
Dim varCurRow As Long
Dim i As Long
ReDim v(0 To 0)
'other variables defined
varLastRow = 3
varCurRow = 1
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
v(UBound(v)) = Sheets("Email Recipients") _
.Range("A" & varCurRow).Value
ReDim Preserve v(UBound(v) + 1)
varCurRow = varCurRow + 1
Wend
ReDim Preserve v(UBound(v) - 1)

ActiveWorkbook.SendMail v, "Just a Test"

End Sub

Enjoy.

--
Regards,
Tom Ogilvy


"Mr B" wrote in message
...
Tom,
Thanks for the reply and the code.

I tried it and I still get the following message:

Run-time error '1004':
Application-defined or object-defined error.

Again, if I just provide the list as part of the array:

ActiveWorkbook.SendMail ", ")

it works, but using the variable it does not.

Mr B


"Tom Ogilvy" wrote:

Dim v() as String
Redim v(0 to 0)
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
v(ubound(v)) = Sheets("Email Recipients") _
.Range("A" & varCurRow).Value
redim preserve v(ubound(v) + 1)
varCurRow = varCurRow + 1
Wend
Redim Preserve v(ubound(v) - 1)
ActiveWorkbook.SendMail v, "Just a Test", "Testing Code


--
Regards,
Tom Ogilvy

"Mr B" wrote in message
...
I am attempting to read a list of email addresses into a variable and
then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the
variable
and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing
Code
Only"

Any help is appreciated.

Mr B

No criticism of quartz intended, but
Just for information, none of those suggestions will work. If the cells
contain valid email addresses, then building the array as I have shown
should get the job done. (with a correct argument for returnreceipt or
omit it).

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Hi,

I'm not an ace on this but just a couple things I noticed:

What you are copying your data to is a string not an array, and also,
aren't
different recipients separated by a semicolon rather than a comma (or will
either work?).

Try changing:
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing Code

To this:
ActiveWorkbook.SendMail strRecipients, "Just a Test", "Testing Code

If it still fails try changing:
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value

To This (comma to semicolon):
strRecipients = strRecipients & """; """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value

Hope this may help.

"Mr B" wrote:

I am attempting to read a list of email addresses into a variable and
then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the variable
and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing
Code
Only"

Any help is appreciated.

Mr B

Thank you again, Tom

I got it now.

--
HTH

Mr B


"Tom Ogilvy" wrote:

My fault, I didn't check your sendmail line - just foolishly assumed you
knew what you were talking about - so only changed the array part of it.
but, sadly, You hosed it up.

Once I fixed the bad argument you had, it ran fine.

Sub ABCDEFG()
Dim v() As String
Dim varLastRow As Long
Dim varCurRow As Long
Dim i As Long
ReDim v(0 To 0)
'other variables defined
varLastRow = 3
varCurRow = 1
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
v(UBound(v)) = Sheets("Email Recipients") _
.Range("A" & varCurRow).Value
ReDim Preserve v(UBound(v) + 1)
varCurRow = varCurRow + 1
Wend
ReDim Preserve v(UBound(v) - 1)

ActiveWorkbook.SendMail v, "Just a Test"

End Sub

Enjoy.

--
Regards,
Tom Ogilvy


"Mr B" wrote in message
...
Tom,
Thanks for the reply and the code.

I tried it and I still get the following message:

Run-time error '1004':
Application-defined or object-defined error.

Again, if I just provide the list as part of the array:

ActiveWorkbook.SendMail ", ")

it works, but using the variable it does not.

Mr B


"Tom Ogilvy" wrote:

Dim v() as String
Redim v(0 to 0)
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
v(ubound(v)) = Sheets("Email Recipients") _
.Range("A" & varCurRow).Value
redim preserve v(ubound(v) + 1)
varCurRow = varCurRow + 1
Wend
Redim Preserve v(ubound(v) - 1)
ActiveWorkbook.SendMail v, "Just a Test", "Testing Code


--
Regards,
Tom Ogilvy

"Mr B" wrote in message
...
I am attempting to read a list of email addresses into a variable and
then
use that variable in the SendMail function. If I use hard coded email
addresses all is fine, but when I try to store the names in the
variable
and
use the variable it errors out.

Here is my code:
Dim strRecipients As String
'other variables defined
While varCurRow < varLastRow + 1
'read the list of email recipients
Sheets("Email Recipients").Range("A" & varCurRow).Select
If strRecipients = "" Then
strRecipients = "'" & Sheets("Email Recipients").Range("A" &
varCurRow).Value
'strRecipients = """ & range.("A3").Value & """
Else
strRecipients = strRecipients & """, """ & Sheets("Email
Recipients").Range("A" & varCurRow).Value
End If
varCurRow = varCurRow + 1
'cntr = cntr + 1
Wend
ActiveWorkbook.SendMail Array(strRecipients), "Just a Test", "Testing
Code
Only"

Any help is appreciated.

Mr B