Get values from a named range in Excel into an Outlook email

Question

I need to put values from a named range in my workbook into an Outlook mail.

On my tab called "Settings" I have a named range for cells C3:C8 called EmailTo. Each row has a different e-mail address.

.To = Range("EmailTo") gives an error

object does not support this method

Public Sub Outlook()
    
    Dim outlook As Object
    Dim email As Object
    
    Set outlook = CreateObject("Outlook.Application")
    Set email = outlook.CreateItem(olMailItem)
    
    With email
        .To = Range("EmailTo")
        .Display
    End With
    
End Sub

It works when there is one cell that is given a named range but not when the named range is spread across multiple cells.

Answer 1

You should list the e-mail addresses in the named range to a string delimited with semicolons.

Public Sub Outlook()

Dim Outlook As Object
Dim email As Object
Dim strTo As String
Dim i As Long

Set Outlook = CreateObject("Outlook.Application")
Set email = Outlook.CreateItem(olMailItem)

With Range("EmailTo").Cells
  For i = 1 To .Count
    If Not IsEmpty(.Item(i)) Then         'skip empty cells
      strTo = strTo & "; " & Trim$(.Item(i).Value) 'add all e-mail addresses with delimiter
    End If
  Next i
  strTo = Mid(strTo, 3)                   'remove leading semicolon and space
End With

With email
  .To = strTo
  .Display
End With

End Sub