UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Email Query Results In Body Of Email Etc, Access 2016    
 
   
MadPiet
post Jul 2 2020, 04:04 PM
Post#21



Posts: 3,778
Joined: 27-February 09



Daniel,

So do I, but I couldn't see how to do that in an outlook mail message. Is there a way? Personally, i'd have used nthChild of the row object (if I remember right) -- because that way i can format the whole column at once.
Go to the top of the page
 
cheekybuddha
post Jul 2 2020, 04:41 PM
Post#22


UtterAccess Moderator
Posts: 13,005
Joined: 6-December 03
From: Telegraph Hill


This might be of interest (or a cause of depression): Word 2007 HTML and CSS Rendering Capabilities in Outlook 2007 (Part 1 of 2)

--------------------


Regards,

David Marten
Go to the top of the page
 
Leah
post Jul 2 2020, 05:03 PM
Post#23



Posts: 862
Joined: 22-February 00
From: New York, New York


I just finished my other project. I will see how far I get now, otherwise, I will leave for Monday and hopefully chill on Friday.

There is so much good stuff out there that was shared that I look forward to learning.

Thanks again and to everyone who chimed in.

Leah

To be continued.

--------------------
Leah A. Kopel
Go to the top of the page
 
MadPiet
post Jul 2 2020, 05:52 PM
Post#24



Posts: 3,778
Joined: 27-February 09



