For Loop to create a dynamic outlook email body that will have lines dependent on trades for a specific date

Question

I am generating an email that shows trades for the day in the body of an email. Currently the only way I can do this is with an If then statement for specific numbers of trades. If we traded 10, I need an if then statement with 10 as the variable criteria, but if I only have 9, then I get an error. I want a dynamic method instead. I can do a For Loop that will list all the trades in debug.print, but in the email, it each trade overwrites the prior trade and I show only one line. I also need an intro like "Hi, today today's trades are: " followed by each trade listed below line by line.

I tried this and it works but only if I have the right number of trades and a variable that matches it. For example, if I had 23 trades, I need an If statement with 23 as a variable value in this case m. I used Arrays as VBA does not let me create a list. Unfortunately, I cannot pull the whole array, I need to pull line by line. If I could pull the array, I could just have a simple loop.

 If m = 23 Then
        .Body = "Hi Chris," & vbLf & vbLf & "The following trade(s) was completed today:" & vbLf & vbLf & ArrayValues(0) & vbLf & ArrayValues(1) & vbLf & ArrayValues(2) & vbLf & ArrayValues(3) & vbLf & ArrayValues(4) & vbLf & ArrayValues(5) & vbLf & ArrayValues(6) & vbLf & ArrayValues(7) & vbLf & ArrayValues(8) & vbLf & ArrayValues(9) & vbLf & ArrayValues(10) & vbLf & ArrayValues(11) & vbLf & ArrayValues(12) & vbLf & ArrayValues(13) & vbLf & ArrayValues(14) & vbLf & ArrayValues(15) & vbLf & ArrayValues(16) & vbLf & ArrayValues(17) & vbLf & ArrayValues(18) & vbLf & ArrayValues(19) & vbLf & ArrayValues(20) & vbLf & ArrayValues(21) & vbLf & ArrayValues(22) & vbLf & vbLf & "Thanks"
    
End if

I want to use something like:

For b = 1 To LastRow
        If Trades.Range("H" & b) = TDate Then
            Debug.Print (Range("B" & b) & "   " & Range("C" & b) & "    " & Range("D" & b) & "   " & Range("F" & b) & "   " & Range("G" & b))
            
        End If
    Next b

This way it does not matter how may trades I have, one formula would over it all. Each Range has a trade characteristic.

If I do a debug.Print in the immediate window I get a list just like I want, but in the email, each line overwrites the prior trade.

I am a rookie at this and appreciate any help. Thanks

[Edit](https://stackoverflow.com/posts/74749978/edit) the post to put in code that reproduces the behaviour described. [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). Include sample data as text and expected result. — niton, Dec 10 '22 at 12:59

score 0 · Answer 1 · answered Dec 10 '22 at 21:50

Sounds like you need to build a string which can be assigned to the message body. For such tasks you can use String Functions available in VBA where you can prepare the correct string and then assign to the message body.

 If m = 23 Then
        .Body = "Hi Chris," & vbLf & vbLf & "The following trade(s) was completed today:" & vbLf & vbLf & ArrayValues(0) & vbLf & ArrayValues(1) & vbLf & ArrayValues(2) & vbLf & ArrayValues(3) & vbLf & ArrayValues(4) & vbLf & ArrayValues(5) & vbLf & ArrayValues(6) & vbLf & ArrayValues(7) & vbLf & ArrayValues(8) & vbLf & ArrayValues(9) & vbLf & ArrayValues(10) & vbLf & ArrayValues(11) & vbLf & ArrayValues(12) & vbLf & ArrayValues(13) & vbLf & ArrayValues(14) & vbLf & ArrayValues(15) & vbLf & ArrayValues(16) & vbLf & ArrayValues(17) & vbLf & ArrayValues(18) & vbLf & ArrayValues(19) & vbLf & ArrayValues(20) & vbLf & ArrayValues(21) & vbLf & ArrayValues(22) & vbLf & vbLf & "Thanks"
    
End if

Your strategical mistake is that you are trying to set up the Body property for a specific case. Instead, consider concatenating the string with the required piece of data (another string) which makes sense for a particular case. And only when you are done adding all the bits to the result string, you can assign it to the message body.

Be aware, the Body property is a plain text string and doesn't deliver any formatting. Instead, I'd suggest using the HTMLBody property instead. The HTMLBody property should be an HTML syntax string. Setting the HTMLBody property will always update the Body property immediately.

Thanks Eugene. If the string can be dynamic such that if there are 10 trades done the code will produce the 10 trades, but the next day if 13 trades are done, then the code would dynamically have to accommodate that scenario without having to modify the code. I appreciate your suggestion. Happy holidays! — Tom, Dec 11 '22 at 20:30
The string works so if I have 35 trades, the loop works great. The problem I know have is, I cannot include an intro such as "Hello Trader" and then the trades followed by "Thanks, Tom". It seems if I use a loop with the HTML body I cannot have an intro and end, but rather just the trades. It still is better than what I have, I would just have to write the intro and sign off each time. Is there a way to incorporate a greeting as well as a closing outside of the loop? — Tom, Jan 12 '23 at 05:04

For Loop to create a dynamic outlook email body that will have lines dependent on trades for a specific date

1 Answers1