mailing one sheet from workbook
 

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

Hi nospaminlich

See my site for code examples
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"nospaminlich" wrote in message ...
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

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