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.
Thanks in advance
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