Dynamically create ordered list in outlook from Excel 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 = "&lt;a href=""" &amp; Replace(currDir, " ", "%20") &amp; """&gt;" &amp; currDir &amp; "&lt;/a&gt;"

strBody = "&lt;BODY style=font-size:11pt;font-family:Calibri&gt;Hello Zack," &amp; _
          "&lt;p&gt;Please complete the closing document review for the following file:" &amp; "&amp;nbsp" &amp; "&amp;nbsp" &amp; hyperlink &amp; "&lt;br&gt;&lt;br&gt;" &amp; vbCrLf &amp; _
          "Items to make note of in the file." &amp; _
          "&lt;ol&gt;" &amp; _
            "&lt;li&gt;" &amp; commentColl(1) &amp; "&lt;/li&gt;" &amp; _
            "&lt;li&gt;" &amp; commentColl(2) &amp; "&lt;/li&gt;" &amp; _
            "&lt;li&gt;" &amp; commentColl(3) &amp; "&lt;/li&gt;" &amp; _
            "&lt;li&gt;" &amp; commentColl(4) &amp; "&lt;/li&gt;" &amp; _
            "&lt;li&gt;" &amp; commentColl(5) &amp; "&lt;/li&gt;" &amp; _
          "&lt;/ol&gt;" &amp; _
          "&lt;/p&gt;&lt;/BODY&gt;"




EDIT:  Output of getCheckListHTML per request

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


"&lt;li&gt;" &amp; Join(arr, "&lt;/li&gt;&lt;li&gt;") &amp; "&lt;/li&gt;"



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 "&lt;li&gt;" &amp; Join(arr, "&lt;/li&gt;&lt;li&gt;") &amp; "&lt;/li&gt;"
Debug.Print "&lt;li&gt;" &amp; Join(arr, "&lt;/li&gt;" &amp; vbNewLine &amp; "&lt;li&gt;") &amp; "&lt;/li&gt;"




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:



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.



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 = "&lt;li&gt;" &amp; Replace(SheetData.Range("Notes_For_Doc_Reviewer").Value, ",", "&lt;/li&gt;" &amp; vbNewLine &amp; "&lt;li&gt;") &amp; "&lt;/li&gt;"
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 "&lt;html&gt;"
    list.Add "&lt;body style=font-size:11pt;font-family:Calibri&gt;"
    list.Add "Hello Zack"
    list.Add "&lt;p&gt;Please complete the closing document review for the following file:"
    list.Add "&amp;nbsp&amp;nbsp"
    list.Add HyperlinkTag
    list.Add "&lt;br&gt;&lt;br&gt;"
    list.Add "Items to make note of in the file."
    list.Add "&lt;ol&gt;"
    list.Add CheckListItems
    list.Add "&lt;/ol&gt;"
    list.Add "&lt;/body&gt;"
    list.Add "&lt;/html&gt;"

    getChecklistHTML = Join(list.ToArray, Delimiter)

End Function

Function getCurrentPathHyperlink(Wb As Workbook)
    Const DefaultLink As String = "&lt;a href='@currDir'&gt;@currDir&lt;/a&gt;"

    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!