send email with email addresses in a range of cells

[Craig]

I'm trying to send a workbook to a list of email addresses within the
workbook. When I use ActiveWorkbook.SendMail and list the recipients
as values of cells withing the workbook, It's coming up with a 1004,
unkown recipient name in list of recipients. How would i format the
recipient section in the vb code to get rid of this error? Or is there
a better way to do this all together (other than
ActiveWorkbook.SendMail?

 

[Ron de Bruin]

Hi Graig

See
http://www.rondebruin.nl/mail/tips1.htm


Send to all E-mail addresses in a range
Dim MyArr As Variant
MyArr = Sheets("mysheet").Range("C1:C10")
..SendMail MyArr, "This is the Subject line"


Send only to the visible Addresses in column C
Dim rng As Range
Dim Arr() As String
Dim N As Integer
Dim cell As Range

Set rng = Sheets("Sheet1").Columns("C").Cells.SpecialCells(xlCellTypeConstants)
ReDim Preserve Arr(1 To rng.Cells.Count)
N = 0
For Each cell In rng
If cell.EntireRow.Hidden = False And cell.Value Like "*@*" Then
N = N + 1
Arr(N) = cell.Value
End If
Next cell
ReDim Preserve Arr(1 To N)

'Use this then in the SendMail line
.SendMail Arr, "This is the Subject line"