mailing one sheet from workbook

G

Guest

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
 
J

JustinLabenne

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
 
Top