使用ActiveWorkbook.Path在电子邮件中插入上的图像

Question

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

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

C:\Users\User\Desktop\Folder\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

Answer 1

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

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

既然你知道它是正确的，就这样使用它：

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