Use VBA to enter a varying range of bullet points into body of email

[mjohnston0209]

I have used VBA to send out standardized emails to multiple people. However, I have yet to solve how to enter bullet points into the body of the email where the list of bullets could vary in length from one day to the next.

Below, is the written email I currently have that doesn't include bullet points. I would like to add the bullets above the word thanks. This would be easy if the bullets were always a predetermined amount. However, one day could be two and the next four and so on.

Mrs. Silkey,

The following students were absent from today's math groups:

Thanks,

Below is the code I created to generate the email:

Sub Send_Absent_Emails_Complete()

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, strbody As String
Set Mail_Object = CreateObject("Outlook.Application")

i = 2
    
    strbody = "<p style='font-family:calibri;font-size:14.5'>" & Cells(3, i) & "," & "<br>" _
        & "<br>" _
        & "The following students were absent from today's math group:" & "<br>" _
        & "<br>" _
        & "Thanks,</p>"
        
    With Mail_Object.CreateItem(o)
        .Subject = Cells(5, i)
        .To = Cells(4, i)
        .HTMLBody = .HTMLBody & strbody
        .Display
        .Send
    End With

    MsgBox "E-mails successfully sent", 64
    Application.DisplayAlerts = False

End Sub

The list of absent students will start in cell B6 and extend downwards for a varying length. I have also attached a screenshot to show what cells components of the email are captured in.

My goal is to create an email for every applicable teacher and loop through each one so there is only one macro. I can do this but just need to solve the bullet point issue.

Any help would be greatly appreciated!

 

[daphneti]

@daphneti - Welcome to the MrExcel Message Board.

Please start a new thread by posting your own question on the forum since it is actually a new question on the same topic. That way, helpers will know your current code and more helpers will be able to notice your question so that you can get faster help.


Although we need to see your own code to help with the margin question as I explained above so that requires you to create a new thread, this part could also help the others who are looking at this thread, so I will try to answer it here.
You should wrap the font name with quotes in the style attribute due to font name contains space:

strbody = "<p style='font-family:""calibri light"";font-size:14.5'>" & Cells(3, i) & "," & "<br>" _

Thanks for the swift reply and the font style help, it did the trick!
I have created a new thread on the margin question: VBA remove margin before bulletpoint/unordered list in email