如何将不同的超链接添加到发送给不同用户的excel电子邮件正文中?每封电子邮件都有一个不同的超链接。这是我迄今为止的代码:
Sub Button1_Click()
Dim rngCell As Range
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String
Dim EmailSubject As String
Dim SendToMail As String
Dim r As Long
Application.ScreenUpdating = False
With ActiveSheet
If .FilterMode Then .ShowAllData
End With
Set OutApp = CreateObject("Outlook.Application")
Set Rng = Range("T5", Cells(Rows.Count, "T").End(xlUp))
For Each rngCell In Rng
r = rngCell.Row
If Range("J" & r).Value = "" And Range("K" & r).Value <> "" And Range("I" & r).Value <= Date Then
Range("J" & r).Value = Date
Set OutMail = OutApp.CreateItem(0)
strBody = "According to my records, your " & Range("A" & r) & Range("S" & r).Value & _
" contract is due for review. This contract expires " & Range("K" & r).Value & _
". It is important you review this contract ASAP and email me " & _
"with any changes that are made. If it is renewed or rolled over, please fill out the " & _
"Contract Cover Sheet which can be found in the Everyone folder " & _
"and send me the Contract Cover Sheet along with the new original contract."
SendToMail = Range("T" & r).Value
EmailSubject = Range("A" & r).Value
On Error Resume Next
With OutMail
.To = SendToMail
.CC = "email address removed for privacy reasons"
.BCC = ""
.Subject = EmailSubject
.Body = strBody
.Display ' You can use .Send
End With
End If
Next rngCell
Application.ScreenUpdating = True
End Sub
在代码中,您正在处理一个纯文本消息体:
.Body = strBody
要设置超链接,您需要为消息正文准备一个HTML标记,然后设置HTMLBody属性,该属性返回表示指定项的HTML正文的字符串。
Outlook对象模型支持自定义消息正文的三种主要方式:
- Body属性返回或设置一个字符串,表示Outlook项目的明文正文
MailItem
类的HTMLBody属性返回或设置表示指定项的HTML正文的字符串。设置HTMLBody
属性将始终立即更新Body属性。例如:
Sub CreateHTMLMail()
'Creates a new e-mail item and modifies its properties.
Dim objMail As Outlook.MailItem
'Create e-mail item
Set objMail = Application.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>Enter the message <a href="http://google.com">text</a> here. </BODY></HTML>"
.Display
End With
End Sub
- Word对象模型可用于处理消息体。有关详细信息,请参阅第17章:使用项目机构
您应该能够添加超链接,我猜这些超链接已经在excel工作表的单元格中了。如果链接排列在名称行旁边的单元格中,则可以仅使用当前的for循环。只需添加&Range("INPUT CELL WITH HYPERLINK HERE"(.value。对于主要部分来说应该不是问题。
如果超级链接只有几个,你需要检查发送给谁,你可以使用它,以及for循环中的If条件。