VBA remove margin before bulletpoint/unordered list in email

D

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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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
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
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
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
smozgur said:
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
Click to expand...
Thanks a lot! Using the style variable works well!
Also your other adjustments are much appreciated, learning as I go ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Add text before pasting pic in outlook
Replies
2
Views
79
DanteAmor
DanteAmor
R
  • Solved
Control text formatting (e.g. font, color, etc.) when using Excel VBA to automate emails via Outlook
Replies
2
Views
295
Roland Hoelscher
R
M
  • Question Question
code to include a column or more based on checkbox
Replies
6
Views
139
mecerrato
M
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
302
Fluff
Fluff
R
  • Question Question
VBA - AUTO FIT, Can we remove make the sum formula in below code
Replies
1
Views
157
Joe4
Joe4

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top