vba 如何将excel范围作为图片添加到Outlook邮件正文
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13911708/
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 add excel range as a picture to outlook message body
提问by Asaf Gilad
I'd like to build\edit the mail signiture in Excel:
我想在 Excel 中构建\编辑邮件签名：
1st cell : |Regards, | 2nd cell (Name) : |Asaf Gilad | 3rd Cell (Title): |PMO | 4th cell (Mail) : |[email protected] |
So that when I click send, the body of the message will look like:
因此，当我单击发送时，消息正文将如下所示：
Dear sir ................................ ....... Message Content ........ ................................ ................................ Regards, Asaf Gilad PMO [email protected]
The signiture contains pictures as well.
签名也包含图片。
I managed to save the range as picture and send that picture as attachment, but the picture turned out to be empty in the body, dispite the fact that it was sent correctly as attachment.
我设法将范围保存为图片并将该图片作为附件发送，但该图片在正文中是空的，尽管它作为附件正确发送。
Here is the code I use:
这是我使用的代码：
Public Sub ExportEmail(recipentName As String)
On Error GoTo err:
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olMail As Outlook.MailItem
Dim strEmailTo As String, strEmailCC As String, strEmailBCC As String
Dim FNAME As String
Dim oRange As Range
Dim oChart As Chart
Dim oImg As Picture
strEmailTo = ""
strEmailCC = ""
strEmailBCC = ""
strEmailTo = "[email protected]"
strEmailCC = "[email protected]
If strEmailTo "" Then
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = strEmailTo
olMail.CC = strEmailCC
olMail.BCC = strEmailBCC
olMail.Subject = " My Subject"
Set oRange = Sheets(1).Range("A1:Z100")
Set oChart = Charts.Add
oRange.CopyPicture xlScreen, xlPicture
oChart.Paste
FNAME = Environ$("temp") & "\testPic.gif"
oChart.Export Filename:=FNAME, FilterName:="GIF"
olMail.Attachments.Add FNAME
olMail.HTMLBody = "" & _
""
olMail.Attachments.Add FNAME
olMail.Send
End If
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Kill FNAME
Set olApp = Nothing
Set olNs = Nothing
Set oRange = Nothing
Set oChart = Nothing
Set oImg = Nothing
Exit Sub
err:
MsgBox err.Description
End Sub
采纳答案by Scott Holtzman
This is a good question, Asaf. When I have built automated e-mail solutions, I've found it difficult to get the signature line in. It's possible, but not easy. Maybe it's updated in 2010, but I haven't checked yet.
这是个好问题，阿萨夫。当我构建自动化电子邮件解决方案时，我发现很难输入签名行。这是可能的，但并不容易。也许它是在 2010 年更新的，但我还没有检查过。
What I do is place the entire body into a text file on a drive, complete with any html tags that I want for formatting. This gives me great flexibility in both making nicely formatted e-mails where I can assign variables as well.
我所做的是将整个正文放入驱动器上的文本文件中，并附上我想要格式化的任何 html 标签。这使我在制作格式良好的电子邮件方面具有很大的灵活性，我也可以在其中分配变量。
I then access those files through the Microsoft Scripting Runtimelibrary.
然后我通过Microsoft Scripting Runtime库访问这些文件。
See below code snippets:
请参阅以下代码片段：
Option Explicit
Const strEmailBoiler As String = "\server\path\folder\subfolder\email_text\"
Sub PrepMessage()
Dim strBody As String, strMon As String
strMon = range("Mon").Value
strFY = range("FY").Value
strBody = FileToString(strEmailBoiler, "reports_email_body.txt")
strBody = Replace(strBody, "[MONTH]", strMon)
strBody = Replace(strBody, "[YEAR]", Right(strFY, 2))
strBody = Replace(strBody, "[FILE PATH]", strFileName)
SendMail "[email protected]", "Subject Goes Here " & strMon & " YTD", strBody
End Sub
Function FileToString(ByVal strPath As String, ByVal strFile As String) As String
'requires reference to Microsoft Scripting Runtime Object Library (or late binding)
Dim ts As TextStream
Set fso = New FileSystemObject
Set ts = fso.OpenTextFile(strPath & strFile, ForReading, False, TristateUseDefault)
FileToString = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
End Function
Sub SendMail(strTo As String, strSubject As String, strHTMLBody As String, Optional strAttach As String, Optional strCC As String)
'requires reference to Microsoft Outlook X.X Object Library (or late binding)
Dim olApp As Outlook.Application
Dim olMI As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMI = olApp.CreateItem(olMailItem)
With olMI
.To = strTo
.Subject = strSubject
.HTMLBody = strHTMLBody
If strAttach <> vbNullString Then .Attachments.Add strAttach
.Display 'using this because of security access to Outlook
'.Send
End With
End Sub
Then my reports_email_body.txtfile will look like this:
然后我的reports_email_body.txt文件将如下所示：
<p>Hello Person,</p>
<p>The Reports file for [MONTH] FY[YEAR] has been saved in the following location:</p>
<p><a href="[FILE PATH]">[FILE PATH]</a></p>
<p>Best,</p>
<br>
Scott Holtzman
<br>My Address
<br>my title
<br>whatever else...
回答by Mike Marshall
In Excel 2010 (and possibly 2007) you can add .HTMLBody to the end of your body string. For instance, use something like this:
在 Excel 2010（也可能是 2007）中，您可以将 .HTMLBody 添加到正文字符串的末尾。例如，使用这样的东西：
.HTMLBody = "<br>" & strbody & .HTMLBody
' <br> is an HTML tag to turn the text into HTML
' strbody is your text from cell C9 on the mail tab
' .HTMLBody inserts your email signature
This will at least solve your signature line problem.
这至少可以解决您的签名行问题。
I am looking for a solution for the same problem: Inserting a range as a picture.
我正在寻找相同问题的解决方案：将范围作为图片插入。
