VBA code- Send email from Excel to Outlook

[Dancarro]

Hi,

I have the below vba code to send an email from an Excel spreadsheet, so the vba code works but the body does not appear, so I think I have something wrong.

I would appreciate any help.

Thank you.
Daniel

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim lDataRow As Long


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

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

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear" & .Range("B" & lDataRow) & _
vbNewLine & vbNewLine & "Reminder of Return for:" & _
vbNewLine & vbNewLine & .Range("D" & lDataRow) & _
vbNewLine & vbNewLine & .Range("E" & lDataRow) & _
vbNewLine & vbNewLine & .Range("F" & lDataRow) & _
vbNewLine & vbNewLine & "Note: Please do not reply to this email" & _
vbNewLine & vbNewLine & "Kind Regards, "

'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'Or use Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

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

 

[SQUIDD]

Hi

try

[COLOR=#212121][FONT=Calibri].Body = "Dear" &  Range("B" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        vbfl & vblf & "Reminder of Return for:" & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vbfl & vblf[/FONT][/COLOR] &  Range("D" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vbfl & vblf & [/FONT][/COLOR]Range("E" & lDataRow).value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vbfl & vblf & [/FONT][/COLOR]Range("F" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vbfl & vblf & [/FONT][/COLOR]"Note: Please do not reply to this email" & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vbfl & vblf &[/FONT][/COLOR] "Kind Regards, "

dave
[/FONT][/COLOR]

 

[SQUIDD]

[COLOR=#212121][FONT=Calibri].Body = "Dear" &  Range("B" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        vblf & vblf & "Reminder of Return for:" & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf[/FONT][/COLOR] &  Range("D" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf & [/FONT][/COLOR]Range("E" & lDataRow).value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf & [/FONT][/COLOR]Range("F" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf & [/FONT][/COLOR]"Note: Please do not reply to this email" & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf &[/FONT][/COLOR] "Kind Regards, "[/FONT][/COLOR]

sorry, this one

 

[Dancarro]

[COLOR=#212121][FONT=Calibri].Body = "Dear" &  Range("B" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        vblf & vblf & "Reminder of Return for:" & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf[/FONT][/COLOR] &  Range("D" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf & [/FONT][/COLOR]Range("E" & lDataRow).value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf & [/FONT][/COLOR]Range("F" & lDataRow) .value & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf & [/FONT][/COLOR]"Note: Please do not reply to this email" & _[/FONT][/COLOR]
[COLOR=#212121][FONT=Calibri]                        [COLOR=#212121][FONT=Calibri]vblf & vblf &[/FONT][/COLOR] "Kind Regards, "[/FONT][/COLOR]

sorry, this one

Hi Dave,
I tried it but still not working

 

[SQUIDD]

hi

so it does send your mail, but just no body?

dave

 

[daverunt]

You don't have a value for lDataRow?
and you are using .Range instead of Range as previously pointed out

 .Body = "Dear" & Range("B" & lDataRow).value & _
 vbNewLine & vbNewLine & "Reminder of Return for:" & _
 vbNewLine & vbNewLine & Range("D" & lDataRow).value & _
 vbNewLine & vbNewLine & Range("E" & lDataRow).value & _
 vbNewLine & vbNewLine & Range("F" & lDataRow).value & _
 vbNewLine & vbNewLine & "Note: Please do not reply to this email" & _
 vbNewLine & vbNewLine & "Kind Regards, "

 

[Dancarro]

Hi Dave,

I managed a way to insert the body by using the following approach which does the job:

.Body = "Dear " & Cells(cell.Row, "C").Value _
& vbNewLine & vbNewLine & _
"Reminder Deadlines:" _
& vbNewLine & vbNewLine & _
Cells(cell.Row, "E") _
& vbNewLine & vbNewLine & _
Cells(cell.Row, "F") _

Thank you very much for your assistance, much appreciated

 

[SQUIDD]

glad you got it to work

dave