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