I need to make the following code work for a variable number of rows
and stop when it reaches a #VALUE! error.


Public Sub ConcatenateEmail()
Dim myString As String
Dim r As Long

myString = ""

For r = 4 To 585
myString = myString & Cells(r, "G") & ","
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString
End Sub