Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 3rd, 2009, 09:35 AM
Registered User
Join Date: Sep 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need to break out sheets and send e-mails

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!
Old September 3rd, 2009, 10:07 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


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

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"
   Set oReport = Nothing
Next oSht

End Sub
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Preventing User Code Break via Control Break key chris.bragg Access VBA 2 May 13th, 2009 09:52 AM
Error when i send mails ssomchai BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 May 7th, 2008 03:44 AM
Send e-mails to multiple recipients using database misskaos Classic ASP Basics 6 October 17th, 2006 11:09 PM
send & recive mails from exchange & lotus sever zaheersharief Pro VB 6 1 May 16th, 2005 06:55 AM
How to send e-mails??? Varg_88 Classic ASP Basics 4 December 31st, 2004 03:42 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.