Sending Emails according to sheet name
 

On Sheet2 I have a table. There is a list of Names in
Range B3:B52, however there may be only 12, 20, 35, or
however many names in that range up to 50 names. In Range
C3:C52 there are email address. These email address are
corresponding to the names.

Starting with Sheet4(there can be up to 53 sheets
including Sheet4), the sheets are going to be named with
names that match names located in Range B3:B52 on Sheet2.
I need a code that will look at each sheet name starting
with Sheet4 and then look at Range B3:B52 on Sheet2 and
when it finds the match, it will look at the corresponding
email address and send that sheet to that email address
using Outloook 2000. It dont matter how the sheet is
emailed.

Can anyone please help?

Thank you.

Sending Emails according to sheet name
 

Todd

If there are only three sheets that aren't names, then you could loop
through all the sheets and skip those three. Like this:

Dim wks as Worksheet
Dim FndRng As Range

For Each wks in ThisWorkbook.Worksheets
If Not (wks.Name = "sheet1" Or wks.Name = "sheet2" Or wks.Name =
"Sheet3") Then
Set FndRng = Sheet2.Range("B3:B52").Find(wks.Name)
If Not FndRng Is Nothing Then
MyTo = FndRng.Offset(0,1).Value
'Code to send email
End If
End If
Next wks

For the code to send a sheet by email, see here

http://www.dicks-clicks.com/excel/olSending.htm

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Todd Huttenstine" wrote in message
...
On Sheet2 I have a table. There is a list of Names in
Range B3:B52, however there may be only 12, 20, 35, or
however many names in that range up to 50 names. In Range
C3:C52 there are email address. These email address are
corresponding to the names.

Starting with Sheet4(there can be up to 53 sheets
including Sheet4), the sheets are going to be named with
names that match names located in Range B3:B52 on Sheet2.
I need a code that will look at each sheet name starting
with Sheet4 and then look at Range B3:B52 on Sheet2 and
when it finds the match, it will look at the corresponding
email address and send that sheet to that email address
using Outloook 2000. It dont matter how the sheet is
emailed.

Can anyone please help?

Thank you.