时间戳

首页 > 解决方案 > 使用 ActiveWorkbook.Path 在电子邮件中插入图像

excel - 使用 ActiveWorkbook.Path 在电子邮件中插入图像

问题描述

我的电子表格中有 Excel VBA 代码，其中包含姓名和电子邮件地址列表、创建 PowerPoint 证书并将每个人的证书通过电子邮件发送。

如果我给它一个特定的路径，我可以在电子邮件的末尾添加一个徽标，例如

C:\用户\用户\桌面\文件夹\img.png

但如果我说

ActiveWorkbook.Path & '\img.png'

它插入一个空框。

Public Function generateCerts()
    Dim CurrentFolder As String
    Dim fileName As String
    Dim myPath As String
    Dim UniqueName As Boolean
    Dim sh As Worksheet

    Dim myPresentation As PowerPoint.Presentation
    Dim PowerPointApp As PowerPoint.Application
    Dim shp As PowerPoint.Shape

    Dim outlookApp As Outlook.Application
    Dim myMail As Outlook.MailItem

    Set outlookApp = New Outlook.Application

    Set PowerPointApp = CreateObject("PowerPoint.Application")
    Set myPresentation = PowerPointApp.Presentations.Open(ActiveWorkbook.Path & "\Certificate3.pptx")
    
    Set shp = myPresentation.Slides(1).Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, Left:=0, Top:=250, Width:=825, Height:=68)
    shp.TextFrame.TextRange.Font.Size = 36
    shp.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
    
    Set sh = Sheets("CertNames")
    For Each rw In sh.Rows
        If rw.Row > 1 Then
            If sh.Cells(rw.Row, 1).Value = "" Then
                Exit For
            End If
        
            shp.TextFrame.TextRange.Text = sh.Cells(rw.Row, 1) & " " & sh.Cells(rw.Row, 2).Value & " " & sh.Cells(rw.Row, 3).Value
    
            fileName = ActiveWorkbook.Path & "\" & sh.Cells(rw.Row, 2).Value & " " & sh.Cells(rw.Row, 3).Value & " " & rw.Row & ".pdf"
            myPresentation.ExportAsFixedFormat fileName, _
                ppFixedFormatTypePDF, ppFixedFormatIntentPrint, msoCTrue, ppPrintHandoutHorizontalFirst, _
                ppPrintOutputSlides, msoFalse, , ppPrintAll, , False, False, False, False, False
                
            Set myMail = outlookApp.CreateItem(olMailItem)
            myMail.Attachments.Add fileName
            
            myMail.SentOnBehalfOfName = ""
'            myMail.BCC = ""
            myMail.To = sh.Cells(rw.Row, 4).Value
            myMail.Subject = "Thank you for attending"
            myMail.HTMLBody = "Hello" & " " & sh.Cells(rw.Row, 1).Value & ","
            myMail.HTMLBody = myMail.HTMLBody & "<p>Thank you for participating in <b><i>Session 7
            myMail.HTMLBody = myMail.HTMLBody & "<p>Support</p>"
            myMail.HTMLBody = myMail.HTMLBody & "<img src='ActiveWorkbook.Path & '\img.png''"
            myMail.Display
'            myMail.Send
            
        End If
    Next rw

    myPresentation.Saved = True
    PowerPointApp.Quit
    
End Function

标签： excelvba

解决方案

将路径声明为字符串，以便您可以检查它：

Dim imagePath As String
imagePath = ActiveWorkbook.Path & "\img.png"

既然您知道它是正确的，请像这样使用它：

myMail.HTMLBody = myMail.HTMLBody & "<img src='" & imagePath & "'>"

推荐阅读