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