如何在Excel VBA邮件代码中添加.SentOnBehalfOf实现共享邮箱发件？

你只需要在创建Outlook邮件对象后、发送邮件之前的任意位置添加SentOnBehalfOfName属性即可，最稳妥的时机是刚创建完邮件对象就设置，这样能避免后续属性设置可能带来的冲突，同时要确保你已拥有该共享邮箱的发件权限。

修改后的完整代码如下：

Sub send_email()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Statements")
    
    Dim OA As Object
    Dim msg As Object
    
    Set OA = CreateObject("Outlook.Application")
    Dim each_row As Integer
    Dim last_row As Integer
    last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
  
    For each_row = 2 To last_row
        Set msg = OA.createitem(0)
        ' 在这里添加共享邮箱发件设置，替换为你的共享邮箱完整地址
        msg.SentOnBehalfOfName = "shared_mailbox@yourdomain.com"
        
        msg.To = sh.Range("A" & each_row).Value
        Dim first_name As String
        Dim last_name As String
        first_name = sh.Range("B" & each_row).Value
        last_name = sh.Range("C" & each_row).Value
        msg.cc = sh.Range("D" & each_row).Value
        msg.Subject = sh.Range("E" & each_row).Value
        msg.body = sh.Range("F" & each_row).Value
        Dim date_to_send As String
        date_to_send = Format(sh.Range("H" & each_row).Value, "dd/mm/yyyy")
        Dim Status As String
        Status = sh.Range("I" & each_row).Value
        Dim current_date As String
        current_date = Format(Date, "dd/mm/yyyy")
        
        If date_to_send = current_date Then
            If sh.Range("G" & each_row).Value <> "" Then
                msg.attachments.Add sh.Range("G" & each_row).Value
                sh.Cells(each_row, 9).Value = "Sent"
                Dim Content As String
                Content = Replace(msg.body, "<>", first_name & " " & last_name)
                msg.body = Content
                msg.send
            Else
                sh.Cells(each_row, 9).Value = "Sent"
                Content = Replace(msg.body, "<>", first_name & " " & last_name)
                msg.body = Content
                msg.send
            End If
        End If
    Next each_row
End Sub

补充说明 #

• SentOnBehalfOfName的值必须是你有权限使用的共享邮箱完整地址（例如team-support@company.com）
• 给未声明的变量补加了类型声明，避免潜在的类型错误
• 操作单元格时明确指定了工作表（sh.Cells而非直接Cells），防止切换工作表后出现错误

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