Re: Email from Excel with Selection of Cell Value

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:FE0E29BF-7F6F-43DD-AB7D-E1FB43B5A6CB@xxxxxxxxxxxxxxxx
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 francizyeo@xxxxxxxxx Yes 6-Feb-07 Tender ABC 15
francis francizyeo@xxxxxxxxx Yes 6-Feb-07 Sales xyz 10
Joey francizyeo@xxxxxxxxx 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(xlCellTypeConstants)
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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:11B7C344-B604-4769-B9D3-1957A425655B@xxxxxxxxxxxxxxxx
> 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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:7C848335-DC2C-4841-8819-431D4F37E8D3@xxxxxxxxxxxxxxxx
>> > 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
>>



.

Quantcast