Results 1 to 7 of 7

Emailing User Defined Ranges

Threaded View

bloodmilksky Emailing User Defined Ranges 11-02-2016, 09:09 AM
NoSparks Re: Emailing User Defined... 11-02-2016, 09:18 AM
S O Re: Emailing User Defined... 11-02-2016, 09:21 AM
bloodmilksky Re: Emailing User Defined... 11-02-2016, 09:57 AM
dmcgov Re: Emailing User Defined... 11-02-2016, 10:38 AM
bloodmilksky Re: Emailing User Defined... 11-03-2016, 11:46 AM
dmcgov Re: Emailing User Defined... 11-03-2016, 12:32 PM
  1. #1
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Emailing User Defined Ranges

    Hi Guys, I hope you are all well today

    I was wondering if anyone could help me with the below. I am trying to use the below code to email user defined ranges(the name of the range is shown as a value in first cell, the emaill address is in the 2nd with information below). on sheet 2 of a workbook. but every time I run the macro it errors with :

    Compile Error:

    Loop Without Do.

     
        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
    any help would be greatly appreciated

    Many thanks

    jamie
    Last edited by bloodmilksky; 11-02-2016 at 09:36 AM. Reason: CODE ENTERED WRONG

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 06-28-2016, 05:38 PM
  2. [SOLVED] Cut and Paste with User Defined Ranges in a macro
    By ehope10 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2015, 11:12 AM
  3. duplicates - user defined ranges - need help!
    By banks334 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2014, 07:21 PM
  4. Combine VLOOKUP and SUMIF to return sum of user-defined ranges in a table
    By sciondraconis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2014, 03:27 AM
  5. Replies: 0
    Last Post: 11-19-2012, 10:41 AM
  6. User Defined Functions: can control what named ranges get used?
    By djt76010 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-21-2008, 08:26 PM
  7. [SOLVED] Retaining user-defined chart settings when emailing document
    By hogon in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-28-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1