E-mail look up

[scriblesvurt]

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.

[John]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[scriblesvurt]

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]

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

[John]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>