Looping through each row in Excel table and accessing data from each column

MiniMacros

New Member
Joined
Jul 2, 2010
Messages
7
Hi all - apologies as this is something that should be pretty simple, but I have unfortunately not been able to find the solution with a search so I must be using the wrong verbiage.

I have already completed a macro that sends an email based on 3 inputs ("To" email field, subject line, and body of email). I separately have a table I am creating that will have contain these 3 variables (and others) in each column, with multiple different entries in each row. The goal is being able to send multiple unique emails at once by going through each row using the previously mentioned Macro. For example Column A says "Organization Title", Column B is the "To" field, Column C is the "Subject Line" and Column D is the "Body". How do I create a macro that will loop through each row in the table and let me grab the text strings out of all the Columns to pass onto my other macro? I already feel comfortable passing the 3 variables to a different Sub, just need the code to iterate through each row and get the 3 variables out of the data table into string variables in VBA.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Figured it out, but pasting solution for reference

VBA Code:
Sub Test_SendMailToAll()
    
    Dim CRMTable As Range
    Dim EmailTo As String
    Dim EmailGreeting As String
    
    Set CRMTable = Worksheets("CRM Master Sheet").Range("b3", Worksheets("CRM Master Sheet").Range("b3").End(xlToRight).End(xlDown))
    
        For Each Row In CRMTable.Rows
            EmailTo = Row.Cells(1, 3).Value
            EmailGreeting = Row.Cells(1, 4).Value
            Mail_Outlook_With_Signature_Html_2 EmailTo, EmailGreeting
        Next Row
End Sub
 
Upvote 0
Apologies - 1 more question! I've pasted my latest code below which runs great. It goes through my table and if there is a 1 in the first column, will grab the values in the 3rd and 4th columns for my macro. Is it possible to have these columns selected based on the column header instead of a relative reference on the table? That would let me be able to move columns around in the table without messing up the macro.

VBA Code:
Sub Send_Email_To_All()
    
    Dim CRMTable As Range
    Dim EmailTo As String
    Dim EmailGreeting As String

    
    'Old code based on B3 the top left, now using defined table
    'Set B3 in both ranges to the top left of the CRM Table
    'Set CRMTable = Worksheets("CRM Master Sheet").Range("B3", Worksheets("CRM Master Sheet").Range("B3").End(xlToRight).End(xlDown))
    Set CRMTable = Worksheets("CRM Master Sheet").Range("Table1")
    
        For Each Row In CRMTable.Rows
            If Row.Cells(1, 1).Value = 1 Then
                EmailTo = Row.Cells(1, 3).Value
                EmailGreeting = Row.Cells(1, 4).Value
                Mail_Outlook_With_Signature_Html EmailTo, EmailGreeting
                EmailTo = ""
                EmailGreeting = ""
             End If
        Next Row
        
    Exit Sub

End Sub
 
Upvote 0

Forum statistics

Threads
1,215,022
Messages
6,122,716
Members
449,093
Latest member
Mnur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top