Filter Excel data and send in email body

I am not sure if this will only work with outlook, but it does not explicitly call Outlook so may be worth a shot: ( not too hard to change the range to visiblecells or pull up an inputbox for the mailaddress)
but to get started, does this work with your mail program? :)

Sub Send_Range()
   
   ' Select the range of cells on the active worksheet.
   ActiveSheet.Range("A1:B5").Select
   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
      .Item.Send
   End With
End Sub

Select allOpen in new window

Your code works when I try at home but I have outloook at home. I will try it when I go to work where I use groupwise and post back.

I was able to add your code to my combo box filter and it highlighted/activated the filtered data which is great - see below:

***********************************
Sub DropDown7_Change()
 Dim myValue As String
    Dim myDrop As DropDown
   
    Set myDrop = Sheets("Fall 2012 ").DropDowns("Drop Down 7")

    With myDrop
        If .ListIndex = 1 Then
         
       MsgBox "Nothing was selected"
 
       Else
      ActiveSheet.Range("A7:S98").Select
            ddValue = .List(.ListIndex)
           
             ActiveSheet.Range("A7").AutoFilter Field:=1, Criteria1:=ddValue
             
                ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
     
   End With
        End If
    End With
 
End Sub
***********************************

I also have a text seach on my spreadsheet that I'd like to do the same way but not sure where to put your code, below is the current code for the text search filter:

***********************************
Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Range("D1"), Target) Is Nothing Then
               
            If Target = "" Then
                ActiveSheet.Range("$A$7:$W$100").AutoFilter Field:=1
                 
           
            Else
            ActiveSheet.Range("$A$7:$W$100").AutoFilter Field:=1, Criteria1:="*" & Target & "*"
           
           
            End If
            End If
***********************************

Finally, is there a way to hide the email until needed. It stays in the spreadsheet until I close the spreadsheet. Maybe show/hide button or button that says "send email" and closes/hides email program when email is sent?

Thanks again!

Sub DropDown7_Change()
 Dim myValue As String
    Dim myDrop As DropDown
    
    Set myDrop = Sheets("Fall 2012 ").DropDowns("Drop Down 7")

    With myDrop
        If .ListIndex = 1 Then
         
       MsgBox "Nothing was selected"
  
       Else
      ActiveSheet.Range("A7:S98").Select
            ddValue = .List(.ListIndex)
            
             ActiveSheet.Range("A7").AutoFilter Field:=1, Criteria1:=ddValue
              
                ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
     
   End With
        End If
    End With
  
End Sub

Select allOpen in new window

I have this on a command button but would like to merge with another drop down since I have 2 ciriteras for searching (columns 1 and 3). This current one is working but when add the other it doesn't:



Private Sub CommandButton1_Click()
Dim myValue As String
    Dim myDrop As DropDown
   
    Set myDrop = Sheets("Fall 2012 ").DropDowns("Drop Down 7")

    With myDrop
        If .ListIndex = 1 Then
         
       MsgBox "Nothing was selected"
 
       Else
      ActiveSheet.Range("A7:S98").Select
            ddValue = .List(.ListIndex)
           
             ActiveSheet.Range("A7").AutoFilter Field:=1, Criteria1:=ddValue
             
                ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
     
   End With
        End If
    End With
 
 
 
End Sub


This is the other:

 Set myDrop = Sheets("Fall 2012 ").DropDowns("Drop Down 12")

    With myDrop
        If .ListIndex = 1 Then
         
       MsgBox "Nothing was selected"
 
       Else
      ActiveSheet.Range("A7:S98").Select
            ddValue = .List(.ListIndex)
           
             ActiveSheet.Range("A7").AutoFilter Field:=3, Criteria1:=ddValue
             
                ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
     
   End With
        End If
    End With

Did you manage to test this on your non-outlook mail system?

I can see that there is other work to do here, but I would like to be sure that we have a suitable mail method before looking at other code lines.
(as the whole thing may need to be re-written as the mail method changes).

No, it didn't work. There is an "email as pdf attachment" in excel. I tried that but it sends the entire workbook and not just the filtered data. My thought is if I can send the filtered data to a new workbook and email from there it should work. Perhaps have a button on the spreadsheet that says "Email Results" and it'll send filtered range to new workbook and attach to the email and delete the workbook. Suggestions welcome. Thanks!

waiting for the solution!