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