Emailing Ranges Instead Of Rows

Question

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

Never heard of `ActiveSheet.MailEnvelope.Introduction` or ActiveSheet.MailEnvelope.Item`: https://msdn.microsoft.com/en-us/library/office/ff839554(v=office.15).aspx Where did you get this code from? — Ralph, Oct 12 '16 at 09:30
It errors on initially on "cr.Offset(, -1).Resize(30, 2).Select" — blood_milk_sky, Oct 12 '16 at 09:41
Funnily Enough Picked it up from Mr Excel Forum after googling but this code has given me nothing but trouble and i swear it should be easier that this. — blood_milk_sky, Oct 12 '16 at 09:55
@Ralph: I actually have that "Introduction" in my Excel 2013! — user3598756, Oct 12 '16 at 10:03
This was the solution but it doesnt work on my workbook. even though from what I can see the parameters are correct for the code to work. — blood_milk_sky, Oct 12 '16 at 10:07
Put `cr.Parent.Activate` before the line `cr.Offset(, -1).Resize(30, 2).Select`. You can only select an area on a sheet if the sheet is currently active / selected. — Ralph, Oct 12 '16 at 10:25

R3uK · Answer 1 · 2016-10-12T16:06:21.850

1

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

edited Oct 12 '16 at 16:06

answered Oct 12 '16 at 10:31

R3uK

14,417
7
43
77

I tried your code just because I have never heard of [MailEnvelope](https://msdn.microsoft.com/en-us/library/office/ff836185.aspx) before. And you are right: the code works smoothly without any errors. Yet, I didn't get to see the email and if I uncomment `.Send` then I don't get an email in my Outlook outbox. But with the code provided by the OP with my suggestion (comment above) I do get the required result. Not sure why that is. All I can say is that's what I experience here on my end. Just wanted to share. – Ralph Oct 12 '16 at 10:46
@Ralph : I checked Outlook sent items and I have the sent mails (for valid emails only)... But the 1st version of the code send the whole sheet... :/ I saw each "mail" (Excel interface to prepare mail) with a breakpoint on `.display` (both `.display` and `.send` use Outlook) but can't seem to keep all of them viewable like with direct use of Outlook... – R3uK Oct 12 '16 at 11:06
Just felt like sharing that it doesn't seem to work on my system. I will look more into that if I ever have the need. Right now, all that matters is that it seems to work for you and (hopefully) also for the OP. BTW, with the OP code only the `selection` got sent on my computer and it did not matter if the email address was valid (took Outlook offline for testing and found the emails in the outbox). Setup here: Excel 2016 64-bit on Windows7. – Ralph Oct 12 '16 at 11:20
@Ralph : Same setup, Outlook seems to send mails but I only see the ones with valid addresses in Sent Items. I edited my answer to actually select the desired ranged. I'm clueless on why you need to use `Selection` rather a custom range. We'll see if this work for the OP! Thx for your inputs! – R3uK Oct 12 '16 at 11:40
1

Then you should include in your code my comment above. A range can be only selected if the sheet is activated first. Otherwise, you'll get an error. – Ralph Oct 12 '16 at 11:42
1

Sorry to be such a hazzle but `ActiveWorkbook.ActiveSheet.Select` may fail on some systems (obviously not on ours): http://stackoverflow.com/questions/24344607/excel-vba-select-method-of-range-class-failed Sheets should be activated and not selected. – Ralph Oct 12 '16 at 11:52
@Ralph : No problem, I didn't know that it could fail (I never use it^^). I edited it ;) – R3uK Oct 12 '16 at 11:54
Works fine for me if I change: `RangeToSend.Select` `With Selection.Parent.MailEnvelope` To `With RangeToSend.Parent.MailEnvelope` – Jon Carlstedt Oct 12 '16 at 11:58
Hi that works really well but it is sending the whole of sheet 2 and not the individual ranges on this sheet to the different email addresses – blood_milk_sky Oct 12 '16 at 12:49
@blood_milk_sky : Did you try the change that Jon Carlsted did? What is your setup? – R3uK Oct 12 '16 at 12:52
@JonCarlstedt : This method seems to be highly variable... I HAD TO change it the other way to have only the range included... And OP seems to have the same issue than you. What is your setup? – R3uK Oct 12 '16 at 12:53
my appologies I havent yet literally just seen your code posted. – blood_milk_sky Oct 12 '16 at 12:59
@R3uK : I am on Excel 2013 (15.0.4867.1000) Professional Plus 32-bit, English version on Win 7 Enterprise. Note: It might be a Scandinavian version installed with English language pack. – Jon Carlstedt Oct 12 '16 at 13:32
so should this be what it looks like ? – blood_milk_sky Oct 12 '16 at 15:39
With RangeToSend.Parent.MailEnvelope .Introduction = "Good Morning" With .Item – blood_milk_sky Oct 12 '16 at 15:40
@blood_milk_sky : Try the edit and tell me if it works as expected – R3uK Oct 12 '16 at 15:50
Hi, it just goes on to a compile error: Invalid Or Unqualified reference. – blood_milk_sky Oct 12 '16 at 15:58
I am on Excel 2010 (14.0.7166.5000) Professional Plus 32 bit – blood_milk_sky Oct 12 '16 at 16:00
@blood_milk_sky : Try the redited version, if it doesn't work, I think it'll be easier to use Outlook directly... – R3uK Oct 12 '16 at 16:07
it sends one email then errors on "With Selection.Parent.MailEnvelope" sorrry for the hassel an i really appreciate all of your help – blood_milk_sky Oct 12 '16 at 16:14
and then if you Uncomment With RangeToSend.Parent.MailEnvelope it errors on compile error : Loop Without do – blood_milk_sky Oct 12 '16 at 16:17
@blood_milk_sky : Ok... :/ I'm not sure how to go with this... I don't have the same issue here so hard to help more... If you are open to use Outlook from the start I'll propose an answer tomorrow for this ;) – R3uK Oct 12 '16 at 16:22
@R3uK I am just looking to ensure that these named ranges get sent to the correct email address's so anyway that we could do it would be great. – blood_milk_sky Oct 13 '16 at 11:01
Is there another way of doing it without the code I provided initially. – blood_milk_sky Oct 14 '16 at 13:30

Emailing Ranges Instead Of Rows

1 Answers1