Using VBA to find rows that contain specific information in a column and automatically emailing it?

New Contributor

Have a sheet with 30k rows of customer requests and trying to figure out how to use VBA to find requests that have a specific account number in column G and then emailing each corresponding row containing that account number to x amount of recipients... I am lost. The farthest I have gotten is getting that data copied to another sheet. 


Information about the data: Each entire row is just one clients information, the account number is always and only in column G and clients will have data in multiple rows as separate requests. Any help would be appreciated... I am out of my league, but slowly learning. Searched for hours on this one and the closest I was able to find was this video, but cannot figure out how to modify it for my data.


Filter data, save and send results as an email with VBA in Excel - YouTube

4 Replies

@Jss88 I recommend you check out this video tutorial. They have a great template for doing a mail merge but using excel and therefore able to use filters in excel to determine the mail merge criteria (or at least that is what I seem to recall):

Awesome, thanks for the direction. I will give it a try

Sounds like you want to merge the data into the email message body rather than into a document.

Below is a link to a video demo of a tool (Visual CUT) that caters to your use case. It can burst Excel data and email each row, each group of rows, or all rows as HTML table or cell tokens that you can place  in the email message.


Disclosure: I'm the developer of that tool.

It requires no coding. In Excel, you simply add a True/False expression in a column called ' Include' to indicate what rows to include. You can schedule the process or trigger it manually.

Demo of interactive & scheduled emailing of Excel data with options to group and burst (each row or each group becomes a separate email). Incorporate dynamic data from the workbook as tokens. Generate HTML tables for embedding in the email message. Use Excel formula to exclude certain rows ...


If I may add additionally, you can use VBA to find rows that contain specific information in a column and automatically email it.

Here’s an example code that you can use:

Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strto As String
    Dim strcc As String
    Dim strbcc As String
    Dim strsub As String
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    For Each cell In Range("G1:G" & Cells(Rows.Count, "G").End(xlUp).Row)
        If cell.Value = "specific account number" Then
            Set OutMail = OutApp.CreateItem(0)
            strto = "email address removed for privacy reasons"
            strcc = "email address removed for privacy reasons"
            strbcc = ""
            strsub = "Subject"
            strbody = "Body"
            On Error Resume Next
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody & vbNewLine & vbNewLine & _
                        "Row: " & cell.Row & vbNewLine & _
                        "Account Number: " & cell.Value & vbNewLine & _
                        "Client Information: " & cell.EntireRow.Value
                .Send   'or use .Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell
    Set OutApp = Nothing
End Sub

In this code, you can replace "specific account number" with the account number that you want to find. You can also replace "email address removed for privacy reasons" and "email address removed for privacy reasons" with the email addresses that you want to send the email to. You can modify the subject and body of the email as well.

This code loops through all cells in column G and checks if the value matches the specific account number. If it does, it creates an email with the row number, account number, and client information of that row. It then sends the email to the specified email addresses.


I hope this helps!