Steve,

Here is some code to get you started.

The SendMail didn't work for me, but as I never use SendMail, I have no idea
why. Perhaps you will. It just picks up the address from column A

Sub SendSheets()
Dim thisSheet As Worksheet
Dim cLastRow As Long
Dim clastcol As Long
Dim arySheets
Dim i As Long, j As Long

cLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set thisSheet = ActiveSheet
For i = 1 To cLastRow
clastcol = thisSheet.Cells(i, Columns.Count).End(xlToLeft).Column
On Error Resume Next
Erase arySheets
On Error GoTo 0
ReDim arySheets(0 To clastcol - 2)
For j = 2 To clastcol
arySheets(j - 2) = thisSheet.Cells(i, j).Value
Next j
Worksheets(arySheets).Copy
ActiveWorkbook.SendMail thisSheet.Cells(i, 1).Value
ActiveWorkbook.Close savechanges:=False
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" wrote in message
oups.com...
I'm sure I saw this done a year or so ago, when I was looking something
else up, but naturally can't find it now.

I'm trying to set up an automated distribution list, triggered by a
command button. I know it can be done with the code:

ActiveWorkbook.SendMail ",
") etc

... but I want different sheets in the workbook to go to different
people. Therefore I'd set up a list with:

A B C D
dave@... sheet1 sheet4 sheet 5
bob@... sheet1 sheet2 sheet3
derek@... sheet4 sheet5 sheet6

Is there a way to have a macro look down column A and then send the
listed sheets to the appropriate address? I'm assuming that there'd be
a VLookup equivalent needed..?

Thanks in advance

Steve