Assigning a variable to a databodyrange?

[rizzo93]

I'm using PowerQuery to produce a table containing one column of email addresses. I want these email addresses to be my distribution list in an email. I'm using VBA to create an email window with the addresses already populated.

Originally, I was using a loop to cycle through the column of email addresses and adding to the string variable with each loop. When completed, I put that string into the To: field in my code and all is fine.

But instead of using a loop, is there a way I can assign a variable to the databodyrange of the table produced by PowerQuery?

 

[Norie]

You could do something like this to get the range with the emails in it.

Dim tbl As ListObject
Dim colEmail As ListColumn
Dim rngEmail As Range

    Set tbl = Sheets("Sheet2").ListObjects("Table1")
    
    Set colEmail = tbl.ListColumns("Email")
    
    Set rngEmail = colEmail.DataBodyRange

 

[rizzo93]

You could do something like this to get the range with the emails in it.

Dim tbl As ListObject
Dim colEmail As ListColumn
Dim rngEmail As Range

    Set tbl = Sheets("Sheet2").ListObjects("Table1")
   
    Set colEmail = tbl.ListColumns("Email")
   
    Set rngEmail = colEmail.DataBodyRange

Hi Norie and thanks!

I updated a line of your code to reflect the name of my powerquery table (distroSheet is already defined):

Set tbl = distroSheet.ListObjects("q_NotAcknowledgedEmails")

But it gives this error message:

 

[rizzo93]

When I debug, it lands on this line of code:

objOutlookMsg.To = rngEmail

 

[Norie]

Are you doing anything else with rngEmail?

Don't you need to create a list of email addresses from the values in it?

 

[rizzo93]

I'm not doing anything else with rngEmail.

Yes, my goal is to create a list of email addresses in rngEmail to be used in the To: field of the email I'm creating.