How to send filtered data in excel via mail

To send filtered data in Excel via email, you can use a combination of Excel formulas and VBA macros. Here's a step-by-step guide:

Method 1: Using Excel Formulas

  1. Filter your data as desired using the AutoFilter feature in Excel.
  2. Use the FILTER function to extract the filtered data. For example, if your data is in range A1:E10, you can use the following formula:

=FILTER(A1:E10, A1:A10>10)

This formula filters the data in column A to only show values greater than 10. 3. Use the TEXTJOIN function to concatenate the filtered data into a single string. For example:

=TEXTJOIN(", ", TRUE, FILTER(A1:E10, A1:A10>10))

This formula joins the filtered data in column A with commas and spaces. 4. Use the SUBSTITUTE function to remove any unnecessary characters from the string. For example:

=SUBSTITUTE(TEXTJOIN(", ", TRUE, FILTER(A1:E10, A1:A10>10)), " ", "")

This formula removes any spaces from the string. 5. Use the ENCODEURL function to encode the string for email transmission. For example:

=ENCODEURL(SUBSTITUTE(TEXTJOIN(", ", TRUE, FILTER(A1:E10, A1:A10>10)), " ", ""))

This formula encodes the string using URL encoding. 6. Use the MAILTO function to send the encoded string as an email. For example:

=MAILTO("[email protected]", "Filtered Data", ENCODEURL(SUBSTITUTE(TEXTJOIN(", ", TRUE, FILTER(A1:E10, A1:A10>10)), " ", "")))

This formula sends an email to the recipient with the subject "Filtered Data" and the encoded string as the body.

Method 2: Using VBA Macro

  1. Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.
  2. Create a new module by clicking Insert > Module in the Editor.
  3. Paste the following code into the module:
Sub SendFilteredDataViaEmail()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("YourSheetName")

    Dim filteredData As Range
    Set filteredData = ws.Range("A1:E10").AutoFilter.Range.Columns(1).Offset(1, 0).Resize(ws.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

    Dim emailBody As String
    emailBody = Join(Application.Transpose(filteredData.Value), ", ")

    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    Dim olMail As Object
    Set olMail = olApp.CreateItem(0)

    With olMail
       .To = "[email protected]"
       .Subject = "Filtered Data"
       .Body = emailBody
       .Send
    End With

    Set olMail = Nothing
    Set olApp = Nothing
End Sub

Replace "YourSheetName" with the name of your worksheet, and "[email protected]" with the email address of the recipient.

  1. Save the module by clicking File > Save (or press Ctrl + S).
  2. To run the macro, click Developer > Macros in the ribbon, select the SendFilteredDataViaEmail macro, and click Run.

Note: Make sure to adjust the code to match your specific Excel version and Outlook configuration.