Send range of cell to multiple recipients in Excel

[rbarrera05]

I have a range of table range("A1:B12") in Excel. I was able to send the table to one recipient only.
However, I wanted to send the table to multiple recipient. Emails can be found in column B.

Here's my initial macro code (this code will send the table only send to 1 recipient)
Sub Send_Range()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:E12").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample workshee."
.Item.To = "ryan.c.barrera@ctt.com" 'send to only one recipient

.Item.Subject = "My subject"
.Item.Send
End With


End Sub

 

[Ruddles]

Separate the email addresses with semicolons?

 

[Worf]

Something like this:

Sub Send_Range()
ActiveSheet.[c1:e12].Select
ActiveWorkbook.EnvelopeVisible = 1
With ActiveSheet.MailEnvelope
    .Item.To = GetClip
    .Introduction = "This is a sample worksheet."
    .Item.Subject = "My subject"
    '.Item.Send
End With
End Sub


Function GetClip$()
Dim dto As DataObject
Set dto = New DataObject
On Error GoTo NotText
Range("b1:b" & Range("b" & Rows.Count).End(xlUp).Row).Copy      ' the emails
dto.GetFromClipboard
GetClip = Replace(dto.GetText(1), vbNewLine, ";")               ' add semicolons
NotText:
If Err <> 0 Then MsgBox "Data on clipboard is not text."
End Function

 

[rbarrera05]

Thank you Sir/Ma'am, it works that way...

 

[Worf]

You are welcome.
I’m Sir Worf, a mighty Klingon warrior…
Now seriously, note that one interesting feature of that code is that it has no looping.