Make this the active workbook

[Annette]

How can I make this code work in whatever workbook is the current active
workbook? Right now, the only way it will work is to paste it into the
workbook module. I want to put it in my personal.xls and run it from any
workbook.
Thanks.

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String

ShtCount = ActiveWorkbook.Sheets.Count <===== I thought this did it,
but it doesn't do the trick

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.name & " of " _
& ThisWorkbook.name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Annette

Change every ThisWorkbook in the code to ActiveWorkbook

 

[Annette]

Thanks .. this is great ... and it finishes the project!