VBA Email Cell Contents When Filled In

[pt_lily]

Good morning. I have created the following spreadsheet to log requests>

A (email address)
B ("yes")
C (Date of Request)
D (Time of Request)
E (Description of Request)
F (Request Due)

This will be on ongoing log. I would like for when a new row is completed,
that a "confirmation of request" email go out to the email address listed. I
tried some things by looking at different posts and I am still getting
errors. Any help would be greatly appreciated.

This is the code that I currently have that is not working. Please let me
know if I am way off.

..To = cell.Value
..Subject = "Request Confirmation :" & " " & _
Cells(cell.Row, "E").Value & " " & _

Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" Please confirm the following report request." & vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _
" Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _
" Description : " & Cells(cell.Row, "E").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "F").Value

 

[Ron de Bruin]

Have you see
http://www.rondebruin.nl/mail/folder3/message.htm

 

[pt_lily]

Okay. So I worked with the code on the website. It is sending the emails
now, but I am not getting any information in the body of the email. Again,
any help is appreciated.

Sub EmailMacro()
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("A").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
..To = cell.Value
..Subject = "Report Request Confirmation"
..Body = " DaRon, " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
"Please confirm the following report request." & vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _
" Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _
" Description : " & Cells(cell.Row, "E").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "F").Value
..Send
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next cell

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

 

[Ron de Bruin]

Your mail addresses are in A so this will blow

cell.Offset(0, -1).Value

It will try to display the value in one column to the left of A

If B is the name

Then Try

.Body = " DaRon, " & cell.Offset(0, 1).Value & vbNewLine & vbNewLine & _
"Please confirm the following report request." & vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Date of Request : " & cell.Offset(0, 2).Value.Value & vbNewLine & _
" Time of Request : " & cell.Offset(0, 3).Value & vbNewLine & _
" Description : " & cell.Offset(0, 4).Value & vbNewLine & _
" Deadline : " & cell.Offset(0, 5).Value

 

[pt_lily]

Thank you so much for your help!! It works great now.