ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Use variable as email reipient names (https://www.excelbanter.com/excel-programming/345178-use-variable-email-reipient-names.html)

Mr B[_2_]

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

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




Mr B[_2_]

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





quartz[_2_]

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


Tom Ogilvy

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







Tom Ogilvy

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




Mr B[_2_]

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








All times are GMT +1. The time now is 01:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com