VBA .body 文本太长

Range（“”） 将给出一个运行时错误 1004，使用任何空白单元格，如 Range（“ZZ1”）。还要用下划线字符打破界限—— CDP1802

谢谢！

答案是：

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

串联长线可能会很痛苦。我的建议是 a） 将长文本分成更小的部分，b） 使用中间变量来存储您要放入文本中的值。

如此处所述，您基本上有 2 种方法可以创建长文本行。第一种是使用行继续语法（放在行尾）向 VBA 编译器发出信号，表明下一行是语句的一部分： _

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. " 
(...)

第二种是逐步连接字符串：

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. " 
(...)

第二种方法的缺点是它稍微慢一些 - 但你不必担心。优点是可以使用调试器逐行检查写入变量的内容。此外，如果您在创建语句时遇到语法错误（例如缺少引号），则很容易找到。

正如CDP1802评论中指出的那样，您的错误是 - 您的意思是.编译器无法捕获此问题，因此您仍然会遇到运行时错误，但至少您可以了解问题所在。Range("<b>S32<b/>")"<b>" & Range(S32) & "<b/>"

这些错误很难避免，但是，使用变量会有所帮助：

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. " 
(...)

这有一些优点：

• 您可以使用调试器检查变量的内容。
• 当工作表中的某些内容发生变化时，您只需更改变量的填充方式，但正文内容的创建不会更改。
• 该声明更易于阅读。
• 您犯的错误不会再发生（因为 HTML 格式的设置与填充工作表的内容无关）。

最后但并非最不重要的一点是，正如 BigBen 所指出的，您需要将文本分配给 .使用 不会设置文本的格式（相反，您将在文本中使用这些标签）。HTMLBodyBody