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

Copper 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

3 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 :thumbs_up:


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!