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!

 

[smozgur]

You can use HTML List Object:

Sub Send_Absent_Emails_Complete()

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, strbody As String

Dim rng As Range
Dim cll As Range
Dim listItems 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>"
        
    ' Students range
    ' Set this range dynamically instead of
    ' defining a certain range as I did as sample below
    Set rng = Range("B6:B10")
    For Each cll In rng.Cells
        listItems = listItems & vbCrLf & "<li>" & cll.Value & "</li>"
    Next cll
        
    listItems = "<ul>" & vbCrLf & listItems & "</ul>"
    
    ' Insert listItems into the strBody
    strbody = strbody & vbCrLf & listItems
        
    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

 

[mjohnston0209]

You can use HTML List Object:

Sub Send_Absent_Emails_Complete()

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, strbody As String

Dim rng As Range
Dim cll As Range
Dim listItems 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>"
       
    ' Students range
    ' Set this range dynamically instead of
    ' defining a certain range as I did as sample below
    Set rng = Range("B6:B10")
    For Each cll In rng.Cells
        listItems = listItems & vbCrLf & "<li>" & cll.Value & "</li>"
    Next cll
       
    listItems = "<ul>" & vbCrLf & listItems & "</ul>"
   
    ' Insert listItems into the strBody
    strbody = strbody & vbCrLf & listItems
       
    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

Works perfectly! Thanks

 

[smozgur]

Works perfectly! Thanks

You're welcome. Glad to hear it helps.

 

[mjohnston0209]

You're welcome. Glad to hear it helps.

So I thought I would be able to copy the code over to a mac computer, but I get the error

Run-time error '429':
ActiveX component can't create object

The error pops up on the line

Set Mail_Object = CreateObject("Outlook.Application")

The macbook air uses outlook so I was hoping it would work. Is this possible?

 

[smozgur]

Although I never tried automating e-mail jobs by using Outlook on Mac, I don't think that it is possible by using Outlook as there is no library provided that you can control Outlook application object from Excel. So, even you can use early binding for Word, and PowerPoint as well as the CreateObject method, there is no "Outlook.Application" class defined in Excel on Mac.

I can see a potential workaround by using AppleScript and Mac's Mail application, but I am not sure if it would be something that will help you in your project.

Sending email via Mail from Excel Mac automatically

In this tutorial, we are going to send Mail (pun) from Excel Mac

medium.com

 

[mjohnston0209]

Although I never tried automating e-mail jobs by using Outlook on Mac, I don't think that it is possible by using Outlook as there is no library provided that you can control Outlook application object from Excel. So, even you can use early binding for Word, and PowerPoint as well as the CreateObject method, there is no "Outlook.Application" class defined in Excel on Mac.

View attachment 33261

I can see a potential workaround by using AppleScript and Mac's Mail application, but I am not sure if it would be something that will help you in your project.

Sending email via Mail from Excel Mac automatically

In this tutorial, we are going to send Mail (pun) from Excel Mac

medium.com

Ok thanks. How about via outlook web access? Would I use different wording for any of my variables relating to the email or for

Set Mail_Object = CreateObject("Outlook.Application")

 

[smozgur]

How about via outlook web access?

I didn't personally use the code or add-in, however, Ron de Bruin has really great tutorials that you might want to look at - especially the following one (Excel vs Outlook on Mac):

Mail with Mac Outlook | Mac Excel Automation

Send Mail from Mac Excel with Mac Outlook

www.macexcel.com

You might want to take a look at the article, then you can configure your code to call different actions according to the operating system if your application is supposed to work on both Windows and Mac.

As I said, I didn't use the method but it should be still using AppleScript due to the application sandbox reality on Mac.

Additionally, the following one for Excel vs Mac Mail (instead of Outlook on Mac - it is less complicated since the Mail is already a Mac application):

Mail with Mac Mail | Mac Excel Automation

Send Mail from Mac Excel with Mac Mail

www.macexcel.com

 

[daphneti]

Thanks a lot for the solution below, it works great. I do have two follow up questions (being relatively new to VBA) i was wondering if you could help with?

The code leaves a margin between the header above the list and the bullet list, ie. I am getting the list like this
Header

• Item 1
• Item 2

Instead I would like to get the list without a margin (it is not a line break). I am trying to change the list formatting but have been unsuccesfull. In outlook the issue can be solved by checking the box in the snippit below. Would there be a way to input this formatting in your code?

And the second questions is related to fonts; changing

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

to

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

Will not change the email font to calibri light. Do you know what would cause this issue?

Your answer would be much appreciated.

You can use HTML List Object:

Sub Send_Absent_Emails_Complete()

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, strbody As String

Dim rng As Range
Dim cll As Range
Dim listItems 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>"
      
    ' Students range
    ' Set this range dynamically instead of
    ' defining a certain range as I did as sample below
    Set rng = Range("B6:B10")
    For Each cll In rng.Cells
        listItems = listItems & vbCrLf & "<li>" & cll.Value & "</li>"
    Next cll
      
    listItems = "<ul>" & vbCrLf & listItems & "</ul>"
  
    ' Insert listItems into the strBody
    strbody = strbody & vbCrLf & listItems
      
    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

 

[smozgur]

@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.

Will not change the email font to calibri light. Do you know what would cause this issue?

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