excel - 如何将excel范围作为图片添加到outlook邮件正文

Question

我想在 Excel 中构建\编辑邮件签名:

1st cell        : |Regards,      |2nd cell (Name) : |Asaf Gilad    |3rd Cell (Title): |PMO           |4th cell (Mail) : |Asaf@mail.com |

这样当我点击发送时，邮件正文将如下所示:

Dear sir....................................... Message Content ........................................................................Regards,Asaf Gilad    PMO           Asaf@mail.com 

签名也包含图片。

我设法将范围保存为图片并将该图片作为附件发送，但图片在正文中是空的，尽管它已作为附件正确发送。

这是我使用的代码:

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 = "a@a.com"    strEmailCC = "b@b.com    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 Suberr:    MsgBox err.DescriptionEnd Sub

Answer 1

这是个好问题，Asaf。当我构建了自动电子邮件解决方案时，我发现很难输入签名行。这是可能的，但并不容易。可能是 2010 年更新的，但我还没有检查。

我所做的是将整个正文放入驱动器上的文本文件中，并附上我想要格式化的任何 html 标签。这给了我很大的灵 active ，既可以制作格式精美的电子邮件，也可以在其中分配变量。

然后我通过 Microsoft Scripting Runtime 库访问这些文件。

请看下面的代码片段:

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 "firstname.lastname@xyz.com", "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

然后我的 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...