Dynamically create ordered list in outlook from Excel

Question 1

I have working code for this project, but I am now in need of some help to create a dynamic ordered list within Outlook from Excel.

I am trying to format comments that a user will enter into a UserForm in Excel and pass those comments into an ordered list via a collection; the issue I run into is that the number of comments can vary and I need to account for that when it comes to setting up the ordered list. I know I cannot create dynamic variables, so right now I am stuck on how to loop through the collection and adjust the size of the ordered list dynamically.

Below is the code that is working outside of the fact that the ordered list is not dynamic. Im wondering if maybe passing that Collection into a Class Module would be beneficial, but I am not really proficient with those yet. Please note that Option Explicit is defined in this module.

Dim strComment As String, commentColl As Collection
Dim arr As Variant
strComment = SheetData.Range("Notes_For_Doc_Reviewer")
arr = Split(strComment, ",")
Set commentColl = New Collection
Dim a
For Each a In arr
 commentColl.Add Trim(a)
Next a
'Dim x As Variant, i As Long
'For Each x In commentColl
' Debug.Print CStr(x)
'Next x
Count = commentColl.Count
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ZackEmail = "[email protected]"
currDir = MLAChecklist.path
hyperlink = "<a href=""" & Replace(currDir, " ", "%20") & """>" & currDir & "</a>"
strBody = "<BODY style=font-size:11pt;font-family:Calibri>Hello Zack," & _
 "<p>Please complete the closing document review for the following file:" & "&nbsp" & "&nbsp" & hyperlink & "<br><br>" & vbCrLf & _
 "Items to make note of in the file." & _
 "<ol>" & _
 "<li>" & commentColl(1) & "</li>" & _
 "<li>" & commentColl(2) & "</li>" & _
 "<li>" & commentColl(3) & "</li>" & _
 "<li>" & commentColl(4) & "</li>" & _
 "<li>" & commentColl(5) & "</li>" & _
 "</ol>" & _
 "</p></BODY>"

Locals Window for Items in Collection

EDIT: Output of getCheckListHTML per request

<html>
<body style=font-size:11pt;font-family:Calibri>
Hello Zack,
<p>Please complete the closing document review for the following file:
&nbsp&nbsp
<a href="Z:\Projects\Excel%20Projects\MLA%20UserForm%20Checklist">Z:\Projects\Excel Projects\MLA UserForm Checklist</a>
<br><br>
Items to make note of in the file.
<ol>
<li>Test run</li>
<li>items list</li>
<li>run through things</li>
<li>test number</li>
<li>blah blah<li>
</ol>
<br>
Thank You,
</p>
</body>
</html>

OUtlook Body Output

Question 2

You should include the mechanism that creates arr. Is arr an 1 dimensional array?

Question 3

correct, it is a dynamic one dimensional array that is being stored in a collection.

Question 4

There is no reason to convert arr to a collection. Join() can quickly build a tag list from a 1 Dimensional array. The trick is to have Join() insert a closing tag + open tag between each element.

"<li>" & Join(arr, "</li><li>") & "</li>"

Join Demo

Dim arr As Variant
Dim n As Long
ReDim arr(6)
For n = 1 To 7
 arr(n - 1) = WeekdayName(n)
Next
Debug.Print "<li>" & Join(arr, "</li><li>") & "</li>"
Debug.Print "<li>" & Join(arr, "</li>" & vbNewLine & "<li>") & "</li>"

Immediate Window Results

You could also avoid creating the array in the first using the same tring to replace the commas with a close tag + an open tag:

Immediate Window Results

Your code is building a hyperlink, filling an ordered list, and creating the message htmlBody and will probably do several other tasks before it is complete. There is no way to test any single process without running the entire code. The fewer tasks that a method performs the easier it is to modify and debug.

Immediate Window Results

Notice how easy it was to test my Refactored Code in the immediate window.

Refactored Code

Note: I find that using an ArrayList is the easiest way to create and modify dynamic html.

Function getCheckListItems() As String
 getCheckListItems = "<li>" & Replace(SheetData.Range("Notes_For_Doc_Reviewer").Value, ",", "</li>" & vbNewLine & "<li>") & "</li>"
End Function
Function getChecklistHTML(HyperlinkTag As String, CheckListItems As String) As String
 Const Delimiter As String = vbNewLine
 Dim list As Object
 Set list = CreateObject("System.Collections.ArrayList")
 list.Add "<html>"
 list.Add "<body style=font-size:11pt;font-family:Calibri>"
 list.Add "Hello Zack"
 list.Add "<p>Please complete the closing document review for the following file:"
 list.Add "&nbsp&nbsp"
 list.Add HyperlinkTag
 list.Add "<br><br>"
 list.Add "Items to make note of in the file."
 list.Add "<ol>"
 list.Add CheckListItems
 list.Add "</ol>"
 list.Add "</body>"
 list.Add "</html>"
 getChecklistHTML = Join(list.ToArray, Delimiter)
End Function
Function getCurrentPathHyperlink(Wb As Workbook)
 Const DefaultLink As String = "<a href='@currDir'>@currDir</a>"
 Dim currDir As String
 currDir = Replace(Wb.Path, " ", "%20")
 getCurrentPathHyperlink = Replace(DefaultLink, "@currDir", currDir)
End Function

Edit:

My original post was using an open tag instead of a closing tag in getCheckListItems(). Many thanks to Ryan Wildry!

Question 5

Thank you. I still have a lot to learn about programming in VBA, but I really appreciate your time. This works amazingly, but it does add an extra <li></li> in the body of the email that is blank. Lets say I only have 5 items to list it will list a blank 6th item.

Question 6

Does SheetData.Range("Notes_For_Doc_Reviewer").Value have an extra comma in it?

Question 7

No, it only has 4 commas in it.

Question 8

getCurrentPathHyperlink should have a strongly typed return value. Also, each function should have an explicit scope too. Also, depending on the size of the HTML you need to create, writing it all in code may become harder to maintain. If the HTML grows too much larger, I find it easier to keep an HTML fragment saved to named range someplace with placeholder values e.g. {0} or similar, then simply replace in the string. Still, a good overall approach. +1 for using Join.

Question 9

Thank you both for your assistance. I learned a lot with this one.

TinMan TinMan 4,3181 gold badge13 silver badges25 bronze badges · Accepted Answer · 2019-11-21 15:53:33Z

There is no reason to convert arr to a collection. Join() can quickly build a tag list from a 1 Dimensional array. The trick is to have Join() insert a closing tag + open tag between each element.

"<li>" & Join(arr, "</li><li>") & "</li>"

Join Demo

Dim arr As Variant
Dim n As Long
ReDim arr(6)
For n = 1 To 7
 arr(n - 1) = WeekdayName(n)
Next
Debug.Print "<li>" & Join(arr, "</li><li>") & "</li>"
Debug.Print "<li>" & Join(arr, "</li>" & vbNewLine & "<li>") & "</li>"

Immediate Window Results

You could also avoid creating the array in the first using the same tring to replace the commas with a close tag + an open tag:

Immediate Window Results

Your code is building a hyperlink, filling an ordered list, and creating the message htmlBody and will probably do several other tasks before it is complete. There is no way to test any single process without running the entire code. The fewer tasks that a method performs the easier it is to modify and debug.

Immediate Window Results

Notice how easy it was to test my Refactored Code in the immediate window.

Refactored Code

Note: I find that using an ArrayList is the easiest way to create and modify dynamic html.

Function getCheckListItems() As String
 getCheckListItems = "<li>" & Replace(SheetData.Range("Notes_For_Doc_Reviewer").Value, ",", "</li>" & vbNewLine & "<li>") & "</li>"
End Function
Function getChecklistHTML(HyperlinkTag As String, CheckListItems As String) As String
 Const Delimiter As String = vbNewLine
 Dim list As Object
 Set list = CreateObject("System.Collections.ArrayList")
 list.Add "<html>"
 list.Add "<body style=font-size:11pt;font-family:Calibri>"
 list.Add "Hello Zack"
 list.Add "<p>Please complete the closing document review for the following file:"
 list.Add "&nbsp&nbsp"
 list.Add HyperlinkTag
 list.Add "<br><br>"
 list.Add "Items to make note of in the file."
 list.Add "<ol>"
 list.Add CheckListItems
 list.Add "</ol>"
 list.Add "</body>"
 list.Add "</html>"
 getChecklistHTML = Join(list.ToArray, Delimiter)
End Function
Function getCurrentPathHyperlink(Wb As Workbook)
 Const DefaultLink As String = "<a href='@currDir'>@currDir</a>"
 Dim currDir As String
 currDir = Replace(Wb.Path, " ", "%20")
 getCurrentPathHyperlink = Replace(DefaultLink, "@currDir", currDir)
End Function

Edit:

My original post was using an open tag instead of a closing tag in getCheckListItems(). Many thanks to Ryan Wildry!

Thank you. I still have a lot to learn about programming in VBA, but I really appreciate your time. This works amazingly, but it does add an extra <li></li> in the body of the email that is blank. Lets say I only have 5 items to list it will list a blank 6th item.
Does SheetData.Range("Notes_For_Doc_Reviewer").Value have an extra comma in it?
getCurrentPathHyperlink should have a strongly typed return value. Also, each function should have an explicit scope too. Also, depending on the size of the HTML you need to create, writing it all in code may become harder to maintain. If the HTML grows too much larger, I find it easier to keep an HTML fragment saved to named range someplace with placeholder values e.g. {0} or similar, then simply replace in the string. Still, a good overall approach. +1 for using Join.
Thank you both for your assistance. I learned a lot with this one.

Stack Exchange Network

Dynamically create ordered list in outlook from Excel

1 Answer 1

Join Demo

Refactored Code

Edit:

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

Dynamically create ordered list in outlook from Excel

1 Answer 1

Join Demo

Refactored Code

Edit:

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions