A little help with looping please

288enzo

Well-known Member
Joined
Feb 8, 2009
Messages
717
Office Version
  1. 2016
Platform
  1. Windows
I would like to loop through last row starting at A2.

Here is what I have so far:

VBA Code:
Sub Mail_test()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    
    test_top = "<html><head><title></title></head><body>" _
        & "<style> table.main {border:2px solid black} " _
        & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
        & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
        & "<style> .href { font-size: 13px; font-family: calibri;}</style> " _
        & "<table class=mainwidth:600px""><tr>" _
        & "<center><spanfont-size:19px;"">" _
        & "TITLE<br></span></center><br>" _
        & "<spanfont-size:15px;"">" _
        & "Text" _
        & "<center><table class=""schedule""width:590px;""><tbodyfont-size:13px;"">" _
        & "<tr align=""center"";style=""color:#c00000;"">" _
        & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>" _

    test_bottom = "</tbody></table></center>" _
        & "Text</table></body></html>" & .HTMLBody
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To LR

    row = & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>" _

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
    
    .Display
        
    .HTMLBody = test_top & row (looping to LR in column A starting at A2) & test_bottom
    
    End With
        
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You need a matching

VBA Code:
Next x

to show the end of your For loop. My educated guess is it should go here. Also, do not create a new Outlook app every time through the loop. I also strongly encourage you to use indentation to show the structure of your code.

Rich (BB code):
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Set OutApp = CreateObject("Outlook.Application")
   
    For x = 2 To LR

    row = & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>" _

    Set OutMail = OutApp.CreateItem(0)
   
    With OutMail
   
    .Display
       
    .HTMLBody = test_top & row (looping to LR in column A starting at A2) & test_bottom
   
    End With
       
    Next x

    Set OutMail = Nothing
    Set OutApp = Nothing
 
Upvote 0
See if this is what you want ...

VBA Code:
Sub Mail_test()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    Dim test_top As String, test_bottom As String, Row As String
    
    test_top = "<html><head><title></title></head><body>" _
             & "<style> table.main {border:2px solid black} " _
             & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
             & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
             & "<style> .href { font-size: 13px; font-family: calibri;}</style> " _
             & "<table class=mainwidth:600px""><tr>" _
             & "<center><spanfont-size:19px;"">" _
             & "TITLE<br></span></center><br>" _
             & "<spanfont-size:15px;"">" _
             & "Text" _
             & "<center><table class=""schedule""width:590px;""><tbodyfont-size:13px;"">" _
             & "<tr align=""center"";style=""color:#c00000;"">" _
             & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>" _

    test_bottom = "</tbody></table></center>" _
                & "Text</table></body></html>"

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Set OutApp = CreateObject("Outlook.Application")
    
    For x = 2 To LR

        Row = "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>"
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .Display
            .HTMLbody = test_top & Row & "(looping to LR in column A starting at A2)" & test_bottom & .HTMLbody
        End With
    
    Next x

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Upvote 0
You need a matching

VBA Code:
Next x

to show the end of your For loop. My educated guess is it should go here. Also, do not create a new Outlook app every time through the loop. I also strongly encourage you to use indentation to show the structure of your code.

Rich (BB code):
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Set OutApp = CreateObject("Outlook.Application")
 
    For x = 2 To LR

    row = & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>" _

    Set OutMail = OutApp.CreateItem(0)
 
    With OutMail
 
    .Display
     
    .HTMLBody = test_top & row (looping to LR in column A starting at A2) & test_bottom
 
    End With
     
    Next x

    Set OutMail = Nothing
    Set OutApp = Nothing
This created 3 separate emails with row = & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>" _.

What I'm trying to do is send one email with three rows (data in A2, A3, A4).

The final output should look like this
1638898784331.png
 
Upvote 0
See if this is what you want ...

