Use variable as email reipient names
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
|