Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
..Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Hi Ron,

Thanks in advance! I have attached here the table and the codes adapted from
you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with
formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E,
Event in Col F, Prd in Col G and Hldg in Col H.
Using the below code, I always get the subject line with "Reminder : Sales
xyz" for the 3 mail send. Is it possbile to have the codes loops thru the
cells and put the relevant values in the subject line.

Further, the msg body only appear as " I refers to the above subject event,
Please be remind that the date to act on this is approaching. Detail are : "
Its does not shows the cells' contents as desired, ie the contents in Col E,
Col F,
Col G and Col H


Name Emails Date Remind Deadline Event Prd
Holdg
franciz Yes 6-Feb-07 Tender ABC 15
francis Yes 6-Feb-07 Sales xyz 10
Joey Yes 5-Feb-07 Auction abc 5

Option Explicit

' This marco is with courtesy from Ron de Bruin @
http://www.rondebruin.nl/tips.htm


Sub Reminder2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder :" & " " &
ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"I refers to the above subject event, Please be
remind that the date to act on this is approaching." & vbNewLine & vbNewLine
& _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " &
ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " &
ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " &
ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : "
& This Workbook.Sheets("sheet1").Range("E")

.Send 'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Appreciate your help in this.

cheers, franciz

"Ron de Bruin" wrote:

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Hi franciz

When you use this it use the values from the row

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" I refers to the above subject event, Please be remind that the date to act on this is approaching." &
vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " & Cells(cell.Row, "F").Value & vbNewLine & _
" Prd : " & Cells(cell.Row, "G").Value & vbNewLine & _
" Hldg : " & Cells(cell.Row, "H").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "E").Value


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks in advance! I have attached here the table and the codes adapted from
you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with
formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E,
Event in Col F, Prd in Col G and Hldg in Col H.
Using the below code, I always get the subject line with "Reminder : Sales
xyz" for the 3 mail send. Is it possbile to have the codes loops thru the
cells and put the relevant values in the subject line.

Further, the msg body only appear as " I refers to the above subject event,
Please be remind that the date to act on this is approaching. Detail are : "
Its does not shows the cells' contents as desired, ie the contents in Col E,
Col F,
Col G and Col H


Name Emails Date Remind Deadline Event Prd
Holdg
franciz Yes 6-Feb-07 Tender ABC 15
francis Yes 6-Feb-07 Sales xyz 10
Joey Yes 5-Feb-07 Auction abc 5

Option Explicit

' This marco is with courtesy from Ron de Bruin @
http://www.rondebruin.nl/tips.htm


Sub Reminder2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder :" & " " &
ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"I refers to the above subject event, Please be
remind that the date to act on this is approaching." & vbNewLine & vbNewLine
& _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " &
ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " &
ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " &
ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : "
& This Workbook.Sheets("sheet1").Range("E")

.Send 'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Appreciate your help in this.

cheers, franciz

"Ron de Bruin" wrote:

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Note that in this example "Sheet1" must be active"

If not post back, we add a sheet reference to the code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi franciz

When you use this it use the values from the row

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" I refers to the above subject event, Please be remind that the date to act on this is approaching." &
vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " & Cells(cell.Row, "F").Value & vbNewLine & _
" Prd : " & Cells(cell.Row, "G").Value & vbNewLine & _
" Hldg : " & Cells(cell.Row, "H").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "E").Value


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks in advance! I have attached here the table and the codes adapted from
you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with
formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E,
Event in Col F, Prd in Col G and Hldg in Col H.
Using the below code, I always get the subject line with "Reminder : Sales
xyz" for the 3 mail send. Is it possbile to have the codes loops thru the
cells and put the relevant values in the subject line.

Further, the msg body only appear as " I refers to the above subject event,
Please be remind that the date to act on this is approaching. Detail are : "
Its does not shows the cells' contents as desired, ie the contents in Col E,
Col F,
Col G and Col H


Name Emails Date Remind Deadline Event Prd
Holdg
franciz Yes 6-Feb-07 Tender ABC 15
francis Yes 6-Feb-07 Sales xyz 10
Joey Yes 5-Feb-07 Auction abc 5

Option Explicit

' This marco is with courtesy from Ron de Bruin @
http://www.rondebruin.nl/tips.htm


Sub Reminder2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder :" & " " &
ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"I refers to the above subject event, Please be
remind that the date to act on this is approaching." & vbNewLine & vbNewLine
& _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " &
ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " &
ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " &
ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : "
& This Workbook.Sheets("sheet1").Range("E")

.Send 'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Appreciate your help in this.

cheers, franciz

"Ron de Bruin" wrote:

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Hi Ron,

It's EXCELLENT!!. Thank you very much. Except for the security dialog box
that
appear for every mail ( guess I have to click about 50 to 70 times a day for
this )
Is there anyway to do away this?

If you don't mind, would you explain the Offset in these lines of codes, not
sure how the OFFSET works here.

How come its works without using ThisWorkbook.Sheet("Sheet1")? and
do I write as ThisWorkbook.Sheet("Sheet1"Cells(cell.Row, "G").Value if made
reference to Sheet1 ?

Which Excel VBA book would you advise a beginner to read? I have Excel 2003
Power Programming by JW, but find it a little over my head.

Hope I am not asking too much ;)

If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then

..Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _

"Ron de Bruin" wrote:

Note that in this example "Sheet1" must be active"

If not post back, we add a sheet reference to the code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi franciz

When you use this it use the values from the row

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" I refers to the above subject event, Please be remind that the date to act on this is approaching." &
vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " & Cells(cell.Row, "F").Value & vbNewLine & _
" Prd : " & Cells(cell.Row, "G").Value & vbNewLine & _
" Hldg : " & Cells(cell.Row, "H").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "E").Value


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks in advance! I have attached here the table and the codes adapted from
you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with
formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E,
Event in Col F, Prd in Col G and Hldg in Col H.
Using the below code, I always get the subject line with "Reminder : Sales
xyz" for the 3 mail send. Is it possbile to have the codes loops thru the
cells and put the relevant values in the subject line.

Further, the msg body only appear as " I refers to the above subject event,
Please be remind that the date to act on this is approaching. Detail are : "
Its does not shows the cells' contents as desired, ie the contents in Col E,
Col F,
Col G and Col H


Name Emails Date Remind Deadline Event Prd
Holdg
franciz Yes 6-Feb-07 Tender ABC 15
francis Yes 6-Feb-07 Sales xyz 10
Joey Yes 5-Feb-07 Auction abc 5

Option Explicit

' This marco is with courtesy from Ron de Bruin @
http://www.rondebruin.nl/tips.htm


Sub Reminder2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder :" & " " &
ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"I refers to the above subject event, Please be
remind that the date to act on this is approaching." & vbNewLine & vbNewLine
& _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " &
ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " &
ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " &
ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : "
& This Workbook.Sheets("sheet1").Range("E")

.Send 'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Appreciate your help in this.

cheers, franciz

"Ron de Bruin" wrote:

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Hi franciz


You can use CDO if it is possible to avoid the security warning
http://www.rondebruin.nl/cdo.htm


We loop through all the cells with a value in the B column of the sheet "Sheet1"
Cell in the loop is always a cell in column B in the sheet "Sheet1" now

For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants



We check the cell value (cell in B) to be sure that there is a mail address

If cell.Value Like "?*@?*.?*"



Offset use (Row, column)
So we go one column to the right (column C in the same row(we use 0 for the row) )
And we check if the value = "yes"

And LCase(cell.Offset(0, 1).Value) = "yes"



Now we go two columns to the right (column D) and check if the value = "send"

And LCase(cell.Offset(0, 2).Value) < "send" Then



