Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Emailing Sheets Code Edit
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Emailing Sheets Code Edit
just a guess but did you change the sheet name on the tab?
did you change its name in the VBA window? "Todd Huttenstine" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Emailing Sheets Code Edit
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Emailing individual sheets in workbooks on Office 2007 | Excel Discussion (Misc queries) | |||
Need help with Emailing Code Please | New Users to Excel | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
hide unnecessary sheets when emailing to others | Excel Worksheet Functions | |||
Emailing Sheets | Excel Programming |