Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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):
https://www.youtube.com/watch?v=URF3ikW0Svc

🧐 Get My Full Done-For-You Macro Library Here: https://bit.ly/UDVL-YTDesc 🔥GET THIS +300 OF MY BEST TEMPLATES HERE► https://bit.ly/300WKBK-Desc 🤖BRING AI INTO EXCEL WITH THIS INCREDIBLE ADD-IN► https://bit.ly/AIToolpackYT-Desc ▶️ GET ACCESS TO OVER 100 UPDATED TRAININGS, WORKBOOKS & PDF CODEBO...
Awesome, thanks for the direction. I will give it a try :thumbs_up:

@Jss88 

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!