e-mail excel file to array of addresses in another worksheet

[gandalf]

I have a workbook with two sheets in it.
The first sheet is a form (template).
The second sheet has a list of names and email adresses:
Sheet2.range(A1) = name1, Sheet2.range(B1) = email adress1
Sheet2.range(A2) = name2, Sheet2.range(B2) = email adress2, etc... up till 9.
People fill in the first sheet and then push a command button to registrer it to an overview list and the document will be automatically sent to some recipients which are on the second sheet. Now I want to send ONLY ONE mail to all nine recipients instead of having to "answer" nine times yes on the question "A program wants to send automatically an e-mail in your name..." Is this possible?
I also would like a code that if I expand or shorten my recipients list I don't have to adapt my macro or my range.

I have allready searched several hours on the board and up till now no other topic was satisfying.

Thanks in advance

 

[Damon Ostrander]

Hi gandalf,

Perhaps this will get you going. This is a macro that demonstrates setting up an email to a recipient list that is on a worksheet.

Sub SendEmOut()

'E-mails this workbook as an attachment to all the recipients
'listed in column A of worksheet 1

Dim Recips(1 To 3) As String 'Recipient array
Dim RecipVal As Variant
Dim LastRow As Integer

LastRow = Worksheets(1).[a65536].End(xlUp).Row

'Load array of recipients from active sheet column 1
For i = 1 To LastRow
Recips(i) = Worksheets(1).Cells(i, 1)
Next i

RecipVal = Recips

ThisWorkbook.SendMail Recipients:=RecipVal, _
Subject:="Test of Multiple Recipients"

End Sub

 

[gandalf]

Thanks Damon!!!!!

Worked like a charm.