Iterating through columns in VBA (Excel)

Question

When I typed that title, I came across many help topics and looked up few of them and got some basic idea. I still am stuck and that is why creating a new post to get some guidance.

Scenario is this:

My excel sheet has Y or N letter in A1, some text in B1 and a valid future date in C1.

I currently have a VBA script that does this for a single row:

Checks whether the (date in C1 -15 days) is today's date and then check if A1 is N. If both these conditions are true then send an automatic e-mail.

What I do not know is, How do I make it function for a n number of rows?

Here is what I have as of now.

Sub SendAlert()
'Set up the objects required for Automation into lotus notes
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'The current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    Dim stSignature As String
    Dim i As Integer
    Dim lastRow As Long

    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")

    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

    'Open the mail database in notes
    Set Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If

    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"

    'Setting Flag to decide whether to send the mail or not
    mSend = 0

    'Copying Cells - Cell A1 as Copying Status , Cell B1 as Pending Tasks Cell C1 as Deadline Date
    cStatus = cStatus & Cells(1, 1)
    Msg = Msg & Cells(1, 2)
    dDate = dDate & Cells(1, 3)

    **'Looping through the cells**
    lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Gets the last row number
    For i = 1 To lastRow
        if & Cells(i,1) = "N"


    'Attach Your Signature
    stSignature = "Regards," & vbCrLf & "Some Name"

    'MailDoc.Recipient = Recipient
    Addressee = "[email protected],"
    Recipient = Split(Addressee, ",")
    MailDoc.sendto = Recipient

    'MailDoc.Subject = Subject
    MailDoc.Subject = "Pending Activities Report"

    'MailDoc.Body = BodyText
    MailDoc.Body = "Dear NXC Team," & vbCrLf & vbCrLf & "Here are the pending activities:" & vbCrLf & vbCrLf & Msg & vbCrLf & vbCrLf & stSignature

    'MailDoc.SAVEMESSAGEONSEND = SaveIt
    MailDoc.SAVEMESSAGEONSEND = True

    'Send the document
    If (DateAdd("d", -15, dDate) = Date) And (cStatus = "N") Then
    MailDoc.SEND 0, Recipient
    End If

    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub

Looping through the cells is what I can not understand. I even have the logic..

1. Check all cells(i,3) where i is from 1 till last non-blank row.
2. Manipulate date - 15 days for every data in cell (i,3).
3. If any of those is matching with today then check it's corresponding (i,1) column.
4. If that particular (i,1) is set to N then trigger the e-mail.

I am having a mSend flag. It initially will be 0. If the 4th condition satisfied then I will set it to 1 and then check mSend value before I trigger the e-mail.

Someone please guide.

Answer 1

I myself got it :)

lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Gets the last row number
For i = 1 To lastRow
    If DateAdd("d", -15, Cells(i, 3)) = Date Then
        If Cells(i, 1) = "N" Then
            mSend = 1
            Msg = Msg & Cells(i, 2)
        End If
    End If
Next i

Thanks!