Emailing Sheets Code Edit

[Todd Huttenstine]

I found the below code and now I am trying to edit it to
work with my program.

Here is how the code works:
The Workbook has 3 sheets named Sheet1, Sheet2, and
Sheet3. On Sheet1 there is data in Columns A, B, and C.
Names are in column A, Email addresses in Column B, and
Sheet names in column C. All the data starts on row 4 in
each column. The code looks for names starting in Cell
A4, looks for Email addresses starting in Cell B4, and
looks for the sheet name to email starting in cell C4.

The code goes through each row until there is no more data
and then it stops running. The code sends an email to the
email address in Column B. The code knows what sheet to
email because the name of the sheet is in Column C. And
the code uses the value(Persons name) in ColumnA to put in
the body of the email (example: if the value in column A
is Todd, the body of the email will say "Here are your
stats, Todd").

Here is the problem...
If the sheets are named using the default naming method
like "Sheet1, Sheet2, Sheet3, Sheet4 and so on..., the
code works fine. The working origianl code is below:

If I change the name of the sheets, the code will not
work. Can you please tell me how to fix this problem?

Thank you


Dim ol As New Outlook.Application
Dim olmail As MailItem
Dim cell As Range, cell2 As Range
Dim shRng As Range
Dim sh As Worksheet, sharr() As String
Dim wb As Workbook
Dim i As Integer

Set sh = ThisWorkbook.Sheets("sheet1")
i = 1
Set ol = New Outlook.Application

For Each cell In sh.Range("a4", Range("a4").End(xlDown))
Set shRng = cell.Offset(0, 2)
ReDim sharr(1 To shRng.Offset(0, 50).End
(xlToLeft).Column - _
shRng.Column + 1)

For Each cell2 In sh.Range(shRng, shRng.Offset(0,
50).End(xlToLeft))
sharr(i) = cell2.Value
i = i + 1
Next cell2

ThisWorkbook.Sheets(sharr).Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:="C:\Sheets.xls"

Set olmail = ol.CreateItem(olMailItem)
With olmail
.To = cell.Offset(0, 1).Value
.Subject = "Your Stats"
.Body = "Here are your stats, " & cell.Value
.Attachments.Add wb.Path & "\" & wb.Name
.Display
.Send
End With

wb.Close savechanges:=False
i = 1
Kill "c:\sheets.xls"
Next cell

 

[Gareth]

just a guess but did you change the sheet name on the tab?

did you change its name in the VBA window?

 

[Steve Hieb]

Hi Todd,

I think all you need to do is change this:

Set sh = ThisWorkbook.Sheets("sheet1")

to this:

Set sh = Sheet1

In VBA under the Project Explorer (CTRL+R) you should see the
Microsoft Excel Objects for your project. Before you rename the first
sheet you should see:

Sheet1("Sheet1")

If you change the first sheet's name to "ABC" for example, then you'll
see:

Sheet1("ABC")

If you reference the object in your code ... Sheet1 ... then you don't
have to worry about the name of the worksheet being changed by
someone. FYI, you can also change the name of the object using
Properties (F4) in VBA (although not usually necessary). Example:

FirstSheet("ABC")

In this case you could reference "FirstSheet" in your code and again
.... not worry if the worksheet name gets changed. I'm assuming the
worksheet names in column C are not causing any problem. If they are,
you might be able to modify the code with all of this in mind.

Hope this helps,
Steve Hieb