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

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

Nice one, can it be made so that 'sEmailList can be pasted into the to
field of an e-mail? Im sure it would be ok to just paste it manualy?

This is great.
Thanks,

S


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

I cant upload an .xls atachment. The forum wont let me

--
scriblesvur
-----------------------------------------------------------------------
scriblesvurt's Profile: http://www.excelforum.com/member.php...fo&userid=2439
View this thread: http://www.excelforum.com/showthread.php?threadid=39011

Hi there,

Have a look at advice from Chip Pearson
http://www.cpearson.com/excel/clipboar.htm (which I've followed).

As Chip points out, you'll need to add a reference to the "Microsoft Forms
2.0 object library" to use the DataObject data type. To do this find the
reference via Tools\References... in the VBE window and check the
appropriate box.

Basically, the following has been added:

Dim MyDataObj As New DataObject
MyDataObj.SetText sEmailList
MyDataObj.PutInClipboard

Best regards

John



Sub email()

Dim iStartRow As Integer
Dim iLastRow As Integer
Dim iCol As Integer
Dim iEmailCol As Integer
Dim sEmailList As String
Dim MyDataObj As New DataObject

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

'Trim leading semicolon and space
sEmailList = Right(sEmailList, (Len(sEmailList) - 2))

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

'Copy string to clipboard
MyDataObj.SetText sEmailList
MyDataObj.PutInClipboard
Debug.Print sEmailList

End Sub


"scriblesvurt"
wrote in message
news:scriblesvurt.1srzyr_1122379576.2619@excelforu m-nospam.com...

Nice one, can it be made so that 'sEmailList can be pasted into the to
field of an e-mail? Im sure it would be ok to just paste it manualy?

This is great.
Thanks,

S


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