Refer to range in VBA Excel (macro)

[Jyggalag]

Hi All,

I am having an issue in Excel. My current setup looks like this:

However, if I put range("O2") it works fine and it sends out the email to my email address. But once I try to increase the range and make it send out to multiple emails, it comes with the following error (highlighting the line above):

Does anybody know how to fix this issue? It would truly help!!

Thank you

Kind regards,
Jyggalag

 

[RoryA]

You can't pass an array to the To property, it needs a delimited string. You can use a function like:

Function GetAddressesFromRange(InputRange As Range, Optional Delimiter As String = ";") As String
    Dim Cell As Range
    For Each Cell In InputRange.Cells
        Dim EmailList As String
        If Len(Cell.Value) <> 0 Then
            EmailList = EmailList & Delimiter & Cell.Value
        End If
    Next Cell
    If Len(EmailList) <> 0 Then GetAddressesFromRange = Mid$(EmailList, Len(Delimiter) + 1)
End Function

then in your calling code use:

.To = GetAddressesFromRange(Range("O2:O10"))

Side note, posting your code as pictures really doesn't help us to help you.

 

[mohadin]

Hi
what about

EmailItem.to = Join(Filter(Application.Transpose(Range("O2:O10")), " ", False), ";")

 

[Jyggalag]

You can't pass an array to the To property, it needs a delimited string. You can use a function like:

Function GetAddressesFromRange(InputRange As Range, Optional Delimiter As String = ";") As String
    Dim Cell As Range
    For Each Cell In InputRange.Cells
        Dim EmailList As String
        If Len(Cell.Value) <> 0 Then
            EmailList = EmailList & Delimiter & Cell.Value
        End If
    Next Cell
    If Len(EmailList) <> 0 Then GetAddressesFromRange = Mid$(EmailList, Len(Delimiter) + 1)
End Function

then in your calling code use:

.To = GetAddressesFromRange(Range("O2:O10"))

Side note, posting your code as pictures really doesn't help us to help you.

@mohadin and @RoryA Amazing!!

I tested both of your solutions and they both work!! Mohadins seems more simple, but maybe i'm missing an advantage from yours Rory?

Nonetheless thank you so much!! I wish I could mark 2 solutions haha

Update: Will make sure to post code in the future!