vba - 通过电子邮件发送范围而不是行

Question

我正在尝试浏览 Excel 工作簿的第 2 页，以通过电子邮件将范围发送给客户。

范围为 A1:B30、C1:D30、E1:F30 等，其帐号在 A1 中，电子邮件在 B1 中，信息如下。

每次我尝试运行电子邮件时，它都会出现:

Run Time Error 1004

然后继续出错

Object has been moved or deleted

是否有另一种通过电子邮件发送范围或修改此代码的方法？

Sub EmailRanges()
Dim cr As Range
Set cr = [b1]
ActiveWorkbook.EnvelopeVisible = True
Do While cr <> ""
    cr.Offset(, -1).Resize(30, 2).Select
    With ActiveSheet.MailEnvelope
        .Introduction = " Good Morning"
        .Item.To = cr
        .Item.Subject = "Just testing, sorry for filling you inbox ^_^ "
        .item.Send                                 ' to send
        .Item.Display                               ' to test
    End With
    MsgBox cr & " receives " & Selection.Address
    Set cr = cr.Offset(, 2)
Loop
Application.ScreenUpdating = True
MsgBox "The Customers Have Been Notified"
End Sub

Answer 1

您需要更明确地说明您的引用资料(工作簿、工作表……)。

感谢@Ralph:

A range can be only selected if the sheet is activated first. Otherwise, you'll get an error.

这在我的电脑上运行顺利:

Sub Email_Ranges()
    Dim rG As Range
    Dim RangeToSend As Range
    Dim CustomerMail As String

    Set rG = ActiveWorkbook.ActiveSheet.[b1]

    ActiveWorkbook.EnvelopeVisible = True

    Do While rG.Value <> vbNullString
        CustomerMail = rG.Value
        Set RangeToSend = rG.Offset(, -1).Resize(30, 2)

        'With RangeToSend.Parent.MailEnvelope

        ''Uncomment below if you get an error
        rG.Parent.Activate
        RangeToSend.Select
        With Selection.Parent.MailEnvelope

            .Introduction = "Good Morning"
            With .Item
                .To = CustomerMail
                .Subject = "Just testing, sorry for filling your inbox ^_^ "
                .display    'to test
                .Send      'to send
            End With
        End With
        Debug.Print CustomerMail & " receives " & RangeToSend.Address
        Set rG = rG.Offset(, 2)
    Loop

    ActiveWorkbook.EnvelopeVisible = False
End Sub