email certain rows.

[Sjakkie]

How can i send an email from a table which contains data

say....
1 2 3 4 5 6 7
8
a data1-1 data1-2 data1-3 Sent
b data2-1 data2-2 data2-3
c data3-1 data3-2 data3-3 Sent
d data4-1 data4-2 data4-3 Sent
e data5-1 data5-2 data5-3

How can i send only the rows that dont have sent in column 4 and allows me
to modify which other columns i want to ignore. I have 10 columns of data
per row.

 

[Ron de Bruin]

Hi Sjakkie

You can use Autofilter to filter on column 4 and then use
http://www.rondebruin.nl/mail/folder2/mail4.htm

 

[Sjakkie]

i want something like the following. thought this does not select anything
and gives a big error.


Sub jack()
Dim cnt As Long
Dim lRow As Long 'Last Row

lRow = ActiveSheet.Range("q" & _
ActiveSheet.Rows.Count).End(xlUp).Row
For cnt = 1 To lRow
If ActiveSheet.Range("q" & cnt).Text = "Not Sent" Then
ActiveSheet.Range("cnt").Select





End If

Next cnt


' 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 = ""
.Item.Subject = "My subject"
.Item.Send
End With

End Sub

 

[Ron de Bruin]

If you filter and want to send all visible data on the sheet you can replace this line

Set rng = selection.SpecialCells(xlCellTypeVisible)

With

Set Rng = Sheets("YourSheet").UsedRange.SpecialCells(xlCellTypeVisible)