| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
| Extract domain names from email IDs | Excel Worksheet Functions | |||
| Email worksheet from a list of names and email | Excel Discussion (Misc queries) | |||
| add email address to a list of names | Excel Discussion (Misc queries) | |||
| splitting names & email add. | Excel Programming | |||
![Mr B[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
