如何更改通过 Excel VBA 代码通过 Outlook 发送的电子邮件的字体格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21893172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change the font formatting on an email sent through Outlook via Excel VBA code?
提问by Chonchos
I have VBA code that uses Excel to send a number of emails through Outlook.
我有使用 Excel 通过 Outlook 发送大量电子邮件的 VBA 代码。
It inputs the name of each individual contact into the email body (Dear So-and-so), the company name/invoice number into the subject line (Company ABC Invoice 123), attaches a different file for each contact (Invoice 123.pdf), marks the message as high importance, requests a read receipt, sends on behalf of a secondary email account I use for invoices rather than my personal email account, BCCs to the secondary email, and adds an email signature at the bottom with a picture.
它将每个联系人的姓名输入电子邮件正文（Dear So-so），将公司名称/发票编号输入主题行（Company ABC Invoice 123），为每个联系人附加不同的文件（Invoice 123.pdf )，将消息标记为高度重要，请求阅读回执，代表我用于发票的辅助电子邮件帐户而不是我的个人电子邮件帐户发送，密件抄送到辅助电子邮件，并在底部添加带有图片的电子邮件签名.
Most of the code (if not all of it, actually) is from this wonderful Ron de Bruin site.
大多数代码（如果不是全部的话，实际上）来自这个美妙的 Ron de Bruin 站点。
I pasted together a lot of code to get where I am and don't know how I managed to get it to work at all.
我粘贴了很多代码来获得我的位置，但根本不知道我是如何设法让它工作的。
I tried numerous different "tags" in an attempt to change the font formatting.
我尝试了许多不同的“标签”以尝试更改字体格式。
I tried changing Outlook's template settings and taking the code out in hopes that it would input with the "Outlook default" font.
我尝试更改 Outlook 的模板设置并取出代码，希望它能使用“Outlook 默认”字体输入。
There are style tags in my code but they do not have much of an effect. It is set to Times New Roman Size 18.
我的代码中有样式标签，但它们没有太大影响。它设置为 Times New Roman 尺寸 18。
The "Dear So-and-so" inputs as Times New Roman Size 12. The email body inputs as Calibri 13.5. My signature inputs as whatever I set it to in the signature setup.
“亲爱的某某”输入为 Times New Roman Size 12。电子邮件正文输入为 Calibri 13.5。我的签名输入为我在签名设置中设置的任何内容。
I want it to all be the same font, size, color, etc.
我希望它都具有相同的字体、大小、颜色等。
Sub Mail_Outlook_With_Signature_Html_1()
' Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim strbody As String
Dim cell As Range
Dim FileCell As Range
Dim rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
strbody = "<P STYLE='font-family:Times New Roman;font-size:18'>This is the message text for each message. This is all the same for each contact!</P>"
On Error Resume Next
With OutMail
.Display
.To = cell.Value
.CC = ""
.BCC = "[email protected]"
.Subject = Cells(cell.Row, "D").Value
.HTMLBody = "Dear " & Cells(cell.Row, "A").Value & "," & "<br>" & "<br>" & strbody & .HTMLBody
.Importance = 2
.ReadReceiptRequested = True
.SentOnBehalfOfName = """My Company Email Name"" <[email protected]>"
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Edit:Roland Shaw noticed that the font size did not have any clarifications as to what 18 was. 18pts? 18px? 18inches? So I changed the code to the following.
编辑：Roland Shaw 注意到字体大小没有任何说明 18 是什么。18分？18像素？18寸？所以我将代码更改为以下内容。
strbody = "<P STYLE='font-family:Times New Roman;font-size:18pt'>
This made the body of the text change formatting but it did not change the "Dear so-and-so" part of the email. Still hoping for some changes there.
这使文本正文更改格式，但没有更改电子邮件的“亲爱的某某”部分。仍然希望那里有一些变化。
2nd Edit: h4xpace suggested taking a look at this articlefor some guidance. I already attempted to follow this guide before posting, but I could not get it to work. I went back and tried to just add what I THOUGHT was the relevant bits of code needed to change the body fonts. Additions and changes to code below:
第二次编辑：h4xpace 建议看一下这篇文章以获得一些指导。在发布之前，我已经尝试遵循本指南，但我无法让它工作。我回去尝试只添加我认为是更改正文字体所需的相关代码位。对以下代码的添加和更改：
With OutMail
.Display
.To = cell.Value
.CC = ""
.BCC = "[email protected]"
.Subject = Cells(cell.Row, "D").Value
**.BodyFormat = olFormatHTML**
.HTMLBody = **"<HTML><BODY><b>"**Dear " & Cells(cell.Row, "A").Value & "," & "<br>" & "<br>" & strbody & .HTMLBody **</b></BODY></HTML>"**
.Importance = 2
.ReadReceiptRequested = True
.SentOnBehalfOfName = """My Company Email Name"" <[email protected]>"
This did not work either.
这也不起作用。
I don't know enough about VBA to add the necessary pieces of code without altering what I already have.
我对 VBA 的了解不够，无法在不改变我已有的代码的情况下添加必要的代码段。
Final changes
最终更改
The code works as I initially intended it to now. The two main changes are to the following lines. On strbody I needed to add "pt" to the font-size. Also I needed to put the formatting information before any of the "message contents".
该代码按我最初的预期工作。两个主要更改是以下几行。在 strbody 上，我需要将“pt”添加到字体大小。我还需要将格式信息放在任何“消息内容”之前。
strbody = "<P STYLE='font-family:TimesNewRoman;font-size:12.5pt;color: rgb(31,73,125)'>This is the message text for each message. This is all the same for each contact!</P>"
.HTMLBody = "<P STYLE='font-family:TimesNewRoman;font-size:12.5pt;color: rgb(31,73,125)'>Dear " & Cells(cell.Row, "A").Value & "," & "<br>" & "<br>" & strbody & .HTMLBody
采纳答案by user1229617
You are almost there, You are creating your email body As
你快到了，你正在创建你的电子邮件正文作为
.HTMLbody = 'SomeText' + strbody(Formatting + MoreText)
Because the formatting section is part way through it only affects the text that follows it. Make .HTMLBodyStart with your formatting information and everything that follows will pick it up.
因为格式化部分是它的一部分，所以它只会影响它后面的文本。请.HTMLBody开始你的格式信息，并随后将它捡起来的一切。
回答by Alexandre C?té
I think that OP and I were trying to work around the same problem wich was to send a message with normal formatting and keep the signature via ron debruin's method.
我认为 OP 和我试图解决同样的问题，即发送具有正常格式的消息并通过ron debruin 的方法保留签名。
I solved my problem by adding:
我通过添加解决了我的问题：
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strbody & "</BODY></HTML>" & .HTMLBody
to the .OutMail object
到 .OutMail 对象
回答by Adam Pride
What works for me is:
对我有用的是：
.HTMLBody = strbody & TextLine1 & vbcrlf & strbody & TextLine2
i.e. put the formatting string before every line of text.
即把格式化字符串放在每一行文本之前。
回答by Serialize
You need to set the .BodyFormat property of the OutMail object to olFormatHTML.
您需要将 OutMail 对象的 .BodyFormat 属性设置为 olFormatHTML。
See the example provided here: http://msdn.microsoft.com/en-us/library/office/aa171418(v=office.11).aspx
请参阅此处提供的示例：http: //msdn.microsoft.com/en-us/library/office/aa171418(v=office.11).aspx
