首页 文章

VBA outlook - 从excel检索电子邮件地址作为收件人

提问于
浏览 次
0

我想从excel单元格中检索电子邮件地址，并将它们作为Outlook上的收件人进行复制 .

然而，前景中的“To”和“CC”是空的 .

input and output:

单元格A1是我想要 "send to" 的电子邮件地址 .

单元格A2是我想要 "CC to" 的电子邮件地址 .

my VBA code:

Sub Button1_Click()

    Dim OutApp As Object
    Dim OutMail As Object




    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)



    On Error Resume Next
    With OutMail
        .To = Cells("A1")
        .CC = Cells("A2")
        .BCC = ""
        .Subject = "This is the Subject line"

    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
excel vba outlook-vba

3 回答

  • 0

    您需要添加收件人，而不是To，CC或BCC属性 . 这些属性仅包含显示名称 . 应使用Recipients集合来修改此属性 . 例如：

    Sub CreateStatusReportToBoss()  
   Dim myItem As Outlook.MailItem  
   Dim myRecipient As Outlook.Recipient 
   Set myItem = Application.CreateItem(olMailItem)  
   Set myRecipient = myItem.Recipients.Add("Dan Wilson")  
   myItem.Subject = "Status Report"  
   myItem.Display  
End Sub

    您可能会发现以下文章有用：

    回复于
  • 0

    如果删除“On Error Resume Next”，则可以进行调试 . 以下是无效的：

    .To = Cells("A1")
.CC = Cells("A2")

    尝试

    .To = Range("A1")
.CC = Range("A2")
    回复于
  • 1

    Recipient我运气好了：

    'If not defined:
'olBCC=3
'olCC=2
'olTo=1

Set OutMail = Application.CreateItem(olMailItem) 
Set myRecipient = OutMail.Recipients.Add(Range("A1"))
'myRecipient.Type = olTo
'This is default - use for clarity if desired
Set myRecipient = OutMail.Recipients.Add(Range("A2")) 
myRecipient.Type = olCC

    如果要添加多个收件人，则必须一次添加一个

    回复于

相关问题