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

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
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:

VBA Code:
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!
 

Attachments

  • Email Info.PNG
    Email Info.PNG
    6.6 KB · Views: 3

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,409
You can use HTML List Object:

VBA Code:
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
 
Solution

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
You can use HTML List Object:

VBA Code:
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
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46

ADVERTISEMENT

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

VBA Code:
Set Mail_Object = CreateObject("Outlook.Application")

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

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,409
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.

1614555559339.png


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.
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
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.
Ok thanks. How about via outlook web access? Would I use different wording for any of my variables relating to the email or for

VBA Code:
Set Mail_Object = CreateObject("Outlook.Application")
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,409
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):

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):
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,830
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top