Use VBA to send HTML email with excel data

[Piip]

I am trying to set up a macro to allow a colleague to enter some data into a spreadsheet, press a button and it send multiple emails out to multiple recipients with attachments and information taken from the spreadsheet. The colleague wants some of the wording to be in bold and this where i have hit a snag. I can't get the code to be in html and pick up the data from the spreadsheet.

Sub SendEmailFromExcelWithBody()
    Dim OutApp As Object, OutMail As Object
    Dim ws As Worksheet
    Dim i As Long, lRow As Long

    Set OutApp = CreateObject("Outlook.Application")
    
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            Set OutMail = OutApp.CreateItem(0)


            With OutMail
                .Importance = 2
                .ReadReceiptRequested = True
                .To = ws.Range("A" & i).Value
                .Cc = ws.Range("B" & i).Value
                .Subject = "SSCL Devices with Out-dated Security Patches - " & Cells(i, "E")
                .HtmlBody = "Good Morning/Afternoon," _
             "Name Assigned to Device: " & ws.cells(1, 2).Value & " _
             "<br><br> Device Name: (cell.Row, "D").Value & _
             "<br><br>The device above is assigned to you and has been identified as having out of date security patches.  Your device needs to be updated immediately. If you believe this to be incorrect, please contact " & _
             "<br><b>Please reply to this email to confirm: " & _
             "<br><br>1. Your device name. Please follow the instructions in the document above" & _
              

                .Attachments.Add "attchement link"
                .Attachments.Add "attachment link"

                .Display
            End With
        Next i
    End With
End Sub

Where this code fails is here: "Name Assigned to Device: " & ws.cells(1, 2).Value & " _
I can't add the cell data and continue to the next line, it seems to want the line to end there

 

[Logit]

With the following, you will need to edit some of the code lines to match your situation there (paths & file names/locations).

Option Explicit

Sub SendEmailFromExcelWithBody()
    Dim OutApp As Object, OutMail As Object
    Dim ws As Worksheet
    Dim i As Long, lRow As Long

    Set OutApp = CreateObject("Outlook.Application")
    
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            Set OutMail = OutApp.CreateItem(0)


            With OutMail
                .Importance = 2
                .ReadReceiptRequested = True
                .To = ws.Range("A" & i).Value
                .Cc = ws.Range("B" & i).Value
                .Subject = "SSCL Devices with Out-dated Security Patches - " & Cells(i, "E")
                .HtmlBody = "Good Morning/Afternoon," & _
             "Name Assigned to Device: " & ws.Cells(1, 2).Value & _
             "<br><br>" & "Device Name: " & ws.Cells(1, 4).Value & _
             "<br><br>" & "The device above is assigned to you and has been identified as having out of date security patches.  Your device needs to be updated immediately. If you believe this to be incorrect, please contact " & _
             "<br><b>" & "Please reply to this email to confirm: " & _
             "<br><br>" & "Your device name. Please follow the instructions in the document above"


                .Attachments.Add "C:\Users\logit\OneDrive\Desktop\WinZip Registration.txt"
                .Attachments.Add "C:\Users\logit\OneDrive\Desktop\AUTO PAYMENT  MANUAL  PAYMENT.docx"

                .Display
            End With
        Next i
    End With
End Sub

 

[Piip]

& ws.Cells(1, 2).Value & _

Is this bit it specifically doesn't like. It wants .Value to be the end of the string and adding & _ at the end is still messing it up

 

[Micron]

If you're going to use line continuation characters I'd recommend this syntax. It's less confusing IMO and chances are that you're never left with a trailing underscore or a missing ampersand. Note that I pasted the solution offered into a module and there is no issue with the syntax, so not sure what you mean.

            &  "Name Assigned to Device: " & ws.Cells(1, 2).Value  _
            &  "<br><br>" & "Device Name: " & ws.Cells(1, 4).Value

FWIW I rarely use this method. I prefer to assign the string to a variable. I think it's easier and less prone to this sort of problem.

 

[Logit]

Micron :

Using this syntax creates an error for me on my machine ...

&  "Name Assigned to Device: " & ws.Cells(1, 2).Value  _
            &  "<br><br>" & "Device Name: " & ws.Cells(1, 4).Value

 

[Micron]

