trying to email parts of a spreadsheet to individuals

[Kdes24uk]

Hi
I am looking for some guidence on the following -

I have a spreadsheet of my ALL my staff Clocking times that I want to email to each member of staff individually (just their own times). There are page breakes separating each employee, but the length is variable depending on how many clockings they did in a particular week.

I can insert email addresses etc as a lookup and have automated the task of sending the SAME report to ALL individuals but for privacy would like to ensure that they only get therir own section.

Can anyone give me some pointers ?

the structure is -

SMITH, JOHN Dept A

Date Clockings Normal Hrs Actual Daily
Dec 05 2005
07:58 Clock 1 7.24 7.40 0.16 12.36
12:25 Clock 1
12:47 Clock 1
16:10 Clock 1

Tue, Dec 06 2005
08:04 Clock1 7.24 7.31 0.07 12.40
12:53 Clock1
13:21 Clock1
16:05 Clock1
etc etc

(Page break)

JONES, JANE Sales Support

Date Clockings Normal Hrs Actual Daily
Mon, Dec 05 2005
07:58 Clock1 7.24 7.40 0.16 12.36
12:25 Clock1
12:47 Clock1
16:10 Clock1

Tue, Dec 06 2005
08:04 Clock1 7.24 7.31 0.07 12.43
12:53 Clock1
13:21 Clock1
16:05 Clock1

etc etc

(page break)

 

[Kdes24uk]

this code would seem to be a starting point but I dont know how to repeat it for each individual. Also the range a1:b5 would have to be variable or a 'page' - am I on the right track ?


Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:B5").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "My subject"
.Item.Send
End With
End Sub

 

[MDuff]

Hi Welcome to the bord

This link has a lot of code for sending emails in excel

http://www.rondebruin.nl/sendmail.htm

I think the part on sending ranges will work for you although you may need to adjust it.