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
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