VBA .body text is too long

I am trying to design a spreadsheet for our department that will take information from the filled in cells and draft an email to send to the customer and construction contact. The problem I'm running into is that the body of my email in VBA is too long. It has to be formatted a certain way for the email. Please help.

My current code for the button is simply:

Private Sub CommandButton1_Click()

Dim OutObj As Object, OutMail As Object
Set OutObj = CreateObject("Outlook.Application")
Set OutMail = OutObj.createitem(0)

With OutMail
    .to = Range("S29").Value
    .CC = Range("S34").Value
    .BCC = Range("S36").Value
    .Subject = "Company - Construction phase Contact Information - " & Range("E4")
    .HTMLbody = ""
    .display
End With

End Sub

This is what I'm trying to put in the .HTMLbody = portion of the code.

"Dear " & Range("S28") & ",<br/><br/>Your " & Range("S30") & "work (Work Order #:" & Range("S31") & ") at " & Range("S32") & " will be moving to the construction phase of the Construction Process. The job is currently with the scheduling department to be assigned to a specific construction group and crew. </br>" & Range("S33") & " is the Construction Project Manager assigned to this job. They will be overseeing the construction process and should be contacted directly if you have questions or concerns with your project. They have been CC'd to this email for your convenience.<br/>Once construction is complete, if additional support is needed for your final meter set(s), please contact me and I will be happy to assist you. Keep in mind that it typically takes a few days for those to get scheduled after we receive your inspection.<br/>The following site readiness conditions are required for us to begin with construction:<br/>1- " & Range("A1") & "<br/>2- " & Range("A2") & "<br/>3- "& Range("A3") & "<br/>4- "&Range("A4") & "<br/>Lastly, to ensure we're including the appropriate representatives for this phase of the project, please notify us if there are different or additional people we should include for construction coordination. Please either add those individuals to this email thread or send us the best name and contact information for us to reach out to them directly.<br/>Thank you so much and please don't hesitate to reach out to us with any questions or concerns!<br/>Sincerely,<br/>" & Range("S35")

This returns a Run-Time Error "1004"

I've tried using"

msg1 = "text long text long etc..."
msg2 = "text long text long etc..."
msg3 = "text long text long etc..."
msg4 = "text long text long etc..."

But either I'm doing this wrong or it doesn't work for me.

Concatenating long lines can be a pain in the ass. My advice is a) to break the long text into smaller pieces and b) use intermediate variables to store the values you want to put into the text.

As described here, you have basically 2 ways to create long text lines. The first is to use the line continue syntax (put _ at the end of a line) to signal the VBA compiler that the next line is part of the statement:

Dim body As String
body = "Dear " & Range("S28") & ",<br/><br/>Your " & Range("S30") _
     & "Work (Work Order #:" & Range("S31") & ") at <b>" & Range("<b>S32<b/>") & "<b/>" _
     & "will be moving to the construction phase of the Construction Process. " 
(...)

The second is to concatenate the string step by step:

body = "Dear " & Range("S28") & ",<br/><br/>Your " & Range("S30")
body = body & "Work (Work Order #:" & Range("S31") & ") at <b>" & Range("<b>S32<b/>") & "<b/>" 
body = body & "will be moving to the construction phase of the Construction Process. " 
(...)

Disadvantage of the second method is that it is slightly slower - but nothing you should be worried about. Advantage is that you can check row by row what was written into the variable using the debugger. Also, if you have a syntax error when creating the statement (eg missing quotes), it is easy to locate.

As CDP1802 pointed in the comments, your error is Range("<b>S32<b/>") - what you meant is "<b>" & Range(S32) & "<b/>". The compiler cannot catch this problem, so you would still get a runtime error, but at least you get an idea where the problem is.

These kind of mistakes are hard to avoid, however, it can help to use variables:

Dim body As String
Dim customer As String, work As String, workOrder As String, workOrderAt As String
With ActiveSheet
    customer = .Range("S28")
    work = .Range("S30")
    workOrder = .Range("S31")
    workOrderAt = .Range("S32")
    (...)
End With

body = "Dear " & customer & ",<br/><br/>Your " & work
body = body & "Work (Work Order #:" & workOrder & ") at <b>" & workOrderAt & "<b/>" 
body = body & "will be moving to the construction phase of the Construction Process. " 
(...)

This has some advantages:

• You can check with the debugger the content of the variables.
• When something changes in your sheet, you just have to change how the variables are filled, but the creating of the body-content is not changed.
• The statement is easier to read.
• The mistake you made cannot happen any more (because the Html-format is set independent of filling the content of the sheet).

And last but not least, as BigBen pointed out, you need to assign the text to HTMLBody. Using Body will not format your text (instead you will have those tags within your text).

Range("") will give you a Runtime Error 1004, use any blank cell like Range("ZZ1"). Also break the lines with an underscore character –
CDP1802

Thanks!

The answer for this was:

Private Sub CommandButton1_Click()

Dim OutObj As Object, OutMail As Object Set OutObj = 
CreateObject("Outlook.Application") Set OutMail = OutObj.createitem(0)

With OutMail
    .to = Range("S29").Value
    .CC = Range("S34").Value
    .BCC = Range("S36").Value
    .Subject = "Company - Construction phase Contact Information - " & Range("E4")
    .HTMLbody = _
        "Dear " & Range("S28") & ",<br/><br/>Your " & Range("S30") & "work (Work Order #:" & Range("S31") & ") at " & Range("S32") & _
        " will be moving to the construction phase of the Construction Process.  The job is currently with the scheduling department to be assigned to a specific" & _
        " construction group and crew. </br>" & Range("S33") & " is the Construction Project Manager assigned to this job.  They will be overseeing the construction process and" & _
        " should be contacted directly if you have questions or concern with your project.  They have been CC'd to this email for your convenience.<br/>Once construction is complete," & _
        " if additional support is needed for your final meter set(s), please contact me and I will be happy to assist you.  Keep in mind that it typically takes a few days for" & _
        " those to get scheduled after we receive your inspection.<br/>The following site readiness conditions are required for us to begin with construction:<br/>1- " & _
        Range("A1") & "<br/>2- " & Range("A2") & "<br/>3- " & Range("A3") & "<br/>4- " & Range("A4") & "<br/>Lastly, to ensure we're including the appropriate representatives for this phase of" & _
        " the project, please notify us if there are different or additional people we should include for construction coordination.  Please either add those individuals to" & _
        " this email thread or send us the best name and contact information for us to reach out to them directly.<br/>Thank you so much and please don't hesitate to" & _
        " reach out to us with any questions or concerns!<br/>Sincerely,<br/>" & Range("S35")
    .display 
End With

End Sub