How come its works without using ThisWorkbook.Sheet("Sheet1")? and
do I write as ThisWorkbook.Sheet("Sheet1"Cells(cell.Row, "G").Value if made
reference to Sheet1 ?

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

Without a sheet reference cells always point to the activesheet.
You can this to point to the "Sheet1"
Sheets("Sheet1").Cells(cell.Row, "F").Value

I think i will change my example soon to avoid the offset.
Maybe easier to understand and to change then.





Which Excel VBA book would you advise a beginner to read? I have Excel 2003
Power Programming by JW, but find it a little over my head.

It is one of the best books there is I think for all levels.
There are also dummy books but if you are reading the newsgroups and use the book you are OK.
Remember there is no book where you can find your specific problem.

Install my GoogleSearch add-in to find information in old postings
http://www.rondebruin.nl/Google.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

It's EXCELLENT!!. Thank you very much. Except for the security dialog box
that
appear for every mail ( guess I have to click about 50 to 70 times a day for
this )
Is there anyway to do away this?

If you don't mind, would you explain the Offset in these lines of codes, not
sure how the OFFSET works here.

How come its works without using ThisWorkbook.Sheet("Sheet1")? and
do I write as ThisWorkbook.Sheet("Sheet1"Cells(cell.Row, "G").Value if made
reference to Sheet1 ?

Which Excel VBA book would you advise a beginner to read? I have Excel 2003
Power Programming by JW, but find it a little over my head.

Hope I am not asking too much ;)

If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _

"Ron de Bruin" wrote:

Note that in this example "Sheet1" must be active"

If not post back, we add a sheet reference to the code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi franciz

When you use this it use the values from the row

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" I refers to the above subject event, Please be remind that the date to act on this is approaching." &
vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " & Cells(cell.Row, "F").Value & vbNewLine & _
" Prd : " & Cells(cell.Row, "G").Value & vbNewLine & _
" Hldg : " & Cells(cell.Row, "H").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "E").Value


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks in advance! I have attached here the table and the codes adapted from
you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with
formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E,
Event in Col F, Prd in Col G and Hldg in Col H.
Using the below code, I always get the subject line with "Reminder : Sales
xyz" for the 3 mail send. Is it possbile to have the codes loops thru the
cells and put the relevant values in the subject line.

Further, the msg body only appear as " I refers to the above subject event,
Please be remind that the date to act on this is approaching. Detail are : "
Its does not shows the cells' contents as desired, ie the contents in Col E,
Col F,
Col G and Col H


Name Emails Date Remind Deadline Event Prd
Holdg
franciz Yes 6-Feb-07 Tender ABC 15
francis Yes 6-Feb-07 Sales xyz 10
Joey Yes 5-Feb-07 Auction abc 5

Option Explicit

' This marco is with courtesy from Ron de Bruin @
http://www.rondebruin.nl/tips.htm


Sub Reminder2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder :" & " " &
ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"I refers to the above subject event, Please be
remind that the date to act on this is approaching." & vbNewLine & vbNewLine
& _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " &
ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " &
ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " &
ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : "
& This Workbook.Sheets("sheet1").Range("E")

.Send 'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Appreciate your help in this.

cheers, franciz

"Ron de Bruin" wrote:

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz

Hi Ron,

Simply Great ! Thank you for the guide and patience. I appreciate every
words and I have learn something new and useful. Hope that I can learn this
stuff and assist others too. Btw, if you change the codes, pls let me know.

Cheers, franciz

"Ron de Bruin" wrote:

Hi franciz


You can use CDO if it is possible to avoid the security warning
http://www.rondebruin.nl/cdo.htm


We loop through all the cells with a value in the B column of the sheet "Sheet1"
Cell in the loop is always a cell in column B in the sheet "Sheet1" now

For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants



We check the cell value (cell in B) to be sure that there is a mail address

If cell.Value Like "?*@?*.?*"



Offset use (Row, column)
So we go one column to the right (column C in the same row(we use 0 for the row) )
And we check if the value = "yes"

And LCase(cell.Offset(0, 1).Value) = "yes"



Now we go two columns to the right (column D) and check if the value = "send"

And LCase(cell.Offset(0, 2).Value) < "send" Then



