export to Outlook email, paragraph formatting

[markjkubicki]

This issue has plagued my for a very long time


1 - because it drives me nuts every time I need to correct in in an email;
2- because it ought to be sooooooo easy to fix, but that evades me constantly)

the below snippet creates an Outlook email.
The problem is the paragraph format (text of the email) although the code includes:

 SpaceAfter = 0, Paragraph.SpaceBefore = 0

the spacing always results in "spacing = auto" (which defaults to a double spaced paragraph - although that is not the case with emails initiated directly is Outlook);


(I'm also not certain what this portion of code does; but that's a different question for another day)

>" & Nz(varbody, "") & Format(Now) & "<

 Dim olApp As Object
    Dim objMail As Object
    Set olApp = CreateObject("Outlook.Application")    'Create a new instance of Outlook
    'Create e-mail item
    Set objMail = olApp.CreateItem(0)
    With objMail
        'Set body format to HTML
        '        .bodyformat = 2

        Dim vTitle As String
        Dim vPrompt As String
        Dim Responce As Variant
        If IsNull(varaddress) Then
            vTitle = "MISSING ADDRESS"
            vPrompt = "Address is incorrect or missing; no email will be sent"
            Responce = MsgBox(vPrompt, vbOKOnly + vbCritical, vTitle)
            Exit Sub
        Else
            With objMail
                .to = varaddress & ""
                .Subject = Nz(varsubject, "")
                .HTMLBody = "<span style='font:calibri, font-size:11pt, Paragraph.SpaceAfter = 0, Paragraph.SpaceBefore = 0;'>" & Nz(varbody, "") & Format(Now) & "<\span>"
                .Body = Nz(varbody, "")
                .Save
                .Display
            End With
        End If
    End With
    Set olApp = Nothing
    Set objMail = Nothing

any thoughts will be greatly appreciated in advance
m.

[Welshgasman]

Why are you using .HTMLBody and .Body?
I thought it was one or the other?

[markjkubicki]

no idea why both are there;
and removing .body sort of worked:

the paragraph spacing is correct: 0pt before / after;
however,
in the body, all of the line feeds disappear. the text of the is one long line. I tried replacing all of the vbCrLf with Chr(13) & Chr(10); this did not correct the problem.

(the format of the text ought to be / needs to be RichFormat for further edit by the user)

should not matter but... the code for creating the text of the email is

  varbody = ""
        varbody = varName & "," & vbCrLf & _
            vbCrLf & _
            Nz("Mfr: " & Forms![frmSpec].[Mfr] & ", " & Forms![frmSpec].[CatalogNo], "")
        If Len(Nz(Forms![frmSpec].[Location])) > 0 Then
            varbody = varbody & vbCrLf & _
                "Location: " & Forms![frmSpec].[Location]
        End If
            
        If Not IsNull(Forms![frmSpec].[Qty]) Then
            varbody = varbody & vbCrLf & _
                Nz("Quantity: " & Forms![frmSpec].[Qty] & " " & Forms![frmSpec].[Units], "")
        End If

[June7]

If you want to build an HTML string, need to use HTML tag <br> for line feeds.

varbody is not building HTML.

AFAIK, mixing HTML and non-HTML strings won't work. The email is either HTML or it isn't.

HTML email can be edited by user. What edits will they do?
I did a test with HTML email. I could modify colors and fonts and edit data.

[markjkubicki]

what edits would they do... any thing that you could image being done: add to, delete from, reformat, add images...
the email ought to be HTML


? varbody is not building HTML ?

thnx ... homework for tonight (for me): 'learn how to write a basic HTML string'

thnx ,
m.

[June7]

Pretty good tutorial site https://www.w3schools.com/html/
Here are some examples:

body = "<table style='text-align:right;border:1px solid black;font-family:calibri;border-collapse:collapse;padding:15px'><tr style='background:yellow;mso-highlight:yellow'>" & _
       "<th>No</th><th>Client</th><th>Date</th><th>PR</th><th>Currency</th><th>Amt</th></tr>" & _
       "<tr><td>12345</td><td>Jones</td><td>12Dec2017</td><td>ABC</td><td>US</td><td>2000</td></tr>" & _
       "<tr><td>67890</td><td>Finkbiner</td><td>12Dec2017</td><td>ABC</td><td>US</td><td>1000</td></tr></table>"

body = "<HTML style='font-family:calibri'><Body><font face='calibri'><a href='C:\Users\June'><img SRC=C:\Users\Administrator\June\DOT\Lab\Editing\LABDB.png></a><br>" & _
       "<font color='red' size='1'>&nbsp;&nbsp;&nbsp;&nbsp;READ THIS EMAIL COMPLETELY</font><br>" & _
            body & _
       "<br><b>Please confirm or I will kick you.</b><br>" & _
       "</font></Body></HTML>" & _
       "<table><tr bgcolor='yellow'>THIS WILL CREATE BLACK TEXT WITH YELLOW BACKGROUND</tr></table>" & _
       "<span style='background:yellow'>ALSO BLACK TEXT YELLOW BACKGROUND</span>"

body = "<HTML><head><style>" & _
       "table{border-collapse:collapse; width:80%} td,th{text-align:left; width:50%}  table,td,th{border: 1px solid black;}" & _
       "</style></head><Body><table>" & _
       "<tr><th>Quote ID:</th><td>" & Format(123, "###,###.00") & "</td></tr>" & _
       "<tr><th>Customer:</th><td>456</td></tr>" & _
       "<tr><th>Address:</th><td>789</td></tr>" & _
       "<tr><th>Address:</th><td>123</td></tr>" & _
       "<tr><th>Town:</th><td></td>456</tr>" & _
       "<tr><th>PostCode:</th><td>789</td></tr>" & _
       "<tr><th>Image:</th><td><p>1: <img SRC=C:\Users\June\LABDB.png></td></tr>" & _
       "</table>"

Dim strS As String, strT As String
strS = "width='500' style='text-align:left;border:2px;font-family:calibri;border-collapse:collapse;padding:5px'"
strT = "style='border:3px solid black' width='50%'"
testH1 = "<table " & strS & "><tr><td " & strT & ">Initial quote : </td><td " & strT & "></td></tr>" & _
"<tr><th " & strT & ">Discount rate : </th><td  " & strT & "></td></tr>" & _
"<tr><th " & strT & ">Final quote : </th><td " & strT & "></td></tr></table><br>" & _
"<table " & strS & "><tr><th " & strT & ">Last spend on year 2020 : </th><td " & strT & "></td></tr>" & _
"<tr><th " & strT & ">Increment increase percentage : </th><td " & strT & "></td></tr>" & _
"<tr><th " & strT & ">Final quote : </th><td " & strT & "></td></tr></table>"

[markjkubicki]

PERFECT
thnx again

in particular: the part of the code:

"<font color='red' size='1'>&nbsp;&nbsp;&nbsp;&nbsp;READ THIS EMAIL COMPLETELY</font><br>" & _
body & _
"<br><b>Please confirm or I will kick you.</b><br>" & _

(sounds like something i would write !!)