如何在Excel中使用VBA回复Outlook邮件？

Question

我想回复一封选定的邮件。

下面的Excel VBA代码创建邮件，但在正文中没有之前的对话（线程）。

Sub Test_template()
        
    Dim emailApplication As Object
    Dim emailItem As Object
        
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.ActiveExplorer.Selection.Item(1).ReplyAll
        
    emailItem.bcc = "XYZ.com"
    emailItem.Body = "Hi, have a nice day "
    emailItem.Display
        
    Set emailItem = Nothing
    Set emailApplication = Nothing
        
End Sub

- amazo gamerniac

Answer 1

您是在尝试这个吗？我已经对代码进行了注释并提供了相关的 MSDN 链接。如果您仍然遇到困难，只需问一下。

Option Explicit

Sub Sample()
    Dim OutlookApp As Object
    Dim OutlookMail As Object

    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.ActiveExplorer.Selection.Item(1)
    
    '~~> Get MailItem.GetConversation method (Outlook)
    ' https://learn.microsoft.com/en-us/office/vba/api/outlook.mailitem.getconversation
    Dim OutlookConversation As Object
    Set OutlookConversation = OutlookMail.GetConversation

    '~~> Conversation.GetTable method (Outlook)
    ' https://learn.microsoft.com/en-us/office/vba/api/outlook.conversation.gettable
    Dim OutlookTable As Object
    Set OutlookTable = OutlookConversation.GetTable
    
    '~~> Obtains a 2D array from the Table.
    ' https://learn.microsoft.com/en-us/office/vba/api/outlook.table.getarray
    Dim OutlookAr As Variant
    OutlookAr = OutlookTable.GetArray(OutlookTable.GetRowCount)
    
    Dim OutlookReplyToThisMail As Object
    Set OutlookReplyToThisMail = OutlookMail.Session.GetItemFromID(OutlookAr(UBound(OutlookAr), 0))
    
    Dim MyMessage As String: MyMessage = "Hi, have a nice day "
    
    With OutlookReplyToThisMail.ReplyAll
        .BCC = "XYZ.com"
        .HTMLBody = MyMessage & .HTMLBody
        .Display
    End With
End Sub

- Siddharth Rout

---

谢谢，它正在运作。但是我还有一个疑问。我无法插入新行。 例如： Dim MyMessage As String: MyMessage = "Hi," & vbNewLine & "have a nice day " 输出 - Hi, have a nice day 为什么vbNewLine没有起作用？ - amazo gamerniac

因为我们将该文本用于 HTML 正文，其中 vbNewLine 没有任何意义。请尝试这样做：MyMessage = "你好，" & "<br>" & "祝你有美好的一天"。 - Siddharth Rout

这个方法在邮件主题非常相似的情况下效果不佳。GetConversation 方法似乎会选择较旧的邮件。 - findwindow

Answer 2

.ReplyAll 产生了预期的结果，但被 emailItem.Body = "嗨，祝你有美好的一天 " 覆盖了。

Option Explicit

Sub Test_template()
    
    Dim emailApplication As Object
    Dim emailItem As Object
    
    'Set emailApplication = CreateObject("Outlook.Application")
    
    ' mail has to be slelected in Outlook application so it has to be open already
    Set emailApplication = GetObject(, "Outlook.Application")
    Set emailItem = emailApplication.ActiveExplorer.Selection.Item(1).ReplyAll
    
    emailItem.BCC = "XYZ.com"
    
    'emailItem.Body = "Hi, have a nice day "
    
    emailItem.Body = "Hi, have a nice day " & emailItem.Body
    
    ' or
    'emailItem.htmlBody = "Hi, have a nice day " & emailItem.htmlBody
    
    emailItem.Display
    
    Set emailItem = Nothing
    Set emailApplication = Nothing
    
End Sub

- niton