Use variable as email reipient names

[Guest]

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 Ogilvy]

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

 

[Guest]

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 Array("(e-mail address removed)", "(e-mail address removed)")

it works, but using the variable it does not.

Mr B

 

[Guest]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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).

 

[Guest]

Thank you again, Tom

I got it now.