How to take care of empty cells in Excell

[navimir]

Hello,
I want to send email to list of users which are stored in Excel from A1 to A10.
But if suppose if any of the cells from A1 to A10 is empty then it gives error..
how to take care of empty cells??

Function SendMail()
Dim y As String
y = Sheets(1).Range("A1:A10").Value
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
With ActiveWorkbook
.SendMail Recipients:=y, Subject:="test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Function

 

[ZAX]

Isn't better for you if you assign the values of A1:A10 to "y" separating them with ";"....
i.e:Someone;SomoneElse;;;.. without having spaces.

Function SendMail()
Dim y As String
Rem Set recipients to y seperated by ;
For Each Cell In Range("A1:A10")
    If IsEmpty(Cell) = False Then
       If y = "" Then
          y = Cell.Value
       Else
          y = y & ";" & Cell.Value
       End If
    End If
Next
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
With ActiveWorkbook
.SendMail Recipients:=y, Subject:="test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Function

ZAX