Leah,
FWIW, this might be helpful (it's about formatting tables with CSS)
https://www.w3schools.com/CSS/css_table.asp
Go to the top of the page
 
DanielPineault
post Jul 2 2020, 06:53 PM
Post#25


UtterAccess VIP
Posts: 7,376
Joined: 30-June 11



Here's a very crude demo. Normally, I build a template html file, read it into memory and add to it. This way the html doesn't need to be hard coded in VBA like the demo below and can easily be adjusted.

CODE
Sub GenEmail()
    Dim olApp                 As Object
    Dim OlMail                As Object
    Dim sHTML                 As String

    On Error GoTo Error_Handler

    Set olApp = CreateObject("Outlook.application")
    Set OlMail = olApp.CreateItem(0)
    
    'Build the e-mail body
    sHTML = sHTML & "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 3.2//EN"">" & vbCrLf
    sHTML = sHTML & "<html>" & vbCrLf
    sHTML = sHTML & "<head>" & vbCrLf
    sHTML = sHTML & "<style>" & vbCrLf
    sHTML = sHTML & "   body {" & vbCrLf
    sHTML = sHTML & "       font-size: 11pt;" & vbCrLf
    sHTML = sHTML & "       font-family: Calibri;" & vbCrLf
    sHTML = sHTML & "   }" & vbCrLf
    sHTML = sHTML & "   th {" & vbCrLf
    sHTML = sHTML & "       font-size: 14pt;" & vbCrLf
    sHTML = sHTML & "       font-family: Arial, Helvetica;" & vbCrLf
    sHTML = sHTML & "       text-transform: uppercase;" & vbCrLf
    sHTML = sHTML & "   }" & vbCrLf
    sHTML = sHTML & "   .shaded {background-color: #92a8d1;}" & vbCrLf
    sHTML = sHTML & "</style>" & vbCrLf
    sHTML = sHTML & "</head>" & vbCrLf
    sHTML = sHTML & "<body>" & vbCrLf
    sHTML = sHTML & "<p>Dear So and So,</p>" & vbCrLf
    sHTML = sHTML & "<p>" & GenHTMLTable("qry_Companies") & "</p>" & vbCrLf '*************Adjust query name here!*************
    sHTML = sHTML & vbCrLf
    sHTML = sHTML & "</body>" & vbCrLf
    sHTML = sHTML & "</html>" & vbCrLf


    'Create the actual e-mail
    OlMail.To = "First_Name.Last_Name@Somewhere.com"
    OlMail.Subject = "PLEASE ADVISE: Estimates of Additional Fees"
    OlMail.HTMLBody = sHTML
    OlMail.Display

Error_Handler_Exit:
    On Error Resume Next
    If Not OlMail Is Nothing Then Set OlMail = Nothing
    If Not olApp Is Nothing Then Set olApp = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GenEmail" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub


'---------------------------------------------------------------------------------------
' Procedure : GenHTMLTable
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate an HTML Table string from a Query's Resulting Recordset
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sQuery        : Name of the query to use to create the HTML Table string from
' bInclHeader   : True/False whether to include a header row with the field names
'
' Usage:
' ~~~~~~
' sMsg = GenHTMLTable("YourQueryName")
' sMsg = GenHTMLTable("YourQueryName", FALSE)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ********************************************************************************
******
' 1         2017-02-14              Initial Release
' 2         2018-09-20              Updated Copyright
'---------------------------------------------------------------------------------------
Function GenHTMLTable(sQuery As String, Optional bInclHeader As Boolean = True) As String
10        On Error GoTo Error_Handler
          Dim db                    As DAO.Database
          Dim qdf                   As DAO.QueryDef
          Dim prm                   As DAO.Parameter
          Dim rs                    As DAO.Recordset
          Dim fld                   As DAO.Field
          Dim sHTML                 As String
          Dim lRowCounter As Long

20        Set db = CurrentDb
30        Set qdf = db.QueryDefs(sQuery)

40        For Each prm In qdf.Parameters
50            prm = Eval(prm.Name)
60        Next prm

70        Set rs = qdf.OpenRecordset
80        With rs
90            sHTML = "<table>" & vbCrLf
100           If bInclHeader = True Then
                  'Build the header row if requested
110               sHTML = sHTML & vbTab & "<tr>" & vbCrLf
120               For Each fld In rs.Fields
130                   sHTML = sHTML & vbTab & vbTab & "<th>" & fld.Name & "</th>" & vbCrLf
140               Next
150               sHTML = sHTML & vbTab & "</tr>" & vbCrLf
160           End If
170           If .RecordCount <> 0 Then
180               Do While Not .EOF
                        lRowCounter = lRowCounter + 1 'used for alternate row shading
                      'Build a row for each record in the recordset
190                   sHTML = sHTML & vbTab & "<tr" & IIf(lRowCounter Mod 2 = 0, " class=""shaded""", "") & ">" & vbCrLf 'shaded
200                   For Each fld In rs.Fields
210                       sHTML = sHTML & vbTab & vbTab & "<td>" & fld.Value & "</td>" & vbCrLf
220                   Next
230                   sHTML = sHTML & vbTab & "</tr>" & vbCrLf
240                   .MoveNext
250               Loop
260           End If
270           sHTML = sHTML & "</table>"
280       End With

290       GenHTMLTable = sHTML

Error_Handler_Exit:
300       On Error Resume Next
310       If Not fld Is Nothing Then Set fld = Nothing
320       If Not rs Is Nothing Then
330           rs.Close
340           Set rs = Nothing
350       End If
360       If Not db Is Nothing Then Set db = Nothing
370       Exit Function

Error_Handler:
380       MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: GenHTMLTable" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occurred!"
390       Resume Error_Handler_Exit
End Function

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
stevep
post Jul 3 2020, 04:49 PM
Post#26



Posts: 118
Joined: 9-November 14



I was intrigued by Madpiets suggestion about using the query field type to set the alignment.

The following is a modified piece of Daniels code which is intended to set the alignment based on the field type but everything in the email still comes out left aligned. What am I doing wrong? I think I am quite close

I inspected the resulting html and it says <td text-align: right>14</td> but it still comes out left aligned.

CODE
        If .RecordCount <> 0 Then
            Do While Not .EOF
                'Build a row for each record in the recordset
                sHTML = sHTML & vbTab & "<tr text-align:center; >" & vbCrLf
                For Each Fld In rs.Fields
'                    sHTML = sHTML & vbTab & vbTab & "<td text-align:center >" & Fld.Value & "</td>" & vbCrLf 'Daniels original line
                   Select Case Fld.Type ' inserting a section into the code to determine alignment from the field type
                    Case 10, 8, 1 ' text, date, bool
                        sAlign = "center"
                    Case 2, 3, 4, 5, 6, 7, 20 ' numbers byte, int, long, sing, dbl,dec, curr
                        sAlign = "right"
                    Case Else ' i probably missed some
                        sAlign = "left"
                    End Select
                    Debug.Print Fld.Type, sAlign
                    sHTML = sHTML & vbTab & vbTab & "<td text-align: " & sAlign & " >" & Fld.Value & "</td>" & vbCrLf
                Next
                sHTML = sHTML & vbTab & "</tr>" & vbCrLf
                .MoveNext
            Loop
        End If


Here is the email body. I don't know how to tag this to make it present properly but it looks like this in Outlook (all fields are left aligned even though the HTML says right aligned)

CODE
Item    QtyStart    QtyTarget    QtyMin    QtyMult    PLO    EMailString
A    100    140    24    12    48    A 100 48
B    120    100    24    12    0    B 120 0
C    70    1    1    1    0    C 70 0
D    6    180    877    12    877    D 6 877
E    139    140    888    1    888    E 139 888
F    128    140    1    45    45    F 128 45
G    16    140    100    20    140    G 16 140
Go to the top of the page
 
MadPiet
post Jul 3 2020, 06:32 PM
Post#27



Posts: 3,778
Joined: 27-February 09



This works in normal HTML...

<td style="color: red; text-align:right;">$5</td>

(if you can't use normal styles and nth-child(n){} to do the formatting... I would not leave any spaces in the HTML, and since you're doing this in VBA, I'd probably create a named constant for single and double quote. then you can do this:

Const cSQUOTE = "'"
Const cDBLQUOTE = """"
strHTML = "<td style=" & cDBLQUOTE & "color:red; text-align:right;" & cDBLQUOTE & ">$5</td>"
Go to the top of the page
 
stevep
post Yesterday, 10:26 AM
Post#28



Posts: 118
Joined: 9-November 14



We are probably driving the OP nuts:)

Here is the html for the whole thing. In Outlook, this all shows left aligned, not right or center.

CODE
<table>
    <tr>
        <th>Item</th>
        <th>QtyStart</th>
        <th>QtyTarget</th>
        <th>QtyMin</th>
        <th>QtyMult</th>
        <th>PLO</th>
        <th>EMailString</th>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >A</td>
        <td "text-align:right;" >100</td>
        <td "text-align:right;" >140</td>
        <td "text-align:right;" >24</td>
        <td "text-align:right;" >12</td>
        <td "text-align:right;" >48</td>
        <td "text-align:center;" >A 100 48</td>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >B</td>
        <td "text-align:right;" >120</td>
        <td "text-align:right;" >100</td>
        <td "text-align:right;" >24</td>
        <td "text-align:right;" >12</td>
        <td "text-align:right;" >0</td>
        <td "text-align:center;" >B 120 0</td>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >C</td>
        <td "text-align:right;" >70</td>
        <td "text-align:right;" >1</td>
        <td "text-align:right;" >1</td>
        <td "text-align:right;" >1</td>
        <td "text-align:right;" >0</td>
        <td "text-align:center;" >C 70 0</td>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >D</td>
        <td "text-align:right;" >6</td>
        <td "text-align:right;" >180</td>
        <td "text-align:right;" >877</td>
        <td "text-align:right;" >12</td>
        <td "text-align:right;" >877</td>
        <td "text-align:center;" >D 6 877</td>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >E</td>
        <td "text-align:right;" >139</td>
        <td "text-align:right;" >140</td>
        <td "text-align:right;" >888</td>
        <td "text-align:right;" >1</td>
        <td "text-align:right;" >888</td>
        <td "text-align:center;" >E 139 888</td>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >F</td>
        <td "text-align:right;" >128</td>
        <td "text-align:right;" >140</td>
        <td "text-align:right;" >1</td>
        <td "text-align:right;" >45</td>
        <td "text-align:right;" >45</td>
        <td "text-align:center;" >F 128 45</td>
    </tr>
    <tr text-align:center; >
        <td "text-align:center;" >G</td>
        <td "text-align:right;" >16</td>
        <td "text-align:right;" >140</td>
        <td "text-align:right;" >100</td>
        <td "text-align:right;" >20</td>
        <td "text-align:right;" >140</td>
        <td "text-align:center;" >G 16 140</td>
    </tr>
</table>


This post has been edited by stevep: Yesterday, 10:27 AM
Go to the top of the page
 
MadPiet
post Yesterday, 11:50 AM
Post#29



Posts: 3,778
Joined: 27-February 09



TBH, I didn't check in Outlook... I wrote and tested it on w3schools, and it looked fine there. Maybe it doesn't use HTML5 (?)
Go to the top of the page
 
DanielPineault
post Today, 10:03 AM
Post#30


UtterAccess VIP
Posts: 7,376
Joined: 30-June 11



Outlook doesn't recognize/handle a lot of CSS and HTML. You have to test, test, test. Normal validation W3C... are no good when it comes to MS/Outlook.

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
MadPiet
post Today, 11:33 AM
Post#31



Posts: 3,778
Joined: 27-February 09



Daniel,

That was my basic conclusion about Outlook... the formatting/CSS support is dodgy at best. Shame, because using Nth child pseudo-class would make this super easy.

Pieter
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 06:13 PM