• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filtering a huge list down then send e-mail to only those people that are shown

MagicTK

New Member
I have a huge list that has name, number, dept, e-mail, and many columns with different projects, etc. Each person has marking in various columns, depending on which one they are on.

I am looking for a method to allow me to filter this huge list down to a smaller specific list (like for people only on a particular project or system, or just managers, etc), then at the top of the column that has everyone's e-mails I want to have a button to send an email to just those people that I filtered down to.

The file in this link does almost exactly what I want, except for being able to send an e-mail to only the rows that are actively shown after being filtered. I tried adding a bunch of e-mails, then in the 4th column, put a mark on a few rows, then filtered to just those rows. When I click the e-mail button, it prepares an e-mail for the entire list, not the filtered list.
https://chandoo.org/wp/send-mails-using-excel-vba-and-outlook/

For my purpose, I also deleted the code that prepares sending a file. I do not need that functionality.

Any help is appreciated.

Thanks,
Tom
 

Marc L

Excel Ninja
A beginner level idea : you can copy the filtered data - or directly use an advanced filter - to a temp worksheet​
then send e-mails from this temp worksheet.​
Or obviously for each row just check if it is not hidden …​
 

MagicTK

New Member
I was able to find a solution. It's based on the file in the link above, but there's a loop to look for and ignore the hidden rows when I filter to whatever criteria I need. I created a dummy list of e-mails, no real contact info just for testing it. Of course, you would use your actual info or contact lists.

When you filter (or not filter) something, then click the E-Mail button, it will open Outlook and get an e-mail ready. I deleted the Subject line code and the Body message code from the original file. Of course, you could add those back in. I just didn't need them. The main purpose of this was to send an e-mail just to a filtered list of people.

I am not a macro expert by any means, so I might not be able to help you with much, if you are trying to modify the code to your purposes.

Hope it helps.

Code:
=====================================

Option Explicit

Sub ExportEmail()


    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim currentWB As Workbook, newWB As Workbook
    Dim strEmailTo As String, strEmailCC As String, strEmailBCC As String, strDistroList As String
    Dim r_cnt1, col_email1, col_blankcheck1 As Variant
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Set currentWB = ActiveWorkbook
   
   

    '******************************Preparing Distribution List *********************************

    currentWB.Activate

   
    strEmailTo = ""
   
    ' Sets the starting location of the loop checking for only visible rows
    r_cnt1 = 6
    col_email1 = 3
    col_blankcheck1 = 4

    Do Until Cells(r_cnt1, col_blankcheck1).Value = ""
   
        'If not hidden, it looks at only the visible rows
        If Not Cells(r_cnt1, col_email1).Rows.Hidden Then
        
            strDistroList = Cells(r_cnt1, col_email1).Value
            strEmailTo = strEmailTo & strDistroList & "; "
       
        End If
       
        r_cnt1 = r_cnt1 + 1
       
    Loop
   
    Range("A1").Select
   
    '******************************Preparing Email*********************************
   
    Set olApp = New Outlook.Application
       Dim olNs As Outlook.Namespace
       Set olNs = olApp.GetNamespace("MAPI")
       olNs.Logon
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = strEmailTo
    olMail.Display
   
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
End Sub
 

Attachments

Last edited by a moderator:

MagicTK

New Member
I was able to find a solution. It's based on the file in the link above, but there's a loop to look for and ignore the hidden rows when I filter to whatever criteria I need. I created a dummy list of e-mails, no real contact info just for testing it. Of course, you would use your actual info or contact lists.

When you filter (or not filter) something, then click the E-Mail button, it will open Outlook and get an e-mail ready. I deleted the Subject line code and the Body message code from the original file. Of course, you could add those back in. I just didn't need them. The main purpose of this was to send an e-mail just to a filtered list of people.

I am not a macro expert by any means, so I might not be able to help you with much, if you are trying to modify the code to your purposes.

Hope it helps.
I forgot to mention, you will need to turn on the Microsoft Office 16.0 Object Library, the Microsoft Scripting Runtime, and Microsoft Outlook 16.0 Object Library. In the file on the second tab, I put a screenshot in the comment that shows the libraries. Or you can just use the file I provided, and that will already have those libraries checked.
 
Top