Excel VBA Outlook Email Help!

[Ryan Francis]

Hi all,

I have tried various websites and methods but I appear to be a little out of my depth. I would like excel to if Column AB = Yes to send an email via Outlook to an email address listed in Column W and for the persons name to be included in the email (Column V) along with other Column cells too which meet the criteria of AB = Yes.

The email I am hoping to create would be:

Dear (Column W)

Regarding (Column A) and (Column B)

The above named member of staff has been absent since (Column F) and was last contacted on (Column AA). Please arrange to make contact with the member of staff as soon as possible and complete the attached form and return to.....


Your help with this would be appreciated, this is the code I have so far:

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

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

On Error GoTo cleanup
For Each cell In Columns("W").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "AB").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Contact Reminder"
.Body = "Dear " & Cells(cell.Row, "W").Value _
THIS IS WHERE I HAVE GOT STUCK!

.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

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

Thank you in advanced!

 

[Deepak]

First, Always tag the code.
U could try vbcrlf, vbnewline ,chr(10), or html <br>

A lots of post are also containing the same type of query. Do the search also for the same.

 

[Deepak]

Oh..

I just forget to welcome u here!!!