VBA - Email Body not sending

[kirstieruchat]

Hi,

I am using the below VBA to try and send an email. The email is sending but there is no body in the email. It is just and email with a subject line.

Any help please?

Thanks
Kirstie

Sub TESTEmailSender()
    Dim OutApp As Object
    Dim OutMail As Object
    
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")


            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = "email@email.com"
                .Subject = Sheet6.Range("B6").Value & " - " & "SDH Price Increase"
                .Body = "Entity: " & Sheet6.Range("I3").Value & _
                        vbNewLine & vbNewLine & _
                        "Customer Email: " & Sheet6.Range("I7").Value & _
                        vbNewLine & vbNewLine & _
                        "Sales Manaager Email: " & Sheet6.Range("I10").Value & _
                        vbNewLine & vbNewLine & _
                        vbNewLine & vbNewLine & _
                        vbNewLine & vbNewLine & _
                        vbNewLine & vbNewLine & _
                        vbNewLine & vbNewLine & _
                "Dear " & _
                       vbNewLine & vbNewLine & _
"You last updated the NBRT on " & Cells(cell.Row, "F").Value & "." & vbLf & Cells(cell.Row, "G").Value & _
                        vbNewLine & vbNewLine & _
                        Cells(cell.Row, "E").Value & _
                        vbNewLine & vbNewLine & _
                        Sheet10.Range("G30").Value & _
                        vbNewLine & vbNewLine & _
                        "Many Thanks," & _
                        vbNewLine & vbNewLine & _
                        "K"
         
                   
    .Send
   
End With
            On Error GoTo 0
            Set OutMail = Nothing
      
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

Remove the On Error Resume Next line and see what is happening. Perhaps you have errors in some of the cells.

 

[kirstieruchat]

Remove the On Error Resume Next line and see what is happening. Perhaps you have errors in some of the cells.

This has helped me to find there is errors. I have stripped it all back.

when i do this: .Body = "Entity: " & Sheet6.Range("I4").Value & vbNewLine & "Customer Email: " it worked perfectly but when I add the next piece of code on: .Body = "Entity: " & Sheet6.Range("I4").Value & vbNewLine & "Customer Email: " & Sheet6.Range("I7").Value it stops working - any idea what I am doing wrong?

 

[RoryA]

What is in I7 on that sheet when the code is running?

 

[kirstieruchat]

What is in I7 on that sheet when the code is running?

Just an email address

 

[RoryA]

That shouldn't be a problem. What exactly does "stops working" mean?

 

[ebea]

Your Code, shortened down a little bit, as it's only tested on ActiveSheet.
And I have used HTML, to show the example, which gives you a bit more opportunities, to refine the Text.
It should work by you.

Sub TESTEmailSender()
    Dim OutApp As Object
    Dim OutMail As Object
    
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")


            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = "email@email.com"
                .Subject = ActiveSheet.Range("B6").Value & " - " & "SDH Price Increase"
                                
                .HTMLBody = "<font face=""calibri"" style=""font-size:12pt;""Entity: " & ActiveSheet.Range("I3").Value & _
                        "<br><br>" & _
                        "Customer Email: " & ActiveSheet.Range("I7").Value & _
                        "<br><br>" & _
                        "Sales Manager Email: " & ActiveSheet.Range("I10").Value & _
                        "<br><br>" & _
                        "<br><br>" & _
                        "Many Thanks" & _
                        "<br>" & _
                        "Kirstie" & _
                        .HTMLBody & "</font>"

                   
    .Send
   
End With
            On Error GoTo 0
            Set OutMail = Nothing
      
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub