Email specific sheets
 

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

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

Maybe you like to use a template to do this
http://www.rondebruin.nl/mail/templates.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Phillips" wrote in message ...
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

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

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

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


"Steve" wrote in message
oups.com...
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

You are welcome



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Steve" wrote in message oups.com...
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