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
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.
Your strategical mistake is that you are trying to set up the
Bodyproperty 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
Bodyproperty is a plain text string and doesn't deliver any formatting. Instead, I'd suggest using theHTMLBodyproperty instead. TheHTMLBodyproperty should be an HTML syntax string. Setting theHTMLBodyproperty will always update theBodyproperty immediately.