VBA - Use named range to fill in email address .To block of code-CodePudding

I am working on a piece of code that emails some information. I want to use a named range to fill my .To block in the email. I have written what I thought would work. Can you read what I have an give me some guidance on what I am missing? I would prefer not to use active sheet in the code. TIA

Public Sub cmdEmailODL_Click()
Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Dim ODLEmail As Range
ODLEmail = Sheets.ODLEmail.Range("ODL_Emails")


With EItem
.To = ODLEmail
.Subject = "Overdue items"

CodePudding user response：

You probably want something like this, that reads each cell in the range into a semi-colon separated string and uses that as the .To:

strAddresslist = ""

For Each c In Range("ODL_Emails").Cells
    strAddresslist = strAddresslist & c.Value & " ; "
Next

With EItem
    .To = ODLEmail
    .Subject = "Overdue items"

CodePudding user response：

working through what was going on I did this to get it to work. I Dim'd the wb, ws and the rngODLEmail. Then Set them and called rngODLEmail in my .To block. The code works and fills in my to block with the information I want. I am unsure of its efficiency.

Public Sub cmdEmailODL_Click()
Dim EApp As Object
Dim EItem As Object
Dim wb As ThisWorkbook
Dim ws As Worksheet

Set EApp = CreateObject("Outlook.Application")
Set Eitem = EApp.CreateItem(0)
Set wb = ThisWorkbook
Set ws = wb.Sheets("ODLEmail")

Dim rngODLEmail As Range
Set rngODLEmail = ws.Range("A2:A2")

With EItem .To = rngODLEmail .Subject = "Overdue items"