循环遍历每一行。

huangapple
go评论87阅读模式
英文:

Loop through each row

问题

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.

我知道我需要一个循环。

  1. Sub Single_attachment()
  2.     Dim appOutlook As Object
  3.     Dim Email As Object
  4.     Dim Source, mailto As String
  5.     Set appOutlook = CreateObject("Outlook.Application")
  6.     Set Email = appOutlook.CreateItem(olMailItem)
  7.     mailto = mailto & Cells(2, 2) & ";"
  8.     Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
  9.     Email.Attachments.Add Source
  10.     ThisWorkbook.Save
  11.     Source = ThisWorkbook.FullName
  12.     Email.Attachments.Add Source
  13.     Email.To = mailto
  14.     Email.Subject = "Important Sheets"
  15.     Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
  16.     Email.Display
  17. End Sub
  1. 子单个附件()
  2.     Dim appOutlook As Object
  3.     Dim Email As Object
  4.     Dim Source，mailto As String
  5.     Set appOutlook = CreateObject("Outlook.Application")
  6.     Set Email = appOutlook.CreateItem(olMailItem)
  7.     mailto = mailto & Cells(2, 2) & ";"
  8.     Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
  9.     Email.Attachments.Add Source
  10.     ThisWorkbook.Save
  11.     Source = ThisWorkbook.FullName
  12.     Email.Attachments.Add Source
  13.     Email.To = mailto
  14.     Email.Subject = "Important Sheets"
  15.     Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
  16.     Email.Display
  17. End Sub
英文:

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.

I know I need a loop.

  1. Sub Single_attachment()
  2.     Dim appOutlook As Object
  3.     Dim Email As Object
  4.     Dim Source, mailto As String
  5.     Set appOutlook = CreateObject("Outlook.Application")
  6.     Set Email = appOutlook.CreateItem(olMailItem)
  7.     mailto = mailto & Cells(2, 2) & ";"
  8.     Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
  9.     Email.Attachments.Add Source
  10.     ThisWorkbook.Save
  11.     Source = ThisWorkbook.FullName
  12.     Email.Attachments.Add Source
  13.     Email.To = mailto
  14.     Email.Subject = "Important Sheets"
  15.     Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
  16.     Email.Display
  17. End Sub

答案1

得分: 1

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

  1. 实现一个for循环是正确的方法。以下是在您的代码中如何实现：
  3. Sub Single_attachment()
  5.     Dim appOutlook As Object
  6.     Dim Email As Object
  7.     Dim Source, mailto As String
  8.     Set appOutlook = CreateObject("Outlook.Application")
  9.     Set Email = appOutlook.CreateItem(olMailItem)
  11.     '实现一个循环，每次迭代将设置`i`，首先为2，然后为3，以此类推，直到i=6
  12.     For i = 2 to 6
  13.         '现在在引用单元格时，使用变量`i`作为行号
  14.         mailto = mailto & Cells(i, 2) & ";"
  15.         Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(i, 3)
  16.         Email.Attachments.Add Source
  17.         ThisWorkbook.Save
  18.         Source = ThisWorkbook.FullName
  19.         Email.Attachments.Add Source
  20.         Email.To = mailto
  21.         Email.Subject = "Important Sheets"
  22.         Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
  23.         Email.Display
  24.    Next i
  26. End Sub

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

英文:

Implementing a for loop is the right way to go. Here's how to do that in your code:

  1. Sub Single_attachment()
  3.     Dim appOutlook As Object
  4.     Dim Email As Object
  5.     Dim Source, mailto As String
  6.     Set appOutlook = CreateObject("Outlook.Application")
  7.     Set Email = appOutlook.CreateItem(olMailItem)
  9.     'implement a loop that will set `i` each iteration, first to 2, then to 3, etc ending at i=6
  10.     For i = 2 to 6
  11.         'now when referring to a cell, use variable `i` for the row
  12.         mailto = mailto & Cells(i, 2) & ";"
  13.         Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(i, 3)
  14.         Email.Attachments.Add Source
  15.         ThisWorkbook.Save
  16.         Source = ThisWorkbook.FullName
  17.         Email.Attachments.Add Source
  18.         Email.To = mailto
  19.         Email.Subject = "Important Sheets"
  20.         Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
  21.         Email.Display
  22.    Next i
  24. End Sub

huangapple
  • 本文由 发表于 2023年6月12日 22:45:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/76457796.html
  • email
  • excel
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定