Thread: Excel Email VBA

Hi All

I have the following code which allows me to create an email from excel. It attaches the spreadsheet that I am in, but only copies over the relevent information that is assigned to the person it is being sent to. Further to this, I can also regulate which information that I want to send by filtering the fields.

However the problem that I am facing is that, I cannot get the code to populate the body of the email Please help

I have attached an example document with dummy data

It's this section of the code that is causing me problems:
Application.Dialogs(xlDialogSendMail).Show _
arg1:=Recipient, _
arg2:="Report for " & RegionToGet

Full code:

Public Sub SendItAll()
    ' Clear out any old data on Report
    Sheets("Report").Select
    Range("A1").CurrentRegion.ClearContents
    ' Sort data by region
    Sheets("Data").Select
    Range("A1").CurrentRegion.Select
    Selection.Sort Key1:=Range("D2"), 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
        Recipient = Range("B" & i).Value
        Product = Range("C" & i).Value
        ' Clear out any old data on Report
        Sheets("Report").Select
        Range("A1").CurrentRegion.ClearContents
        ' Get records from Data
        Sheets("Data").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 [change the field to the column number where the names are kept]
        Selection.AutoFilter Field:=4, Criteria1:=RegionToGet
        Selection.AutoFilter Field:=2, Criteria1:=Product
        ' 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 for " & RegionToGet
        ActiveWorkbook.Close SaveChanges:=False
    Next i
End Sub

Thank you
JG