VBA Code:
Sub Mail_test()
   
    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    Dim test_top As String, test_bottom As String, Row As String
   
    test_top = "<html><head><title></title></head><body>" _
             & "<style> table.main {border:2px solid black} " _
             & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
             & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
             & "<style> .href { font-size: 13px; font-family: calibri;}</style> " _
             & "<table class=mainwidth:600px""><tr>" _
             & "<center><spanfont-size:19px;"">" _
             & "TITLE<br></span></center><br>" _
             & "<spanfont-size:15px;"">" _
             & "Text" _
             & "<center><table class=""schedule""width:590px;""><tbodyfont-size:13px;"">" _
             & "<tr align=""center"";style=""color:#c00000;"">" _
             & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>" _

    test_bottom = "</tbody></table></center>" _
                & "Text</table></body></html>"

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Set OutApp = CreateObject("Outlook.Application")
   
    For x = 2 To LR

        Row = "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>"
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .Display
            .HTMLbody = test_top & Row & "(looping to LR in column A starting at A2)" & test_bottom & .HTMLbody
        End With
   
    Next x

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
I believe this will do the same thing, create separate emails for each row and not one email with all rows.

On a side note, why did you add each "As String". I've never seen a difference with or without. Newbie question?
 
Upvote 0
I see, then it would be something like the code below.

On a side note, why did you add each "As String". I've never seen a difference with or without. Newbie question?

I use the features and improvement settings that the VBE offers me as much as possible. The most eye-catching to me are "option explicit", "intellisense" (Ctrl+J) and "auto complete" (Ctrl+Space).
The first will force the coder to pre-declare all variables used, otherwise the VBE will not compile the code (I prefer compile-time errors over run-time errors ...) and I prefer my declarations to be explicit, so instead of
Rich (BB code):
Dim MyText
I use
VBA Code:
Dim MyText As String
so the MyText variable starts as a String vartype and stays a String vartype during its lifetime, and instead of
Rich (BB code):
Dim Sht
I use
VBA Code:
Dim Sht as Worksheet
for the same reason and for the benefit of using intellisense as well.
You might want to take a look at this:



VBA Code:
Sub Mail_test()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    Dim test_top As String, test_bottom As String, Row As String

    test_top = "<html><head><title></title></head><body>" _
             & "<style> table.main {border:2px solid black} " _
             & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
             & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
             & "<style> .href { font-size: 13px; font-family: calibri;}</style> " _
             & "<table class=mainwidth:600px""><tr>" _
             & "<center><spanfont-size:19px;"">" _
             & "TITLE<br></span></center><br>" _
             & "<spanfont-size:15px;"">" _
             & "Text" _
             & "<center><table class=""schedule""width:590px;""><tbodyfont-size:13px;"">" _
             & "<tr align=""center"";style=""color:#c00000;"">" _
             & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>" _

    test_bottom = "</tbody></table></center>Text</table></body></html>"

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To LR
        Row = Row & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>"
    Next x

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .Display
        .HTMLbody = test_top & Row & test_bottom & .HTMLbody
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Upvote 0
Solution
On a side note, why did you add each "As String". I've never seen a difference with or without. Newbie question?
Aside from the reasons given, in general you can prevent a lot of bugs with strong typing. Strong typing means that each variable has a specific data type. If you try to assign a variable to a variable with an incompatible type, it was give you an error, which is better than just assigning whatever to whatever and getting bugs.

Using Variant is weak typing, because a Variant variable changes type depending on what you assign to it--and you can assign anything to it. If you do not have Option Explicit, and do not declare variables, they default to Variant. And if you declare it without a type, it defaults to Variant.

One problem (IMHO) with VBA is that even if you use typed variables, VBA will try to resolve type conflicts with silent conversions. For example, if you have

Rich (BB code):
Dim i As Long
Dim s As String

i = "1234" ' String will be converted and i will be 1234
s = 1234 ' number will be converted and s will be "1234"

i = "x" ' type mismatch runtime error! But if i is Variant you will not get any error.
 
Upvote 0
I see, then it would be something like the code below.



I use the features and improvement settings that the VBE offers me as much as possible. The most eye-catching to me are "option explicit", "intellisense" (Ctrl+J) and "auto complete" (Ctrl+Space).
The first will force the coder to pre-declare all variables used, otherwise the VBE will not compile the code (I prefer compile-time errors over run-time errors ...) and I prefer my declarations to be explicit, so instead of
Rich (BB code):
Dim MyText
I use
VBA Code:
Dim MyText As String
so the MyText variable starts as a String vartype and stays a String vartype during its lifetime, and instead of
Rich (BB code):
Dim Sht
I use
VBA Code:
Dim Sht as Worksheet
for the same reason and for the benefit of using intellisense as well.
You might want to take a look at this:



