Dynamically create ordered list in outlook from Excel

Question

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>"

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>

You should include the mechanism that creates arr. Is arr an 1 dimensional array? — TinMan
– TinMan, Commented Nov 21, 2019 at 14:41
correct, it is a dynamic one dimensional array that is being stored in a collection. — Zack E
– Zack E, Commented Nov 21, 2019 at 14:47

Heslacher · Accepted Answer · 2019-12-09 17:32:49Z

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>"

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 = "<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. — Zack E
– Zack E, Commented Nov 21, 2019 at 16:22
Does SheetData.Range("Notes_For_Doc_Reviewer").Value have an extra comma in it? — TinMan
– TinMan, Commented Nov 21, 2019 at 16:28
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. — Ryan Wildry
– Ryan Wildry, Commented Nov 21, 2019 at 17:05
Thank you both for your assistance. I learned a lot with this one. — Zack E
– Zack E, Commented Nov 21, 2019 at 17:39

Stack Exchange Network

Dynamically create ordered list in outlook from Excel

1 Answer 1

Join Demo

Refactored Code

Edit:

You must log in to answer this question.

Hot Network Questions

Dynamically create ordered list in outlook from Excel

1 Answer 1

Join Demo

Refactored Code

Edit:

You must log in to answer this question.

Related

Hot Network Questions