excel - 向多个收件人发送带有附件的电子邮件

Question

好吧，让我先说这是我第一次使用 VBA 来自动化某些东西。我需要将发票（以 pdf 格式）发送到订阅时事通讯的公司列表；每家公司都有 1-50 封订阅的电子邮件。我可以将发票分别发送给每个收件人，但我想得到它，以便公司的所有订阅者都在 Outlook 电子邮件的收件人部分。这个想法是有一个数据透视表，显示电子邮件应该发送到的订阅者列表。该列表被用作电子邮件中的收件人。我想我可以弄清楚如何做到这一点，但我得到了一个

运行时错误 '13：类型不匹配

在“+1 to Lookup Cell”步骤和“Set outlookapp = CreateObject”步骤之间。令人沮丧的是，这是我用来单独发送电子邮件的确切代码，迄今为止唯一的修改是“向查找单元添加 +1”步骤。对Error'13进行了一些研究，但无法弄清楚，希望有人能对此有所了解。谢谢！

 Sub Send_Invoices()
    Dim edress As String
    Dim strfrom As String
    Dim subj As String
    Dim message As String
    Dim filename As String
    Dim filename2 As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim myAttachments As Object
    Dim path As String
    Dim lastrow As Integer
    Dim attachment As String
    Dim attachment2 As String
    Dim x As Integer
    Dim wk As Worksheet

'Define Worksheet
    Set wk = Worksheets("Sending Invoices")

'Add +1 to the Lookup Cell
    Do
    Range("F1").Value = Range("F1").Value + 1


    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.CreateItem(0)
    Set myAttachments = outlookmailitem.Attachments
        path = "C:\Users\...\Desktop\...\Invoices\"

'Set the recipient address column
    edress = wk.Cells(8, 12)
    'Set the Subject
    subj = "12/1/2019: Annual Support for ..."
    'Set the filename and location of Invoice
    filename = wk.Cells(2, 4)
    attachment = wk.Cells(2, 6)

    'Repeat for the Subscriber Lists
    filename2 = wk.Cells(2, 5)
    attachment2 = wk.Cells(2, 7)

    outlookmailitem.To = edress
    outlookmailitem.cc = ""
    outlookmailitem.bcc = ""
    outlookmailitem.Subject = subj
    outlookmailitem.body = "Dear ..."

              myAttachments.Add (attachment)
              myAttachments.Add (attachment2)
            'outlookmailitem.Display
            outlookmailitem.send


    lastrow = lastrow + 1
    edress = ""
    x = x + 1


    Set outlookapp = Nothing
    Set outlookmailitem = Nothing

    Loop Until Range("F1") >= Range("F2")


    End Sub

标签： excelvbaoutlook