VBA remove margin before bulletpoint/unordered list in email

daphneti

New Member
Joined
Apr 12, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello!

I am using VBA to send out emails containing a text body made up of bullet points with varying lengths (using the code from smozgur in this thread: Use VBA to enter a varying range of bullet points into body of email). Which creates a reference to the bullet list in the body text instead of typing the list references in the text directly (which makes it more difficult for me to format the list).

The code works perfectly however displays a margin between the bullet list and heading (list 1 in picture left) however i would like to remove the margin (list 2 in picture left). This can be done in outlook directly using the paragraph settings (picture right), would there be a way to change this formatting directly into the code below?

1649771537122.png
1649771628919.png


The code I'm using:

VBA Code:
Sub UL_list_margin_example()
'I am trying to set up an email containing two bullet lists where the number of bullets can vary
 
'Setting up outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Variables setting
Dim StrBullet As String
Dim rng As Range
Dim cll As Range
Dim listItems As String
Dim listItems2 As String

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


'For the bulletitems
Set rng = Worksheets("Sheet1").Range("A3").CurrentRegion
    For Each cll In rng.Cells
        listItems = listItems & vbCrLf & "<li>" & cll.Value & "</li>"
    Next cll
        
    listItems = "<ul>" & vbCrLf & listItems & "</ul>"
    
Set rng = Worksheets("Sheet1").Range("A13").CurrentRegion
    For Each cll In rng.Cells
        listItems2 = listItems2 & vbCrLf & "<li>" & cll.Value & "</li>"
    Next cll
        
    listItems2 = "<ul>" & vbCrLf & listItems2 & "</ul>"

'main body text set up
StrBullet = "<BODY style=font-size:11pt;font-family:""Calibri Light"">" & _
        Worksheets("Sheet1").Range("C1") & "<br><br>" & _
        "<b>" & Worksheets("Sheet1").Range("A1") & "</b>" & _
        vbCrLf & _
        listItems & _
         "<b>" & Worksheets("Sheet1").Range("A11") & "</b>" & _
        vbCrLf & _
        listItems2 & _
        "<b>" & Worksheets("Sheet1").Range("C2") & "</b>"
    

'generate email
    With olMail
        .To = Worksheets("Sheet1").Range("D1")
        .CC = Worksheets("Sheet1").Range("D2")
        .Subject = Worksheets("Sheet1").Range("D3")
        .HTMLBody = StrBullet
        .Display
                
        End With

End Sub

And the sheet layout:
1649771821120.png


Any thoughts/help would be greatly appreciated!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,651
Office Version
  1. 365
Platform
  1. Windows
IIRC, a bulleted list is naturally spaced from other body text so you shouldn't need the vbCrLf, but I could be wrong about that. If it's needed at all, try putting it at the end of the list item line instead of the beginning. The way it is, you're starting with a new line before the list item line as well as before the list tags (<ul>).
However, whenever I tried to pass a vba line feed (it was a long time ago) to html it never seemed to work in my email body. I had to use <br> and <p> instead.
 
Upvote 0

daphneti

New Member
Joined
Apr 12, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your reply! Unfortunatly putting it at the back of the list item line did not change my results. You are right though that the code works fine without it so I have removed both lines.

The reason why I think this does not solve the issue is because the spacing between the heading text and the bullet list is not a line break rather a margin/spacing issue (i cannot remove it when the email is opened in my outlook editor by backspacing, only by using the paragraph setting as posted above).
 
Upvote 0

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
2,034
Custom style is necessary for each individual list item - I know it is strange but it is apparently how it works in the body of the Outlook mail item body.

I would simply use a style variable and set necessary styles globally for the message.

Try the following code. I didn't want to change a lot but still made some changes to your original code other than adding the style variable only (removed some unnecessary chars, used div instead of body element, etc. Also, you don't need the CreateObject method while you are using early binding with Outlook object library reference. So, I commented that line out as well.

Hope it helps.

VBA Code:
Sub UL_list_margin_example()
'Variables setting
Dim StrBullet As String
Dim rng As Range
Dim cll As Range
Dim listItems As String
Dim listItems2 As String
Dim style As String

