Dear Scribble,

I'm not an expert on interacting with email, but I've had a look at Ron de
Bruin's site and he's got some excellent stuff:
http://www.rondebruin.nl/sendmail.htm

As for getting the addresses in the first place, have a look at the code
below. Hope it helps.

Best regards

John

PS - Didn't see your attachment so hopefully I've got the jist of what
you're after.

Sub email()

Dim iStartRow As Integer
Dim iLastRow As Integer
Dim iCol As Integer
Dim iEmailCol As Integer
Dim sEmailList As String

iStartRow = 12 'Change to start row number
iLastRow = 205 'Change to last row number
iCol = 9 'Change to column number (I=9)
iEmailCol = 8 'Change to email column number (H=8)

For X = iStartRow To iLastRow
If Cells(X, iCol).Value 0 Then
sEmailList = sEmailList & "; " & Cells(X, iEmailCol).Value
End If
Next X

'sEmailList is now a string containing all the addresses _
that could be used in the To: field of an email

End Sub



"scriblesvurt"
wrote in message
news:scriblesvurt.1sroue_1122365148.6207@excelforu m-nospam.com...

Background: I have a sheet that people enter their time worked into each
week. There is a column that shows the amount of working time that they
have yet to book. Each week I have to send an e-mail to all the people
on the sheet that have 0 hours left to book.

Please see attached example J

I would like to automate this.

I would like to assign some code to a command button that performs the
following:

Reference's the column I12 through to I205.

If the cell is greater than 0 it will then copy the e-mail address from
the cell in column H,

I would then be able to paste all the e-mails addresses into an e-mail
to contact all the users. I don't know if excel has functionality to
open and e-mail and put all the addresses in or not. If not it can just
generate a list that I can paste in.

Any help really appreciated!

Thanks.


--
scriblesvurt
------------------------------------------------------------------------
scriblesvurt's Profile:
http://www.excelforum.com/member.php...o&userid=24399
View this thread: http://www.excelforum.com/showthread...hreadid=390111