首页 文章

如何将excel范围作为图片添加到Outlook消息体中

提问于
浏览 次
2

我想在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 Sub
err:
    MsgBox err.Description
End Sub
excel vba image outlook sendmail

2 回答

  • 1

    这是一个很好的问题，阿萨夫 . 当我构建自动电子邮件解决方案时，我发现很难获得签名行 . 这是可能的，但并不容易 . 也许它在2010年更新了，但我还没有检查过 .

    我所做的是将整个主体放入驱动器上的文本文件中，并附带我想要格式化的任何html标签 . 这使我在制作格式良好的电子邮件方面具有很大的灵活性，我也可以在其中分配变量 .

    然后我通过 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...
    回复于
  • 1

    在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

    这至少可以解决您的签名行问题 .

    我正在寻找同样问题的解决方案：插入一个范围作为图片 .

    回复于

相关问题