问题

I am trying to run VBA code that generates email with doc attached.

这段代码用于生成带有附件的电子邮件。

The code generates only one email. I have 5 rows on my source Excel file.

这段代码只生成一封电子邮件。我在我的源Excel文件中有5行数据。

I know I need a loop.

我知道我需要一个循环。

Sub Single_attachment()
    Dim appOutlook As Object
    Dim Email As Object
    Dim Source, mailto As String
    Set appOutlook = CreateObject("Outlook.Application")
    Set Email = appOutlook.CreateItem(olMailItem)
    mailto = mailto & Cells(2, 2) & ";"
    Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
    Email.Attachments.Add Source
    ThisWorkbook.Save
    Source = ThisWorkbook.FullName
    Email.Attachments.Add Source
    Email.To = mailto
    Email.Subject = "Important Sheets"
    Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
    Email.Display
End Sub

子单个附件()
    Dim appOutlook As Object
    Dim Email As Object
    Dim Source，mailto As String
    Set appOutlook = CreateObject("Outlook.Application")
    Set Email = appOutlook.CreateItem(olMailItem)
    mailto = mailto & Cells(2, 2) & ";"
    Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
    Email.Attachments.Add Source
    ThisWorkbook.Save
    Source = ThisWorkbook.FullName
    Email.Attachments.Add Source
    Email.To = mailto
    Email.Subject = "Important Sheets"
    Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
    Email.Display
End Sub

Sub Single_attachment()
    Dim appOutlook As Object
    Dim Email As Object
    Dim Source, mailto As String
    Set appOutlook = CreateObject("Outlook.Application")
    Set Email = appOutlook.CreateItem(olMailItem)
    mailto = mailto & Cells(2, 2) & ";"
    Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
    Email.Attachments.Add Source
    ThisWorkbook.Save
    Source = ThisWorkbook.FullName
    Email.Attachments.Add Source
    Email.To = mailto
    Email.Subject = "Important Sheets"
    Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
    Email.Display
End Sub

答案1

得分: 1

这是您的代码的翻译部分：

实现一个for循环是正确的方法。以下是在您的代码中如何实现：

Sub Single_attachment()

    Dim appOutlook As Object
    Dim Email As Object
    Dim Source, mailto As String
    Set appOutlook = CreateObject("Outlook.Application")
    Set Email = appOutlook.CreateItem(olMailItem)

    '实现一个循环，每次迭代将设置`i`，首先为2，然后为3，以此类推，直到i=6
    For i = 2 to 6
        '现在在引用单元格时，使用变量`i`作为行号
        mailto = mailto & Cells(i, 2) & ";"
        Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(i, 3)
        Email.Attachments.Add Source
        ThisWorkbook.Save
        Source = ThisWorkbook.FullName
        Email.Attachments.Add Source
        Email.To = mailto
        Email.Subject = "Important Sheets"
        Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
        Email.Display
   Next i

End Sub

如果您需要进一步的帮助，请随时告诉我。

Sub Single_attachment()

    Dim appOutlook As Object
    Dim Email As Object
    Dim Source, mailto As String
    Set appOutlook = CreateObject("Outlook.Application")
    Set Email = appOutlook.CreateItem(olMailItem)

    'implement a loop that will set `i` each iteration, first to 2, then to 3, etc ending at i=6
    For i = 2 to 6
        'now when referring to a cell, use variable `i` for the row
        mailto = mailto & Cells(i, 2) & ";"
        Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(i, 3)
        Email.Attachments.Add Source
        ThisWorkbook.Save
        Source = ThisWorkbook.FullName
        Email.Attachments.Add Source
        Email.To = mailto
        Email.Subject = "Important Sheets"
        Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
        Email.Display
   Next i

End Sub