VBA Code:
Sub Mail_test()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    Dim test_top As String, test_bottom As String, Row As String

    test_top = "<html><head><title></title></head><body>" _
             & "<style> table.main {border:2px solid black} " _
             & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
             & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
             & "<style> .href { font-size: 13px; font-family: calibri;}</style> " _
             & "<table class=mainwidth:600px""><tr>" _
             & "<center><spanfont-size:19px;"">" _
             & "TITLE<br></span></center><br>" _
             & "<spanfont-size:15px;"">" _
             & "Text" _
             & "<center><table class=""schedule""width:590px;""><tbodyfont-size:13px;"">" _
             & "<tr align=""center"";style=""color:#c00000;"">" _
             & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>" _

    test_bottom = "</tbody></table></center>Text</table></body></html>"

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To LR
        Row = Row & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>"
    Next x

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .Display
        .HTMLbody = test_top & Row & test_bottom & .HTMLbody
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Thank you for taking the time to explain by "on a side note", as well as help me on this.

The good news is it worked, the not so good news is all the formatting was lost.

This is the output.

1638925545833.png
 
Upvote 0
Thank you for taking the time to explain by "on a side note", as well as help me on this.

The good news is it worked, the not so good news is all the formatting was lost.

This is the output.

View attachment 52883
I'm thinking I'll need to add formatting to each row. I'll play around with that in the morning.
 
Upvote 0
See if this is what you want ...

VBA Code:
Sub Mail_test()
   
    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    Dim test_top As String, test_bottom As String, Row As String
   
    test_top = "<html><head><title></title></head><body>" _
             & "<style> table.main {border:2px solid black} " _
             & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
             & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
             & "<style> .href { font-size: 13px; font-family: calibri;}</style> " _
             & "<table class=mainwidth:600px""><tr>" _
             & "<center><spanfont-size:19px;"">" _
             & "TITLE<br></span></center><br>" _
             & "<spanfont-size:15px;"">" _
             & "Text" _
             & "<center><table class=""schedule""width:590px;""><tbodyfont-size:13px;"">" _
             & "<tr align=""center"";style=""color:#c00000;"">" _
             & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>" _

    test_bottom = "</tbody></table></center>" _
                & "Text</table></body></html>"

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Set OutApp = CreateObject("Outlook.Application")
   
    For x = 2 To LR

        Row = "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>"
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .Display
            .HTMLbody = test_top & Row & "(looping to LR in column A starting at A2)" & test_bottom & .HTMLbody
        End With
   
    Next x

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Actually, it worked perfectly. For some strange reason when you pasted it into the board, some of the spacing got lost. I had copied your example, once I made the changes to the spacing it worked. Very cool, thank you for the help.

VBA Code:
Sub Mail_test()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim LR As Long
    Dim x As Long
    Dim test_top As String, test_bottom As String, Row As String

    test_top = "<html><head><title></title></head><body>" _
        & "<style> table.main {border:2px solid black} " _
        & "<style> table.schedule {border-collapse:collapse;border:1px solid black;} " _
        & "<style> table.schedule td {border-collapse:collapse;border:1px solid black;} " _
        & "<style> .href { font-size: 13px; font-family: Audi Type;}</style> " _
        & "<table class=main style=""width:600px""><tr>" _
        & "<center><span style=""font-size:19px;"">" _
        & "TITLE<br></span></center><br>" _
        & "<span style=""font-size:15px;"">" _
        & "Text" _
        & "<center><table class=""schedule"" style=""width:590px;""><tbody style=""font-size:13px;"">" _
        & "<tr align=""center"";style=""color:#c00000;"">" _
        & "<td>header</td><td>header</td><td>header</td><td>header</td></tr>"
        
    test_bottom = "</tbody></table></center>Text</table></body></html>"

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To LR
        Row = Row & "<tr><td>" & Range("A" & x) & "</td><td>text</td><td>text</td><td align=""center"">text</td></tr>"
    Next x

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .Display
        .HTMLbody = test_top & Row & test_bottom & .HTMLbody
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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