'Setting up outlook
Dim olApp As New Outlook.Application
Dim olMail As Outlook.MailItem
'Set olApp = CreateObject("Outlook.Application")

    Set olMail = olApp.CreateItem(olMailItem)

    'For the bulletitems
    Set rng = Worksheets("Sheet1").Range("A3").CurrentRegion
    For Each cll In rng.Cells
        listItems = listItems & "<li>" & cll.Value & "</li>"
    Next cll
    listItems = "<ul>" & listItems & "</ul>"
 
    Set rng = Worksheets("Sheet1").Range("A13").CurrentRegion
    For Each cll In rng.Cells
        listItems2 = listItems2 & "<li>" & cll.Value & "</li>"
    Next cll
    listItems2 = "<ul>" & listItems2 & "</ul>"
 
    style = "<style>" & _
            "div {font-size:11pt;font-family:""Calibri Light""}" & _
            "li {padding: 0pt; margin: 0pt} " & _
            "</style>"

    'main body text set up
    StrBullet = style & "<div>" & _
        Worksheets("Sheet1").Range("C1") & "<br/><br/>" & _
        "<b>" & Worksheets("Sheet1").Range("A1") & "</b>" & _
        listItems & "<br/>" & _
        "<b>" & Worksheets("Sheet1").Range("A11") & "</b>" & _
        listItems2 & "<br/>" & _
        "<b>" & Worksheets("Sheet1").Range("C2") & "</b></div>"
 

    'generate email
    With olMail
        .To = Worksheets("Sheet1").Range("D1")
        .CC = Worksheets("Sheet1").Range("D2")
        .Subject = Worksheets("Sheet1").Range("D3")
        .HTMLBody = StrBullet
        .Display
             
    End With

End Sub

Edit: Result

1649778400868.png
 
Last edited:
Upvote 0
Solution

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,651
Office Version
  1. 365
Platform
  1. Windows
Probably will work for you if you remove the <ul> tags, but then you'll have to inject spaces if you don't want it to be left justified.
 
Upvote 0

daphneti

New Member
Joined
Apr 12, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Custom style is necessary for each individual list item - I know it is strange but it is apparently how it works in the body of the Outlook mail item body.

I would simply use a style variable and set necessary styles globally for the message.

Try the following code. I didn't want to change a lot but still made some changes to your original code other than adding the style variable only (removed some unnecessary chars, used div instead of body element, etc. Also, you don't need the CreateObject method while you are using early binding with Outlook object library reference. So, I commented that line out as well.

Hope it helps.

VBA Code:
Sub UL_list_margin_example()
'Variables setting
Dim StrBullet As String
Dim rng As Range
Dim cll As Range
Dim listItems As String
Dim listItems2 As String
Dim style As String

'Setting up outlook
Dim olApp As New Outlook.Application
Dim olMail As Outlook.MailItem
'Set olApp = CreateObject("Outlook.Application")

    Set olMail = olApp.CreateItem(olMailItem)

    'For the bulletitems
    Set rng = Worksheets("Sheet1").Range("A3").CurrentRegion
    For Each cll In rng.Cells
        listItems = listItems & "<li>" & cll.Value & "</li>"
    Next cll
    listItems = "<ul>" & listItems & "</ul>"
 
    Set rng = Worksheets("Sheet1").Range("A13").CurrentRegion
    For Each cll In rng.Cells
        listItems2 = listItems2 & "<li>" & cll.Value & "</li>"
    Next cll
    listItems2 = "<ul>" & listItems2 & "</ul>"
 
    style = "<style>" & _
            "div {font-size:11pt;font-family:""Calibri Light""}" & _
            "li {padding: 0pt; margin: 0pt} " & _
            "</style>"

    'main body text set up
    StrBullet = style & "<div>" & _
        Worksheets("Sheet1").Range("C1") & "<br/><br/>" & _
        "<b>" & Worksheets("Sheet1").Range("A1") & "</b>" & _
        listItems & "<br/>" & _
        "<b>" & Worksheets("Sheet1").Range("A11") & "</b>" & _
        listItems2 & "<br/>" & _
        "<b>" & Worksheets("Sheet1").Range("C2") & "</b></div>"
 

    'generate email
    With olMail
        .To = Worksheets("Sheet1").Range("D1")
        .CC = Worksheets("Sheet1").Range("D2")
        .Subject = Worksheets("Sheet1").Range("D3")
        .HTMLBody = StrBullet
        .Display
            
    End With

End Sub

Edit: Result

View attachment 62322
Thanks a lot! Using the style variable works well!
Also your other adjustments are much appreciated, learning as I go ;)
 
Upvote 0

Forum statistics

Threads
1,186,796
Messages
5,959,832
Members
438,452
Latest member
Poor Dave

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