Email specific sheets

S

Steve

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 Array("[email protected]",
"[email protected]") 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
 
B

Bob Phillips

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)
 
S

Steve

The SendMail didn't work for me, but as I never use SendMail, I have
no idea why. Perhaps you will.

Bahahahahaha! Yee-esss. I find it hard enough wording my questions in
ways you guys would understand. Knowing how and why VB works is way
over my head. :)

Thanks for the reply Bob, it works perfectly. I'll probably go with
Ron's template, though.

Steve
 
S

Steve

This may get posted twice as there was a server error.

Anyhow, thanks a lot for the template Ron; it does exactly what I
needed.

Steve
 
B

Bob Phillips

I knew Ron had some code for SendMail, but I didn't realise he catered for
using it in this type of situation. Perhaps it's time to read that page
again.

Bob
 
Top