使用VBA从Excel工作表发送多个附件 [英] Sending multiple attachments from excel sheet with VBA

问题描述

我具有现有代码，可以从Excel文件中的工作表发送邮件-

I have the existing code to send a mail from a Sheet in my Excel file -

Sub CreateMail()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim rngTo As Range
    Dim rngSubject As Range
    Dim rngBody As Range
    Dim rngAttach As Range

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    Application.ScreenUpdating = False
    Worksheets("Mail List").Activate

    With ActiveSheet
        Set rngTo = .Range("B1")
        Set rngSubject = .Range("B2")
        Set rngBody = .Range("B3")
        Set rngAttach = .Range("B4")

    End With

    With objMail
        .To = rngTo.Value
        .Subject = rngSubject.Value
        .body = rngBody.Value
        .Attachments.Add rngAttach.Value
        .display 'Instead of .Display, you can use .Send to send the email _
                    or .Save to save a copy in the drafts folder
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
    Set rngTo = Nothing
    Set rngSubject = Nothing
    Set rngBody = Nothing
    Set rngAttach = Nothing

End Sub

但是，我想包含一些附件，因此
Set rngAttach = .Range（ B4）对此无济于事。

However, I want to include a number of attachments, and hence the Set rngAttach = .Range("B4") does not help to do this.

对此有任何帮助吗？
预先感谢！

Any help on this? Thanks in advance!

推荐答案

要使其动态，可以将i的上限设置为其中的最后一行B列

To make it Dynamic you can set the upper limit of i to the last row in Column B

For i = 4 To Range("B" & rows.count).end(xlUp).row
  .Attachments.Add Range("B" & i).Value
Next i 

这篇关于使用VBA从Excel工作表发送多个附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！