A little help with looping please

2

288enzo

Well-known Member
Joined
Feb 8, 2009
Messages
727
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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
6StringJazzer said:
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
Click to expand...
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
GWteB said:
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
Click to expand...
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.

288enzo said:
On a side note, why did you add each "As String". I've never seen a difference with or without. Newbie question?
Click to expand...

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:

www.excel-easy.com

Option Explicit in Excel VBA

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.
www.excel-easy.com www.excel-easy.com
www.excelcampus.com

Keyboard Shortcuts for the VBA Intellisense Drop-down Menu - Excel Campus

Learn 2 shortcuts to access the Intellisense menu for VBA and the VB Editor to autocomplete words, references, and variables.
www.excelcampus.com www.excelcampus.com


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
288enzo said:
On a side note, why did you add each "As String". I've never seen a difference with or without. Newbie question?
Click to expand...
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
GWteB said:
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:

www.excel-easy.com

Option Explicit in Excel VBA

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.
www.excel-easy.com www.excel-easy.com
www.excelcampus.com

Keyboard Shortcuts for the VBA Intellisense Drop-down Menu - Excel Campus

Learn 2 shortcuts to access the Intellisense menu for VBA and the VB Editor to autocomplete words, references, and variables.
www.excelcampus.com www.excelcampus.com


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
Click to expand...
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
288enzo said:
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
Click to expand...
I'm thinking I'll need to add formatting to each row. I'll play around with that in the morning.
 
Upvote 0
GWteB said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
569
smozgur
smozgur
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
304
Patriot2879
P
H
  • Solved
Macro to extract text to Body in Email and Table at end of Body
Replies
1
Views
596
howard
H
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
P
  • Question Question
Data not copying fully into ListBox2
Replies
0
Views
313
Patriot2879
P

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

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