Results 1 to 2 of 2

Subdivide worksheets and send with macros

Threaded View

  1. #1
    Registered User
    Join Date
    03-29-2005
    Posts
    48

    Cool Subdivide worksheets and send with macros

    Hi,

    I have about 30 departments that I send month end information to in Excel. I have a Macro that divides the spreadsheets and sends each one through Microsoft Outlook to the destination email address. In the process of sending the worksheet it appears that the Macros don't send with the worksheet. I would like to send the other Macros I have programed with the worksheet. Below is the Subdivide and send macro. Any suggestions....

    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 & ".xls"
                     Set OutMail = OutApp.CreateItem(0)
                    With OutMail
                        .To = ws.Range("a1").Value
                        .CC = ws.Range("b1").Value
                        .Subject = "July 2008 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
    Last edited by Simon Lloyd; 08-21-2008 at 07:15 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to Transfer Row(s) between worksheets based on Criteria in 1 Column
    By hockeyman in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-22-2008, 02:05 PM
  2. Copy & send single sheet with values and no formulas
    By Sven in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2008, 04:56 PM
  3. Deleting worksheets, printing to Adobe, and moving to a new workbook
    By baconcow in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2008, 10:03 AM
  4. Suppressing Auto Macros
    By DCSwearingen in forum Excel General
    Replies: 3
    Last Post: 05-04-2007, 02:12 PM
  5. Macro - send a file to a specific location
    By Bob1955 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-28-2006, 03:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1