CODEWITHSUNDEEP
excelloopsfor-loopvba
Rahul Shah
Rahul Shah

Reputation: 1407

Change loop from every cell to particular range in Excel VBA

I have the following piece of code which sends emails in bulk.

Sub Sengrd_Files()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range
    Dim FileCell As Range
    Dim rng As Range
para2 = ""
para3 = ""

para232 = Range("AA2").Value

    With Application
        .EnableEvents = False
        .ScreenUpdating = True
    End With

    Set sh = Sheets("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")

    For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)

        'Enter the path/file names in the C:Z column in each row
        Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")

        If cell.Value Like "?*@?*.?*" And _
           Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)

            With OutMail
                .to = cell.Value
                .Subject = "Circle Profitability Report for the period ended 30-NOV-2017"
                .Body = "Dear Sir/Madam," _
& vbNewLine _
    & para232 & vbNewLine _
    & vbNewLine & para2 & vbNewLine _
    & Remark & vbNewLine & vbNewLine _
    & para3 & vbNewLine & vbNewLine


                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell

                .Send  'Or use .Display
            End With

            Set OutMail = Nothing
        End If
    Next cell

    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

enter image description here

7 different mails will be sent to different people mentioned in Column B with Attachment defined in Col C. The Macro by default sends mails for ALL line items probably because of this line in code

**For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)**

I cannot define a variable i and change the above line to

**For Each i =1 to 5 sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)**

due to syntax error. Can anyone help me in syntax in replacing "For each cell in" to a finite range.

Upvotes: 1

Views: 574

Answers (1)

Vityata
Vityata

Reputation: 43595

This is how to make the bulk-mail-sender send only to a given range (in this case B2 - B5):

For Each cell In sh.Range("B2:B5")

And do not forget - spam is bad.

Upvotes: 2

Related Questions