+ Reply to Thread
Results 1 to 4 of 4

Naming Excel Range in Outlook VBA?

  1. #1
    Forum Contributor
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    376

    Naming Excel Range in Outlook VBA?

    I sometimes have non contiguous data that appears in my data set.... when I use the following code


    Set sht = XLApp.Workbooks(Attname).ActiveSheet
    Set Rng = XLApp.Workbooks(Attname).ActiveSheet.UsedRange

    it is not grabbing what might be in the last column.

    Sample i am working on selects all of the data from A1 to U35.... but there is data in v1:y4 that is not being captured.

    how do i get it to grab the data that is in v1:y4 also?

    Within Excel the following works to capture the data... but how to I translate it to Outlook ?
    Please Login or Register  to view this content.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,926

    Re: Naming Excel Range in Outlook VBA?

    Using your declarations above, like this. Only, don't use Double for integer values.
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,147

    Re: Naming Excel Range in Outlook VBA?

    It'd be helpful to see the entire macro. The code in the body of your posting sets sht to the active worksheet in the workbook identified by the variable Attname. That so, why not

    Set Rng = sht.UsedRange

    ?

    Just below that line of code, add

    Debug.Print "UsedRange: "; Rng.Address(0, 0, , 1)
    Debug.Print "count to right: "; Application.WorksheetFunction.CountA(Range(sht.Columns(Rng.Column + Rng.Columns.Count), sht.Columns(sht.Columns.Count)))
    Debug.Print "count below: "; Application.WorksheetFunction.CountA(Range(sht.Rows(Rng.Row + Rng.Rows.Count), sht.Rows(sht.Rows.Count)))
    Stop

    What does that produce in the VBA Editor's Immediate window?

    Re your last question, LastRow and LastCol should both be type Long rather than Double. I'm guessing the problem in Outlook VBA is that it lacks Excel's predefined constants, or enumerations. If so, in my opinion, safer to recreate them as named constants in Outlook VBA code.

    Please Login or Register  to view this content.
    The potential problem with this is that if the only nonblank cells are A1 and X99, this will set Rng to A1 rather than A1:X99.
    Last edited by hrlngrv; 03-13-2020 at 08:57 PM. Reason: correcting typos

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,926

    Re: Naming Excel Range in Outlook VBA?

    Quote Originally Posted by hrlngrv View Post
    I'm guessing the problem in Outlook VBA is that it lacks Excel's predefined constants, or enumerations. If so, in my opinion, safer to recreate them as named constants in Outlook VBA code.
    I concur and I overlooked this. If you are using late binding instead of setting a reference, and it looks like that is what you are doing, you must provide your own constants. Best practice is to give them the same name, as hrlngrv has shown.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel VBA to Copy Merged Range along with Normal Range and Export it to Outlook
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2020, 07:01 AM
  2. [SOLVED] Naming an Excel Range for the entire active sheet
    By SoldOnTheRoad in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2017, 11:27 AM
  3. Copy and paste Excel range as picture into Outlook email body using excel vba
    By ExcelDoc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2016, 09:29 PM
  4. [SOLVED] Excel VBA - Naming Range - Has Duplicates. Consider only Unique Values and Name that Range
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2014, 11:06 AM
  5. Creating and Naming New Outlook (or other) Calendars?
    By nickypatterson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2011, 02:05 PM
  6. Un-Naming a named range in Excel 2003?
    By jwright650 in forum Excel General
    Replies: 3
    Last Post: 01-11-2011, 05:08 PM
  7. [SOLVED] Dynamic range naming in VB6.3 for Excel
    By Paul Chapman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2005, 11:07 PM

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