This works for me (I had to disable the attachment part). However, I was playing around with using either 'morning' or 'afternoon' in the salutation so it's not quite the same. That part also assumes it won't be running in the evening. The resulting email body looks strange, but perhaps that's because my sheet doesn't contain the required info in the right places.

Sub SendEmailFromExcelWithBody()
    Dim OutApp As Object, OutMail As Object
    Dim ws As Worksheet
    Dim i As Long, lRow As Long
    Dim strSalutation As String

    Set OutApp = CreateObject("Outlook.Application")
    Set ws = ThisWorkbook.Sheets("Sheet1")
    If Time() >= #12:00:00 PM# And Time() <= #4:00:00 PM# Then
        strSalutation = "Good afternoon,"
    Else
        strSalutation = "Good morning,"
    End If
   
    With ws
        lRow = .Range("A" & .rows.Count).End(xlUp).row
        For i = 1 To lRow
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .Importance = 2
                .ReadReceiptRequested = True
                .To = ws.Range("A" & i).Value
                .CC = ws.Range("B" & i).Value
                .Subject = "SSCL Devices with Out-dated Security Patches - " & Cells(i, "E")
                .HTMLBody = strSalutation & _
                "<br><br>" & "Name Assigned to Device: " & ws.Cells(1, 2).Value & _
                "<br><br>" & "Device Name: " & ws.Cells(1, 4).Value & _
                "<br><br>" & "The device above is assigned to you and has been identified as having out of date security patches.  Your device needs to be updated immediately. If you believe this to be incorrect, please contact " & _
                "<br><b>" & "Please reply to this email to confirm: " & _
                "<br><br>" & "Your device name. Please follow the instructions in the document above"
                '.Attachments.Add "C:\Users\logit\OneDrive\Desktop\WinZip Registration.txt"
                '.Attachments.Add "C:\Users\logit\OneDrive\Desktop\AUTO PAYMENT  MANUAL  PAYMENT.docx"
                .Display
            End With
        Next i
    End With
End Sub

EDIT - I always believe in cleaning up when objects are created and consider that missing from the code.

 

[Logit]

Micron:

Your macro works fine here with or without the attachments.

 

[Micron]

FWIW, I find this method to be far more reliable and easy to troubleshoot. I know not everyone would be OK with the variable repetition though. I added an error handler to ensure clean up is performed.

Sub SendEmailFromExcelWithBody()
Dim OutApp As Object, OutMail As Object
Dim ws As Worksheet
Dim i As Long, lRow As Long
Dim strSalutation As String, strBody As String

On Error GoTo errHandler
Set OutApp = CreateObject("Outlook.Application")
Set ws = ThisWorkbook.Sheets("Sheet1")
If Time() >= #12:00:00 PM# And Time() <= #4:00:00 PM# Then
    strSalutation = "Good afternoon,"
Else
    strSalutation = "Good morning,"
End If
strBody = "<br><br>" & "Name Assigned to Device: " & ws.Cells(1, 2) & "<br><br>" & "Device Name: "
strBody = strBody & ws.Cells(1, 4) & "<br><br>" & "The device above is assigned to you and has been "
strBody = strBody & "identified as having out of date security patches.  Your device needs to be updated immediately. "
strBody = strBody & "If you believe this to be incorrect, please contact <br><br>"
strBody = strBody & "Please reply to this email to confirm: <br><br> Your device name. "
strBody = strBody & "Please follow the instructions in the document above."

With ws
    lRow = .Range("A" & .rows.Count).End(xlUp).row
    For i = 1 To lRow
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .Importance = 2
            .ReadReceiptRequested = True
            .To = ws.Range("A" & i).Value
            .CC = ws.Range("B" & i).Value
            .Subject = "SSCL Devices with Out-dated Security Patches - " & Cells(i, "E")
            .HTMLBody = strSalutation & strBody
            '.Attachments.Add "C:\Users\logit\OneDrive\Desktop\WinZip Registration.txt"
            '.Attachments.Add "C:\Users\logit\OneDrive\Desktop\AUTO PAYMENT  MANUAL  PAYMENT.docx"
            .Display
        End With
    Next i
End With

exitHere:
Set ws = Nothing
Set OutApp = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

EDIT - there is an error there that I might not get corrected before the time to edit is passed.
EDIT 2 - fixed