Excel 2007 : Mail Every Worksheet

[billy2willy]

Hi,

I am using Excel 07 and I have about 40 departments and each department is on a different spreadsheet. On the applicable worksheet the emal address of the department head is in cell a1 and another managers email address is in b1.

I want to be able to send these with a macro to the various department heads using Microsoft Outlook 07. I had this set up in 03 but the same Marco does not work that great in 07.

I am thinking (excel 03 couldn't but maybe 07 can) also that I want to record a set "blah blah blah" in the body of the email as well and record a set subject line and instead of sending it to the outbox send it to the drafts folder so I have the option of revising it if I want. The following is the Marco recorded in 03

"

Sub Outlook_Mail_Every_Worksheet()
Dim OutApp As Object
Dim OutMail As Object
Dim strdate As String
Dim wb As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
For Each ws In ThisWorkbook.Worksheets
If ws.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
ws.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & ws.Name & " of " _
& ThisWorkbook.Name & " " & strdate
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ws.Range("a1").Value
.CC = ws.Range("b1").Value
.Subject = "April 2010 Budget Statement"

.Attachments.Add wb.FullName
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Set OutMail = Nothing
End If
Next ws
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

"

Any suggestions of an updated Macro?

Thanks,