Solved: Where to put additional code

[Meatball]

Where in this code would I put a line to run a sub to send each page to a different recipient?


Sub SortForZeroFirst()
'
' SortForZeroFirst Macro
' Macro recorded 3/9/2009 by David D
'
'

For i = 3 To Sheets.Count
With Sheets(i)
rw = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("A5:I" & rw).Sort Key1:=.Range("I5"), Order1:=xlAscending, Key2:=.Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End With


Next
End Sub

[nst1107]

Right after your End With line, I would think, and pass Sheets(i) as an argument to the next sub.

[Bob Phillips]

Why not just add in-line?

[Meatball]

Any time I add it to the code I get a loop that will not stop.

[Bob Phillips]

Then that is a code problem, not a placement problem.

What did you add?

[Meatball]

I added Application.Run "Mail_ActiveSheet" before End With, before and after Next. Both subs run fine seperatly, just can't get them to combine.

Sub SortForZeroFirst()
'
' SortForZeroFirst Macro
' Macro recorded 3/9/2009 by David D
'
'

For i = 3 To Sheets.Count
With Sheets(i)
rw = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("A5:I" & rw).Sort Key1:=.Range("I5"), Order1:=xlAscending, Key2:=.Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

End With

Next
End Sub

Sub Mail_ActiveSheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each sh In ThisWorkbook.Worksheets
If sh.Range("A1").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " _
& Format(Now, "dd-mmm-yy")
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail sh.Range("A1").Value, _
"Weekly customer update"
On Error GoTo 0
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

[mdmackillop]

Hi Meatball,
Please use the green VBA button rather than Code tags to format your code.
Regards MD

[Meatball]

I thought the end result was the same but will use VBA button from now on. Thanks

[Kenneth Hobs]

The vba tags color code vba key words is the difference. Makes it kind of nice does it not?

SendMail may not be your best route. For some users, that won't work at all. If you use that method, it will attach the whole workbook as I understand it. Is that what you wanted?

[Bob Phillips]

I am sure that you could combine them, but doesn't this simple mod work?



Sub SortForZeroFirst()
'
' SortForZeroFirst Macro
' Macro recorded 3/9/2009 by David D
'
'

For i = 3 To Sheets.Count
With Sheets(i)
rw = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("A5:I" & rw).Sort Key1:=.Range("I5"), Order1:=xlAscending, Key2:=.Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

End With

Next

Call Mail_ActiveSheet()
End Sub

[Meatball]

xld, Perfect !!!!!!!! I had tried Run Application and put in a couple of different places and had continuos looping or no mailing. "Call" was one I had not tried. Thank you very much
Kenneth, I did have to play with the original code to get it to do only sheets instead of the whole book. I see the difference now between code and VBA tags. Yes, better.
Is there a button to mark a thread solved? If not, how do you mark?

[nst1107]

To mark the thread solved, look near the top of the page under the drop-down "Thread Tools".