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.

Solution 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

Solution 2

Taking a guess at what you're trying to achieve:

Dim lIndex                As Long
Dim lLastRow              As Long

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
' find the number of the last populated row in column A
lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For lIndex = 2 To lLastRow
    ' append the values of each cell in column J to a string, separated by semicolons
    Recipients = Recipients & ";" & Cells(lIndex, "J").Value
Next lIndex
' strip off the leading semicolon by taking everything after the first character
Recipients = Mid$(Recipients, 2)