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
- Filter your data as desired using the AutoFilter feature in Excel.
- 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
- Open the Visual Basic Editor by pressing
Alt + F11
or by navigating toDeveloper
>Visual Basic
in the ribbon. - Create a new module by clicking
Insert
>Module
in the Editor. - 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.
- Save the module by clicking
File
>Save
(or pressCtrl + S
). - To run the macro, click
Developer
>Macros
in the ribbon, select theSendFilteredDataViaEmail
macro, and clickRun
.
Note: Make sure to adjust the code to match your specific Excel version and Outlook configuration.