VBA How to get the next cell address

Question

I am writing a piece of code to go through a loop, what I want to do is store the cell address J2 in a variable and then each time the code runs through the loop get it to go onto the next cell down, i.e. J2 would go to J3 then J3 would go to J4 and so on.

This is what I've got at the minute and have tried:

Index = 0
CurrentCell = Range("J2").Address

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

RowsCount = Application.CountA(Range("A3:A" & Rows.Count))

While Index < RowsCount

    CurrentCell = CurrentCell + 1
    Recipients = CurrentCell + ";"
    Index = Index + 1

Wend

Can someone please help.

Answer 1

Not sure what currentcell is meant to be doing (so I have removed it) but the set rng line will be set to each row

Replace your loop with below

While Index < RowsCount

    set rng = range("J2").offset(0+Index,0)
    Recipients = Recipients & rng.value & ";"
    Index = Index + 1

Wend

Answer 2

This line should assign the address of the next cell:

CurrentCell = Range(CurrentCell).Offset(1, 0).Address