excel - VBA 在 Excel 中创建带有表格和签名的电子邮件
问题描述
在 Excel 中单击按钮时,我正在尝试创建自定义电子邮件。所有用户都有 Outlook。在电子邮件的正文中,我想包含已经格式化的电子表格的一部分。
我可以在那里获得信息,但我无法在正文中获得正确的订单。那是文本,然后是格式化的表格 THEN 签名。
下面的示例将格式化表放在下面,但我希望签名是最后一件事。
任何帮助将不胜感激。
Sub SendUpdateEmail()
Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object
Dim EmailTo As String
Dim EmailCC As String
Dim UpdateDate As String
Dim Location As String
Dim strSig As String
' Set Outlook object
Set outlook = CreateObject("Outlook.Application")
' Set Email Mail Object
Set newEmail = outlook.CreateItem(0)
' Set Inspect Object
Set xInspect = newEmail.GetInspector
' Set Page Editor Object
Set pageEditor = xInspect.WordEditor
' Set Email To
EmailTo = Worksheets("Project Summary").Cells(15, "F").Value
' Set Email CC
EmailCC = Worksheets("Project Summary").Cells(16, "F").Value
' Set Update date
UpdateDate = Worksheets("OUTPUT - Daily Field Ticket").Cells(7, "B").Value
' Set Location
Location = Worksheets("OUTPUT - Daily Field Ticket").Cells(5, "B").Value
With newEmail
.To = EmailTo
.CC = EmailCC
.BCC = ""
.Subject = "UPDATE | " + Location + " | " + UpdateDate
'DO NOT REMOVE - THIS MUST BE VISIBLE FIRST TO GET THE DEFAULT SIGNATURE
.Display
'GET THE HTML CODE FROM THE SIGNATURE
strSig = .HTMLBody
.HTMLBody = "Hello," & "<br>" & "<br>" & "Please see attached the Daily Field Ticket for " + Location
+ " for " + UpdateDate + "." + strSig
Sheet1.Range("A28:F35").Copy
pageEditor.Application.Selection.Start = Len(.Body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
Set pageEditor = Nothing
Set xInspect = Nothing
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub
解决方案
当我必须在 Outlook 邮件中复制粘贴范围时,我通常使用 Ron De Bruin 著名的功能“范围到 HTML”。我已将其插入您的代码并进行了一些编辑。它应该给你预期的结果:
Sub SendUpdateEmail()
Dim outlook As Object
Dim newEmail As Object
Dim EmailTo As String
Dim EmailCC As String
Dim UpdateDate As String
Dim Location As String
Dim strSig As String
' Set Outlook object
Set outlook = CreateObject("Outlook.Application")
' Set Email Mail Object
Set newEmail = outlook.CreateItem(0)
' Set Email To
EmailTo = "test@gmail.com"
' Set Email CC
EmailCC = "test@gmail.com"
' Set Update date
UpdateDate = "18/03/2020"
' Set Location
Location = "Here"
With newEmail
.To = EmailTo
.CC = EmailCC
.BCC = ""
.Subject = "UPDATE | " + Location + " | " + UpdateDate
'DO NOT REMOVE - THIS MUST BE VISIBLE FIRST TO GET THE DEFAULT SIGNATURE
.Display
'GET THE HTML CODE FROM THE SIGNATURE
strSig = .HTMLBody
.HTMLBody = "Hello," & "<br>" & "<br>" & "Please see attached the Daily Field Ticket for " + Location + _
" for " + UpdateDate + "." + RangetoHTML(Sheet1.Range("A28:F35")) & "<br>" & strSig
.Display
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub
Function RangetoHTML(rng As Range)
' By Ron de Bruin.
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
推荐阅读
- android - React Native 设置 GIF 作为 android/ios 设备壁纸
- php - 我需要帮助 确定我是否在 html 或 php 代码片段中犯了错误
- mysql - 如何从文本文件中读取数据并将其放入c中的mysql数据库中
- css - svg 旋转 90 度但消失
- java - 在使用 Java 的 Selenium 中,是否可以在 testNG 中只编写一种测试方法并从中形成多个测试?
- c++ - 产量超出预期
- django - 收到 Stripe Checkout Session 付款后,如何在 Django 中更改我的“订单”?
- javascript - JavaScript - 使用 Array.prototype.splice 从数组中删除元素时出现意外结果
- php - 使用 PHP 从 Facebook 个人资料 ID 获取 Facebook 个人资料 URL
- php - PHP 警告:mysqli_stmt_bind_param() 期望参数 1 为 mysqli_stmt,bool 在