• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter Data based on a list in another sheet and Email

naveeddil

New Member
Dear All,

I have a data in sheet5 (in a table called "SUMMARYTABLE") and it is being filtered on a list placed in Sheet3.
I copied and modified below code to meet my requirement although need a little modifications as its giving some problems.

1. As the list in sheet3 is long as it is linked on formula, so filter only until valid values (value greater than 0). If value reach equal to 0 or BLANK so it should stop generating more actions/ mails. (currently its generating 200+ blank emails).

2. I want to add some text in the Body. i.e. "Hello Name! (column N in sheet 3) Find below list of villages for your survey. Thanks".

3. Can I select few columns data from Sheet5 instead of all and change the order of the data through VBA. Like i want only Column K, L, N, A, B, C, D and in this order as well while being copied to Outlook mail.



Code:
Option Explicit
Sub Filter_ALL_Clusters_and_Send_via_Email()
    Dim rg As Range, i As Long
    Dim fltr As Range, oDoc As Object

    Set rg = Sheet3.Cells(1, 1).CurrentRegion
    Set fltr = Sheet5.Cells(1, 1).CurrentRegion
   
    For i = 2 To rg.Rows.Count
        fltr.AutoFilter 1, rg(i, 1).Value2
        With CreateObject("Outlook.Application").CreateItem(0)
            .Displaynnnnn
            .To = rg(i, 9).Value2
            .Subject = "Report"
            Set oDoc = .GetInspector.WordEditor
            fltr.SpecialCells(xlCellTypeVisible).Copy
            oDoc.Range(0, 0).Paste
            '.Send
        End With
    Next i
End Sub

Thanks in advance
 

Attachments

  • Randomizer.xlsm
    369.4 KB · Views: 3
Back
Top