Try the following macro :
Sub Test()
Dim rgRange As Range, Dict As Object, dblSum As Double
Dim strRecip As String
With Sheets("Sheet1")
Set Dict = CreateObject("Scripting.Dictionary")
Set rgRange = .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
For Each c In rgRange
If Not Dict.exists(c.Value) Then
Dict.Add c.Value, c.Value
End If
Next c
For Each Item In Dict.items
.[H:H].ClearContents
.[J:L].ClearContents
.[H1] = [A1]
.[J1:L1].Value = [A1:C1].Value
.[H2] = Item
Set rgRange = .Range(.[A1], .Cells(.Rows.Count, 1).End(xlUp))
rgRange.Resize(, 3).AdvancedFilter xlFilterCopy, .[H1:H2],
Range("J1:L1")
strRecip = .[L2]
dblSum = Application.Sum(.[K:K])
.Cells(.Rows.Count, "K").End(xlUp).Offset(1) = dblSum
.Cells(.Rows.Count, "J").End(xlUp).Offset(1) = "Total"
.Range(.[J1], .Cells(.Rows.Count, "K").End(xlUp)).Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Item & ".xls"
ActiveWorkbook.SendMail strRecip, "Subject"
Next Item
End With
End Sub
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de Rajat Kapoor
Envoyé : lundi 27 juin 2011 21:01
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Send email to individual receiptant of Pivot Table
data
Suppose there are fields like: Name, Commision, Email Id etc in Sheet1
Now Data contained is given below
Name Commission Email
ABC 1000 a...@abc.com
DEF 500 d...@abc.com
ABC 700 a...@abc.com
ABC 800 a...@abc.com
DEF 500 d...@abc.com
Now I can used either "Pivot Table" or "AutoSum" to get total of ABC & DEF.
But I want a macro should be created according to which email should be send
to ABC and DEF containing there data only along with Subtotal or Total.
This is just a example. Record can be easily more than 1000 and it is not
possible to send email manually. It should be send automatically to each
receiptant .
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
