How to work with empty cells in Excel Macro

navi - May 6, 2013 at 05:38 AM

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)
' Send Email
With ActiveWorkbook
.SendMail Recipients:=y, Subject:="test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With

End Function