Excel VBA实现Outlook邮件主题根据单元格内容动态二选一

代码调整方案 #

核心修改点 #

仅需要替换原代码中设置邮件主题的一行逻辑，使用VBA的IIf条件函数实现需求，判断规则如下：

• 先格式化S5单元格的值为hh:mm:ss时间字符串，和00:00:00比对
• 若不相等，主题取S5的显示文本
• 若相等，主题取T5的显示文本

修改后完整代码 #

Sub esendtable()

Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor

With newEmail
    .To = Sheet1.Range("S2").Text
    .CC = Sheet1.Range("S3").Text
    .BCC = ""
    ' 调整后的主题条件判断逻辑
    .Subject = IIf(Format(Sheet1.Range("S5").Value, "hh:mm:ss") <> "00:00:00", Sheet1.Range("S5").Text, Sheet1.Range("T5").Text)
    .Body = "Dear All," & vbNewLine & vbNewLine & vbTab & Sheet1.Range("S6").Text
    .Recipients.ResolveAll
    .display
        
    Sheet1.Range("B2:L12").Copy
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    Set pageEditor = Nothing
    Set xInspect = Nothing
End With

Set newEmail = Nothing
Set outlook = Nothing

End Sub

适配说明 #

如果你S5单元格存储的是文本格式的时间，而非标准日期时间格式，可将判断条件替换为：

.Subject = IIf(Sheet1.Range("S5").Text <> "00:00", Sheet1.Range("S5").Text, Sheet1.Range("T5").Text)

内容的提问来源于stack exchange，提问作者Mahmoud Badr