Results 1 to 5 of 5

Display only active sheet when saving and sending

Threaded View

  1. #1
    Registered User
    Join Date
    07-13-2014
    Location
    Arizona
    MS-Off Ver
    2010
    Posts
    18

    Post Display only active sheet when saving and sending

    Hello,

    I am new to the forum and have a question, I would like to only display the active sheet when I save and send. The code I currently have sends the entire workbook. Where would I put this string? My current code is listed below.
    Sub Mail_workbook_Outlook_2()
    'SOCAL
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim OutApp As Object
        Dim OutMail As Object
        
    DatePicked = Worksheets("SOCAL Daily Site Analysis").Range("C4").Value
    Title = Worksheets("SOCAL Daily Site Analysis").Range("A1").Value
    SavedName = Worksheets("SOCAL Daily Site Analysis").Range("B3").Value
    
        Set wb1 = ActiveWorkbook
        
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    
        ' Make a copy of the file.
        ' If you want to change the file name then change only TempFileName variable.
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Copy of " & wb1.Name
            FileExtStr = "." & LCase(Right(wb1.Name, _
                                       Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
    
        wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
        Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    
        Set OutApp = CreateObject("Outlook.Application")
        
        Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
        With OutMail
            .To = "scastiglia@pinnaclewireless.com"
            .CC = ""
            .BCC = ""
            .Subject = SavedName & " " & Title & " - " & DatePicked
            .Body = "Daily Jobsite Construction Activity Details for SOCAL are attached."
            .Attachments.Add wb2.FullName
            .Display
        End With
        On Error GoTo 0
    
        wb2.Close SaveChanges:=False
    
        ' Delete the file.
        Kill TempFilePath & TempFileName & FileExtStr
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Any assistance is greatly appreciated.
    Last edited by sacastiglia; 07-19-2014 at 11:04 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Renaming Active.Workbook and saving it without saving Macro
    By Djwill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2012, 03:09 AM
  2. Automatically Display a data input form when active sheet changes
    By Duncan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2005, 05:06 PM
  3. Sending active workbook as email
    By bigalmcintyre in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2005, 08:58 AM
  4. Sending active worksheet and renaming
    By Qaspec in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2005, 06:06 PM
  5. sending the active worksheet and an additional new sheet
    By Qaspec in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2005, 11:06 PM

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