How come its works without using ThisWorkbook.Sheet("Sheet1")? and
do I write as ThisWorkbook.Sheet("Sheet1"Cells(cell.Row, "G").Value if made
reference to Sheet1 ?

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

Without a sheet reference cells always point to the activesheet.
You can this to point to the "Sheet1"
Sheets("Sheet1").Cells(cell.Row, "F").Value

I think i will change my example soon to avoid the offset.
Maybe easier to understand and to change then.





Which Excel VBA book would you advise a beginner to read? I have Excel 2003
Power Programming by JW, but find it a little over my head.

It is one of the best books there is I think for all levels.
There are also dummy books but if you are reading the newsgroups and use the book you are OK.
Remember there is no book where you can find your specific problem.

Install my GoogleSearch add-in to find information in old postings
http://www.rondebruin.nl/Google.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

It's EXCELLENT!!. Thank you very much. Except for the security dialog box
that
appear for every mail ( guess I have to click about 50 to 70 times a day for
this )
Is there anyway to do away this?

If you don't mind, would you explain the Offset in these lines of codes, not
sure how the OFFSET works here.

How come its works without using ThisWorkbook.Sheet("Sheet1")? and
do I write as ThisWorkbook.Sheet("Sheet1"Cells(cell.Row, "G").Value if made
reference to Sheet1 ?

Which Excel VBA book would you advise a beginner to read? I have Excel 2003
Power Programming by JW, but find it a little over my head.

Hope I am not asking too much ;)

If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _

"Ron de Bruin" wrote:

Note that in this example "Sheet1" must be active"

If not post back, we add a sheet reference to the code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi franciz

When you use this it use the values from the row

.To = cell.Value
.Subject = "Reminder :" & " " & _
Cells(cell.Row, "F").Value & " " & _
Cells(cell.Row, "G").Value

.Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" I refers to the above subject event, Please be remind that the date to act on this is approaching." &
vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " & Cells(cell.Row, "F").Value & vbNewLine & _
" Prd : " & Cells(cell.Row, "G").Value & vbNewLine & _
" Hldg : " & Cells(cell.Row, "H").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "E").Value


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks in advance! I have attached here the table and the codes adapted from
you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with
formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E,
Event in Col F, Prd in Col G and Hldg in Col H.
Using the below code, I always get the subject line with "Reminder : Sales
xyz" for the 3 mail send. Is it possbile to have the codes loops thru the
cells and put the relevant values in the subject line.

Further, the msg body only appear as " I refers to the above subject event,
Please be remind that the date to act on this is approaching. Detail are : "
Its does not shows the cells' contents as desired, ie the contents in Col E,
Col F,
Col G and Col H


Name Emails Date Remind Deadline Event Prd
Holdg
franciz Yes 6-Feb-07 Tender ABC 15
francis Yes 6-Feb-07 Sales xyz 10
Joey Yes 5-Feb-07 Auction abc 5

Option Explicit

' This marco is with courtesy from Ron de Bruin @
http://www.rondebruin.nl/tips.htm


Sub Reminder2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "send" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder :" & " " &
ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"I refers to the above subject event, Please be
remind that the date to act on this is approaching." & vbNewLine & vbNewLine
& _
" Detail are : " & vbNewLine & vbNewLine & _
" Event : " &
ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " &
ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " &
ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : "
& This Workbook.Sheets("sheet1").Range("E")

.Send 'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Appreciate your help in this.

cheers, franciz

"Ron de Bruin" wrote:

Yes, that is possible

Show the code that you use now.
I will change it for you then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,
Thank you for your response and guide,the code will produce always for cells
"F3" and "G3" in the subject.

Is it possible to have Excel automatically include the cell's contents in
col F and col G to the corresponsing row its send.
What I have in mind is that :
For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will
also include the Cell contents from C5 and F7 as well as C5 and G7 in the
subject line after "Reminder". ie can excel loop thru and insert the contents
found in the cells

Thank in advance.

cheers, franciz




"Ron de Bruin" wrote:

On each page there is a link to a tips page
You can use this

.Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " &
ThisWorkbook.Sheets("Sheet1").Range("G3").Value




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all,

I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm.
However, I would like to include the value of the cell in the subject line.

For example, I would like to have the contents in row 3, col F and row 3,
col G
appear in the subject after "reminder"

How do I include this in the codes.
.Subject = "Reminder"
And is it possible to include the row's content into the body of the msg?

Thank in advance.

cheers, franciz