CODEWITHSUNDEEP
vbaexcelruntime-errorexcel-2010
blood_milk_sky
blood_milk_sky

Reputation: 27

Emailing Ranges Instead Of Rows

I am attempting to run through sheet 2 of an Excel workbook to email ranges to customers.

The ranges would be A1:B30,C1:D30,E1:F30 and so on with their account number in A1 & email in B1 and information below.

Every time I try to run the email it comes up with:

Run Time Error 1004

and then goes on to error

Object has been moved or deleted

Is there another way of emailing ranges or a way to amend this code? 

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

Upvotes: 2

Views: 325

Answers (1)

R3uK
R3uK

Reputation: 14537

You need to be more explicit about your references (workbook, sheet, ...).

Thx to @Ralph :

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

This run smoothly on my computer :

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

Upvotes: 1

Related Questions