Excel 2007 : Mail Every Worksheet

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,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here