Change loop from every cell to particular range in Excel VBA

Question

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

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.

Why does your code look like it was hit by a truck? At the very least, delete the excessive line breaks. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Jan 22, 2018 at 10:08
What makes you think that line is the problem? Have you tried stepping through your code to debug? — SJR
– SJR, Commented Jan 22, 2018 at 10:11

Vityata · Accepted Answer · 2018-01-22 10:10:15Z

2

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.

answered Jan 22, 2018 at 10:10

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vityata Over a year ago

@Michel - modify it without the .Cells.SpecialCells(xlCellTypeConstants) part.

Rahul Shah Over a year ago

when i change to to" For Each cell In sh.Range("B2:B5").Cells.SpecialCells(xlCellTypeConstants)" it limits to B5 ,but sends the 1st mail 4 items :(

Vityata Over a year ago

@Michel - when you write only For Each cell In sh.Range("B2:B5") it will send it to B2, B3, B4 and B5. If you want to include B6 then write For Each cell in sh.Range("B2:B6"). And do not write the .Cells.SpecialCells(xlCellTypeConstants) part.

Rahul Shah Over a year ago

thanks a lot......should have avoided asking....i was so engaged in for i=1 to 5 for hours,that i almost forgot that i could have modified sh.Columns("B") in the next few words

Vityata Over a year ago

@Michel - it is always good to ask, especially when you are giving your code & what you have tried so far.

Collectives™ on Stack Overflow

Change loop from every cell to particular range in Excel VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related