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.
Bookmarks