如何在Excel VBA中运行多个“ For / Each”循环

debugcn 发表于 Dev

用户名

我正在从Excel文档中自动执行VBA电子邮件附件脚本。数据集看起来像这样

 File Name      Email     Body
 Sample 1       john@     Hello!
 Sample 2       mary @    Hello!

我想做的是告诉excel在“电子邮件”列下向每个人创建一封电子邮件，然后在电子邮件正文的“正文”列中写入文本，然后查找并附加一个名称为在“文件名”列下找到。因此，John @会收到一封电子邮件，上面带有“ Hello！”。和Sample 1附件。

对于每个循环，这将需要三个单独的步骤，这让我感到困惑：

到目前为止，这是我的代码，但要做的只是找到附件：

Sub Attachment()


Dim colb As Range, mycell As Range, mycell2 As Range, mycell3 As Range
Set colb = Range(Range("B2"), Range("B2").End(xlDown))
Set colc = Range(Range("C2"), Range("C2").End(xlDown))
Set cold = Range(Range("D2"), Range("C2").End(xlDown))


For Each mycell In colb

Dim path As String
path = mycell.Value


Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    Set myAttachments = OutMail.Attachments

On Error Resume Next
With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Test"
    .Body = ""
    .Display
End With
On Error GoTo 0

myAttachments.Add "C:\R\" & path

Set OutMail = Nothing
Set OutApp = Nothing

Next

结束子

Dan Donoghue

我不确定您在说什么，因为我看不到需要3次循环。您不仅可以将代码更新为此吗？

With OutMail
    .To = mycell.Offset(0, 1).Text
    .CC = ""
    .BCC = ""
    .Subject = "Test"
    .Body = mycell.Offset(0, 2).Text
    .Display
End With

这将引用mycell并从中偏移，以获取收件人和正文

在这种情况下，您可以将整个例程分解为：

Sub Attachment()
Dim colb As Range, mycell As Range
Set colb = Range(Range("B2"), Range("B2").End(xlDown))
For Each mycell In colb
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    Set myAttachments = OutMail.Attachments
    On Error Resume Next
    With OutMail
        .To = mycell.Offset(0, 1).Text
        .Subject = "Test"
        .Body = mycell.Offset(0, 2).Text
        .Display
    End With
    myAttachments.Add "C:\R\" & mycell.Text
    Set OutMail = Nothing
    Set OutApp = Nothing
    Next
End Sub