Email adresses from range simplified?

[wigarth]

Hi!

I have a code that generates and sends email using outlook.
the code that puts the adresses is something like this:

'something code here
.to = Range("A1").value & ";" & Range("A2").value
'something more code here

the code is simple and working great so far...
The problem however, is that the range of adresses is so big, and alot of the cells containing adresses are even empty.

So, Is there a way to make vba make a list of reciepints from a range (C6:C776) an easier way?

Best reggards
Wigarth

 

[Georgiboy]

Maybe (untested)

Sub test()
    Dim eVar() As Variant, x As Long, z As Long, rVar As Variant
   
    rVar = Range("C6:C776").Value
   
    For x = 1 To UBound(rVar)
        If rVar(x, 1) <> vbNullString Then
            ReDim Preserve eVar(z)
            eVar(z) = rVar(x, 1)
            z = z + 1
        End If
    Next x
   
    .to = Join(rVar, ";")
End Sub

 

[Joe4]

You should be able to loop through the range and add non-blank values to the list, something like this:

    Dim cell As Range
    Dim emails As String
    
    For Each cell In Range("C6:C776")
        If cell.Value <> "" Then
            emails = emails & cell.Value & ";"
        End If
    Next cell
    
    MsgBox emails

 

[wigarth]

You should be able to loop through the range and add non-blank values to the list, something like this:

    Dim cell As Range
    Dim emails As String
   
    For Each cell In Range("C6:C776")
        If cell.Value <> "" Then
            emails = emails & cell.Value & ";"
        End If
    Next cell
   
    MsgBox emails

Worked perfectly. Many thanks to you kind Joe4. Wishing you a happy easter.

 

[Joe4]

You are welcome.
Glad we were able to help!

Happy Easter to you also!