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 = "ZackE@nothing.com"
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>" & _

Locals Window for Items in Collection

EDIT: Output of getCheckListHTML per request

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

OUtlook Body Output

  • \$\begingroup\$ You should include the mechanism that creates arr. Is arr an 1 dimensional array? \$\endgroup\$ – TinMan Nov 21 '19 at 14:41
  • \$\begingroup\$ correct, it is a dynamic one dimensional array that is being stored in a collection. \$\endgroup\$ – Zack E Nov 21 '19 at 14:47

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)

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


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

  • \$\begingroup\$ 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. \$\endgroup\$ – Zack E Nov 21 '19 at 16:22
  • \$\begingroup\$ Does SheetData.Range("Notes_For_Doc_Reviewer").Value have an extra comma in it? \$\endgroup\$ – TinMan Nov 21 '19 at 16:28
  • \$\begingroup\$ No, it only has 4 commas in it. \$\endgroup\$ – Zack E Nov 21 '19 at 16:30
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Ryan Wildry Nov 21 '19 at 17:05
  • 1
    \$\begingroup\$ Thank you both for your assistance. I learned a lot with this one. \$\endgroup\$ – Zack E Nov 21 '19 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.