Thanks .. this is great ... and it finishes the project!
"Ron de Bruin" wrote in message
...
Hi Annette

Change every ThisWorkbook in the code to ActiveWorkbook


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Annette" wrote in message
...
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