Need to break out sheets and send e-mails

ChillerK · September 3rd, 2009, 09:35 AM

Hoping you all can help me out here.

I have a workbook with ~ 25 sheets of financial data.

I am trying to write a macro that does the following for each sheet:

- makes each sheet its own workbook. Copy / paste values and formats would be perfect.

- once each sheet is broken out, attach it to an e-mail, using the e-mail address in cell C2, with the subject of the e-mail being the info in cell A1

Thanks in advance for any help!

Shasur · September 3rd, 2009, 10:07 PM

Hi

Can you try copying the sheet as a whole into a new workbook and mailing them. Here is a hint

Code:

Sub Copy_ExcelSheets()

Dim oWB As Workbook
Dim oReport As Workbook
Dim oSht As Worksheet

Set oWB = ActiveWorkbook
For Each oSht In oWB.Sheets
   Set oReport = Workbooks.Add
   oSht.Copy oReport.Sheets(1)
   oReport.SaveAs "c:\temp\sample.xls"
   oReport.SendMail "[email protected]", "Sample"
   oReport.Close
   Set oReport = Nothing
Next oSht

End Sub

Cheers
Shasur