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