使用Excel VBA在Outlook中添加包含图像的默认签名 [英] Adding default signature, that consists of images, in Outlook using Excel VBA
问题描述
我想在图像上添加签名.此处的图片指的是公司徽标和社交网络图标.
I want to add signatures with images. Images here refer to company logo and social networking icons.
此代码是用Excel VBA编写的,目标是将范围复制粘贴为Outlook电子邮件中的图片.
This code is written in Excel VBA and the goal is to copy paste the range as a picture in Outlook email.
Dim Rng As Range
Dim outlookApp As Object
Dim outMail As Object
Dim wordDoc As Word.Document
Dim LastRow As Long
Dim CcAddress As String
Dim ToAddress As String
Dim i As Long
Dim EndRow As String
Dim Signature As String
'// Added Microsoft word reference
Sub Excel_Image_Paste_Testing()
On Error GoTo Err_Desc
'\\ Define Endrow
EndRow = Range("A65000").End(xlUp).Row
'\\ Range for copy paste as image
Set Rng = Range("A22:G" & EndRow)
Rng.Copy
'\\ Open a new mail item
Set outlookApp = CreateObject("Outlook.Application")
Set outMail = outlookApp.CreateItem(0)
'\\ Display message to capture signature
outMail.Display
'\\ This doesnt store images because its defined as string
'Problem lies here
Signature = outMail.htmlBody
'\\ Get its Word editor
Set wordDoc = outMail.GetInspector.WordEditor
outMail.Display
'\\ To paste as picture
wordDoc.Range.PasteAndFormat wdChartPicture
'\\ TO and CC Address
CcAddress = "xyz@gmail.com"
ToAddress = "abc@gmail.com"
'\\ Format email
With outMail
.htmlBody = .htmlBody & Signature
.Display
.To = ToAddress
.CC = CcAddress
.BCC = ""
.Subject = "Email Subject here"
.readreceiptrequested = True
End With
'\\ Reset selections
Application.CutCopyMode = False
Range("B1").Select
Exit Sub
Err_Desc:
MsgBox Err.Description
End Sub
此文件将分发给许多人.我不知道默认的.htm签名名称.
This file is to be distributed to many people. I wouldn’t know the default .htm signature name.
("AppData \ Roaming \ Microsoft \ Signatures")
("AppData\Roaming\Microsoft\Signatures")
人们可能也有很多签名,但是我的目标是捕获他们的默认签名.
People may also have many signatures but my goal is to capture their default signature.
运行代码后出现错误签名图片
Error signature picture after running the code
我的签名应如下所示.
My signature should be as shown below.
推荐答案
在此代码中,我们将让用户从AppData\Roaming\Microsoft\Signatures
In this code we will let the user select the .Htm
file from AppData\Roaming\Microsoft\Signatures
问题在于我们无法直接使用此文件的html主体,因为图像存储在名为filename_files
的其他文件夹中,如下所示.
The problem is that we cannot directly use the html body of this file because the images are stored in a different folder named as filename_files
as shown below.
htmlbody中提到的路径也不完整.看下面的图片
Also the paths mentioned in the htmlbody are incomplete. See the below images
这是我编写的一个快速函数,它将修复html正文中的路径
Here is a quick function that I wrote which will fix the paths in the html body
'~~> Function to fix image paths in Signature .htm Files
Function FixHtmlBody(r As Variant) As String
Dim FullPath As String, filename As String
Dim FilenameWithoutExtn As String
Dim foldername As String
Dim MyData As String
'~~> Read the html file as text file in a string variable
Open r For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
'~~> Get File Name from path
filename = GetFilenameFromPath(r)
'~~> Get File Name without extension
FilenameWithoutExtn = Left(filename, (InStrRev(filename, ".", -1, vbTextCompare) - 1))
'~~> Get the foldername where the images are stored
foldername = FilenameWithoutExtn & "_files"
'~~> Full Path of Folder
FullPath = Left(r, InStrRev(r, "\")) & foldername
'~~> Replace incomplete path with full Path
FixHtmlBody = Replace(MyData, foldername, FullPath)
End Function
这是完整的过程.我已经注释了代码.让我知道您是否还有任何问题.
Here is the complete procedure. I have commented the code. Let me know if you still have any issues.
Sub Sample()
Dim oOutApp As Object, oOutMail As Object
Dim strbody As String, FixedHtmlBody As String
Dim Ret
'~~> Ask user to select the htm file
Ret = Application.GetOpenFilename("Html Files (*.htm), *.htm")
If Ret = False Then Exit Sub
'~~> Use the function to fix image paths in the htm file
FixedHtmlBody = FixHtmlBody(Ret)
Set oOutApp = CreateObject("Outlook.Application")
Set oOutMail = oOutApp.CreateItem(0)
strbody = "<H3><B>Dear Blah Blah</B></H3>" & _
"More Blah Blah<br>" & _
"<br><br><B>Thank you</B>" & FixedHtmlBody
On Error Resume Next
With oOutMail
.To = "Email@email.com" '<~~ Change as applicable
.CC = ""
.BCC = ""
.Subject = "Example on how to insert image in signature"
.HTMLBody = .HTMLBody & "<br>" & strbody
.Display
End With
On Error GoTo 0
Set oOutMail = Nothing
Set oOutApp = Nothing
End Sub
'~~> Function to fix image paths in Signature .htm Files
Function FixHtmlBody(r As Variant) As String
Dim FullPath As String, filename As String
Dim FilenameWithoutExtn As String
Dim foldername As String
Dim MyData As String
'~~> Read the html file as text file in a string variable
Open r For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
'~~> Get File Name from path
filename = GetFilenameFromPath(r)
'~~> Get File Name without extension
FilenameWithoutExtn = Left(filename, (InStrRev(filename, ".", -1, vbTextCompare) - 1))
'~~> Get the foldername where the images are stored
foldername = FilenameWithoutExtn & "_files"
'~~> Full Path of Folder
FullPath = Left(r, InStrRev(r, "\")) & foldername
'~~> To cater for spaces in signature file name
FullPath = Replace(FullPath, " ", "%20")
'~~> Replace incomplete path with full Path
FixHtmlBody = Replace(MyData, foldername, FullPath)
End Function
'~~> Gets File Name from path
Public Function GetFilenameFromPath(ByVal strPath As String) As String
If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then _
GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End Function
实际操作
这篇关于使用Excel VBA在Outlook中添加包含图像的默认签名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!