Click here to Skip to main content
15,881,281 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
Public Sub SendItAll()


    ' Clear out any old data on Report
    Sheets("REPORT").Select
    Range("A1").CurrentRegion.ClearContents
    ' Sort data by name
    Sheets("PHREIMB").Select
    Range("A1").CurrentRegion.Select
    Selection.Sort Key1:=Range("A1"), Header:=xlYes
    ' Process each record on Distribution
    Sheets("Distribution").Select
    FinalRow = Range("A15000").End(xlUp).Row
    For i = 2 To FinalRow
        Sheets("Distribution").Select
        RegionToGet = Range("A" & i).Value
        Subject = "REPORT"
        Recipient = Range("D" & i).Value
                                     
        ' Clear out any old data on Report
        Sheets("REPORT").Select
        Range("A1").CurrentRegion.ClearContents
        ' Get records from Data
        Sheets("PHREIMB").Select
        Range("A1").CurrentRegion.Select
        ' Turn on AutoFilter, if it is not on
        If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
        ' Filter the data to just this region
        Selection.AutoFilter Field:=1, Criteria1:=RegionToGet
        ' Select only the visible cells and copy to Report
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy Destination:=Sheets("REPORT").Range("A1")
        
         
         
        ' Turn off the Autofilter
        Selection.AutoFilter
        ' Copy the Report sheet to a new book and e-mail
        Sheets("REPORT").Copy
        Application.Dialogs(xlDialogSendMail).Show _
            arg1:=Recipient, _
            arg2:="REPORT" & " " & RegionToGet
            
        ActiveWorkbook.Close SaveChanges:=False
    Next i
End Sub
Posted
Updated 22-Sep-15 11:49am
v2
Comments
Patrice T 22-Sep-15 19:41pm    
What is "body message from cells" in excel ?
What is your problem exactly ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900