| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not email sheet if nothing on it.
Hello from Steved
Ok I have Ron's email program which emails to seperate accounts. My question is if on Cell B6 the cell has no values in it, is their away I can instruct the program not to send that particular worksheet. I have 10 worksheets sometimes only 8 off them need to be emailed. Thankyou. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do not email sheet if nothing on it.
Which sub Steve ?
Can you post the link -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Ok I have Ron's email program which emails to seperate accounts. My question is if on Cell B6 the cell has no values in it, is their away I can instruct the program not to send that particular worksheet. I have 10 worksheets sometimes only 8 off them need to be emailed. Thankyou. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do not email sheet if nothing on it.
Hello Ron From Steved
Ron the file I emailed you last week. Would you be so kind and have a look at it again. Ok this is what I am asking and that is, take City (1) as an example I have no values in cell B6 is it possible not to Email City (1) but email the rest. Thankyou. "Ron de Bruin" wrote: Which sub Steve ? Can you post the link -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Ok I have Ron's email program which emails to seperate accounts. My question is if on Cell B6 the cell has no values in it, is their away I can instruct the program not to send that particular worksheet. I have 10 worksheets sometimes only 8 off them need to be emailed. Thankyou. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do not email sheet if nothing on it.
Hi Steve
Please post a link from a example page from my site the next time that looks like it. I don't remember all the code I see<g I believe you used this example ? http://www.rondebruin.nl/mail/folder1/mail2.htm Example 2 Try this with this check If Sheets(Shname(N)).Range("B6") < "" Then Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") Addr = ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) If Sheets(Shname(N)).Range("B6") < "" Then Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With End If Next N Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Ron the file I emailed you last week. Would you be so kind and have a look at it again. Ok this is what I am asking and that is, take City (1) as an example I have no values in cell B6 is it possible not to Email City (1) but email the rest. Thankyou. "Ron de Bruin" wrote: Which sub Steve ? Can you post the link -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Ok I have Ron's email program which emails to seperate accounts. My question is if on Cell B6 the cell has no values in it, is their away I can instruct the program not to send that particular worksheet. I have 10 worksheets sometimes only 8 off them need to be emailed. Thankyou. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do not email sheet if nothing on it.
Sorry Ron
Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy hh-mm-ss") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)", "Waiheke Depot (9)") Addr = ", ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) If Shname(N) = "Summary" Then Sheets(Shname(N)).Copy Else Sheets(Array("Summary", Shname(N))).Copy End If Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False Set wb = ActiveWorkbook If Shname(N) < "Summary" Then Application.DisplayAlerts = False wb.Sheets("Summary").Delete Application.DisplayAlerts = True End If With wb .SaveAs "C:/Audit Reports/" & Format(Now, "dd - mm - yy, hh - mm - ss") & ".xls" .SendMail Addr(N), "Audit Summary from Mr R.Stephens" .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Please post a link from a example page from my site the next time that looks like it. I don't remember all the code I see<g I believe you used this example ? http://www.rondebruin.nl/mail/folder1/mail2.htm Example 2 Try this with this check If Sheets(Shname(N)).Range("B6") < "" Then Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") Addr = ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) If Sheets(Shname(N)).Range("B6") < "" Then Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With End If Next N Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Ron the file I emailed you last week. Would you be so kind and have a look at it again. Ok this is what I am asking and that is, take City (1) as an example I have no values in cell B6 is it possible not to Email City (1) but email the rest. Thankyou. "Ron de Bruin" wrote: Which sub Steve ? Can you post the link -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Ok I have Ron's email program which emails to seperate accounts. My question is if on Cell B6 the cell has no values in it, is their away I can instruct the program not to send that particular worksheet. I have 10 worksheets sometimes only 8 off them need to be emailed. Thankyou. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do not email sheet if nothing on it.
Hi Steve
Same answer Add If Sheets(Shname(N)).Range("B6") < "" Then after For N = LBound(Shname) To UBound(Shname) And one End if before Next N -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Sorry Ron Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy hh-mm-ss") Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)", "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)", "Swanson Depot (7)", "Panmure Depot (8)", "Waiheke Depot (9)") Addr = ", ", ", ", ", ", ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) If Shname(N) = "Summary" Then Sheets(Shname(N)).Copy Else Sheets(Array("Summary", Shname(N))).Copy End If Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False Set wb = ActiveWorkbook If Shname(N) < "Summary" Then Application.DisplayAlerts = False wb.Sheets("Summary").Delete Application.DisplayAlerts = True End If With wb .SaveAs "C:/Audit Reports/" & Format(Now, "dd - mm - yy, hh - mm - ss") & ".xls" .SendMail Addr(N), "Audit Summary from Mr R.Stephens" .Close False End With Next N Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Steve Please post a link from a example page from my site the next time that looks like it. I don't remember all the code I see<g I believe you used this example ? http://www.rondebruin.nl/mail/folder1/mail2.htm Example 2 Try this with this check If Sheets(Shname(N)).Range("B6") < "" Then Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") Addr = ", ", ", ") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) If Sheets(Shname(N)).Range("B6") < "" Then Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With End If Next N Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron From Steved Ron the file I emailed you last week. Would you be so kind and have a look at it again. Ok this is what I am asking and that is, take City (1) as an example I have no values in cell B6 is it possible not to Email City (1) but email the rest. Thankyou. "Ron de Bruin" wrote: Which sub Steve ? Can you post the link -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello from Steved Ok I have Ron's email program which emails to seperate accounts. My question is if on Cell B6 the cell has no values in it, is their away I can instruct the program not to send that particular worksheet. I have 10 worksheets sometimes only 8 off them need to be emailed. Thankyou. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| send email to each customer email in excel sheet. | Excel Discussion (Misc queries) | |||
| email a sheet | Setting up and Configuration of Excel | |||
| eMail a sheet | Excel Programming | |||
| eMail a sheet | Excel Programming | |||
| eMail a sheet | Excel Programming | |||
Linear Mode
