Results 1 to 7 of 7

Thread: How do I select more than one sheet and send as workbook

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #7
    VBAX Regular Erays's Avatar
    Joined
    Mar 2005
    Posts
    73
    Location

    Thumbs up Highlight pages and send as workbook

    This is the one that works for me. It emails from a value in a cell and names the book from a value in a cell.

    Sub EmailWithOutlookBook()
    'Variable declaration
    Dim oApp As Object, _
    oMail As Object, _
    WB As Workbook, _
    FileName As String
    'Turns off screen updating
    Application.ScreenUpdating = False
    'Makes a copy of the active sheet and save it to
    'a temporary file
    ActiveWorkbook.Windows(1).SelectedSheets.Copy
    Set WB = ActiveWorkbook
    FileName = Cells(8, 3).Value & " Reis.xls"
    On Error Resume Next
    Kill "C:\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:="C:\" & FileName
    'Creates and shows the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
    'Uncomment the line below to hard code a recipient
    .To = Cells(7, 11).Value
    .Cc = Cells(8, 11).Value
    .Bcc = Cells(9, 11).Value
    'Uncomment the line below to hard code a subject
    .Subject = "Here are the reis In workbook form, if you have questions please call "
    '.Body = "I Have attached to this email the totol loss template for claim Number " & Cells(6, 3).Value
    .Attachments.Add WB.FullName
    .Display
    End With
    'Deletes the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
    'Restores screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    End Sub
    Last edited by Erays; 03-19-2005 at 06:58 AM. Reason: Took out personal information

Posting Permissions

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