Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    Alexandria, Virginia, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email Excel Spreadsheets (Excel 2000)

    Does anyone know how to email individual worksheets from an excel workbook to different recipients. I need to email leave sheets to employees from a large workbook. I want to be able to click a button or run a program that would open the workbook, read each sheet and mail it to the person on the tab. Let me know if you have any ideas or if there is a program out there that will do this. (One major problem - we have Groupwise as our email server!!!) Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Excel Spreadsheets (Excel 2000)

    You will have to write code to create a new workbook from each sheet and mail the workbooks. The code below will create the new workbooks and it shows where to put the code to mail it. However, I am not familiar enough with email to know how to do that.

    <pre>Public Sub MailSheets()
    Dim oBook As Workbook, oSheet As Worksheet
    Dim lSaveNumSheets As Long
    Dim strPath As String
    lSaveNumSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    strPath = ActiveWorkbook.Path
    If Right(strPath, 1) <> "" Then
    strPath = strPath & ""
    End If
    For Each oSheet In Worksheets
    Set oBook = Workbooks.Add
    oSheet.Cells.Copy
    oBook.Worksheets(1).Paste Destination:=oBook.Worksheets(1).Range("A1")
    oBook.Worksheets(1).Name = oSheet.Name
    oBook.SaveAs FileName:=strPath & oSheet.Name
    oBook.Close
    ' code to mail goes here
    Next oSheet
    Application.SheetsInNewWorkbook = lSaveNumSheets
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email Excel Spreadsheets (Excel 2000)

    The following code should work with Outlook, but I have never worked with groupwise, so cannot really say if it will suit you. Try placing it in Legare's code before the workbook is closed, and see if it helps.<pre>Workbooks("WorkBook Name").SendMail Recipients:="Recipient"</pre>

    You will need to substitute appropriate names for the workbook and the recipient.

    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Excel Spreadsheets (Excel 2000)

    Using Andrew's suggestion, this works with Outlook and Exchange:

    <pre>Public Sub MailSheets()
    Dim oBook As Workbook, oSheet As Worksheet
    Dim lSaveNumSheets As Long
    Dim strPath As String
    lSaveNumSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    strPath = ActiveWorkbook.Path
    If Right(strPath, 1) <> "" Then
    strPath = strPath & ""
    End If
    For Each oSheet In Worksheets
    Set oBook = Workbooks.Add
    oSheet.Cells.Copy
    oBook.Worksheets(1).Paste Destination:=oBook.Worksheets(1).Range("A1")
    oBook.Worksheets(1).Name = oSheet.Name
    oBook.SaveAs FileName:=strPath & oSheet.Name
    oBook.SendMail Recipients:=oSheet.Name
    oBook.Close
    Next oSheet
    Application.SheetsInNewWorkbook = lSaveNumSheets
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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