See if this works as intended:
Option Explicit
Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim sDate As String
Dim sAddy As String
sDate = Format(Now, "dd-mm-yy")
sAddy = ActiveSheet.Range("Q1").Value
On Error GoTo ErrHandle
With Application
.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name & " " & sDate & ".xls"
.SendMail sAddy, "This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
ErrExit:
.ScreenUpdating = True
End With
Exit Sub
ErrHandle:
MsgBox Err.Description
GoTo ErrExit
End Sub
Quote:
|
Originally Posted by nospaminlich
I have a workbook with a number of sheets containing individuals results.
Each sheet is named after the person, e.g. Fred, Janet, John, Susan.
I'd like to be able to press a button on each sheet and for that individual
sheet to be mailed to the person based on their e-mail address in cell Q1.
Over time sheets will be added/deleted as people move on/join.
Is there an easy way to set this up as the person using it has very limited
Excel skills?
Thanks in anticipation
|