none
Paste query result table into HTML email body RRS feed

  • Question

  • I have the following code successfully pasting thee query results into the body of an html email.  I would like to size all tables the same using tags like <table width='800'> and </table>, but I can't seem to add the tags using SendKeys they appear as text in the email.

    What am I missing.

    strEmail = emailRecs.Fields(1)
    Set olLook = Outlook.Application
    Set olNewEmail = olLook.CreateItem(0)
    strEmailSubject = "PASSPORT AND FLIGHT INFORMATION"
    
    strEmailText = "<br><p style='font-family:calibri'><center>VERY IMPORTANT MESSAGE</center><br>" _
        & vbCrLf & "PLEASE NOTE THAT WE NEED TO RECEIVE  PASSPORT AND FLIGHT INFORMATION RELATED TO ALL PARTIES INCLUDED IN YOUR RESERVATION.<br>" _
        & vbCrLf & "<br>" _
        & vbCrLf & "<br>" _
    
       With olNewEmail   'Attach template
          .To = strEmail
          .CC = ""
    '      .BCC = strBcc
          .HTMLBody = strEmailText 'THIS WORKS
    '      .Body = strEmailText
          .Subject = strEmailSubject
    '      .Attachments.Add ("\\Fullpath\Share\Fullfilename.ext")
    '      .SendUsingAccount = olLook.Session.Accounts.Item(2)
          .Display
        DoCmd.OpenQuery "Passport Information", acViewNormal, acEdit
        DoCmd.SelectObject acQuery, "Passport Information"
        DoCmd.RunCommand acCmdSelectAllRecords
        RunCommand acCmdCopy
        DoCmd.Close acQuery, "Passport Information", acSaveNo
        SendKeys "^v", True
        SendKeys "{ENTER}", True
        
        DoCmd.OpenQuery "ARRIVAL Flight Info", acViewNormal, acEdit
        DoCmd.SelectObject acQuery, "ARRIVAL Flight Info"
        DoCmd.RunCommand acCmdSelectAllRecords
        RunCommand acCmdCopy
        DoCmd.Close acQuery, "ARRIVAL Flight Info", acSaveNo
        SendKeys "^v", True
        SendKeys "{ENTER}", True
        
        SendKeys "<table width='800'>", True
        DoCmd.OpenQuery "DEPARTING Flight Info", acViewNormal, acEdit
        DoCmd.SelectObject acQuery, "DEPARTING Flight Info"
        DoCmd.RunCommand acCmdSelectAllRecords
        RunCommand acCmdCopy
        DoCmd.Close acQuery, "DEPARTING Flight Info", acSaveNo
        SendKeys "^v", True
        SendKeys "</table>", True
       End With



    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Tuesday, February 14, 2017 4:48 PM

Answers

  • What about seeing the width of the heading column since you seem to insinuate the table with isn't being respected.

    What email client are you using?


    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by DriveEV Tuesday, February 14, 2017 11:54 PM
    Tuesday, February 14, 2017 8:34 PM

All replies

  • Hi,

    How about rather than use SendKeys, try something like:

    .HTMLBody = HTMLBody & "<table width='800'>"

    Hope it helps...

    Tuesday, February 14, 2017 4:55 PM
  • Hi DBguy,

    Thanks for the quick reply.

    Where in the code should I try your suggestion?

    Currently .HTMLBody = strEmailText

    When the email displays the strEmailText is in the body and the cursor is at the top of the email body.

    Pasting occurs above the strEmailText pushing it down as the tables get pasted.

    I’m under the impression the tags need to surround the pasted tables.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Tuesday, February 14, 2017 5:11 PM
  • Hi, My suggestion was to replace your SendKeys "<table..." with the one I posted. Hope it's clearer...
    Tuesday, February 14, 2017 5:30 PM
  • Your suggestion did not work.  Maybe what I need is code to set the PreferredWidth of each table after it is pasted.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    I tried the below but got an error at ActiveDocument.Tables

    Sub setTableWidth()
    Dim aTable As Table
    For Each aTable In ActiveDocument.Tables
    aTable.Select aTable.PreferredWidthType = wdPreferredWidthPoints.aTable.PreferredWidth = CentimetersToPoints(17)
    Next aTable
    End Sub


    • Edited by DriveEV Tuesday, February 14, 2017 6:09 PM
    Tuesday, February 14, 2017 5:55 PM
  • May I ask why you are not building the e-mail body string in VBA entirely, instead of using SendKeys (which should be avoided like the plague)?


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, February 14, 2017 6:12 PM
  • Your suggestion did not work.


    Sorry to hear it, but I'm just curious to see how you implemented it. Can you post the new code showing where you added/inserted my suggestion? Thanks.

    Tuesday, February 14, 2017 6:19 PM
  • The SendKeys was simply used to add a blank line after each pasted Table.

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Tuesday, February 14, 2017 6:25 PM
  • You seem to be doing Copy/Paste using sendkeys.  I'd use something more like:

    Function GenHTMLTable(sQuery As String) As String
        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 sHTML                 As String
    
        Set db = CurrentDb
        Set qdf = db.QueryDefs(sQuery)
    
        For Each prm In qdf.Parameters
            prm = Eval(prm.Name)
        Next prm
    
        Set rs = qdf.OpenRecordset
        With rs
            sHTML = "<table>" & vbCrLf
            sHTML = sHTML & vbTab & "<tr>" & vbCrLf
            For Each fld In rs.Fields    'loop through all the fields of the tables
                sHTML = sHTML & vbTab & vbTab & "<th>" & fld.Name & "</th>" & vbCrLf
            Next
            sHTML = sHTML & vbTab & "</tr>" & vbCrLf
            If .RecordCount <> 0 Then
                Do While Not .EOF
                    sHTML = sHTML & vbTab & "<tr>" & vbCrLf
                    For Each fld In rs.Fields            'loop through all the fields of the tables
                        sHTML = sHTML & vbTab & vbTab & "<td>" & fld.Value & "</td>" & vbCrLf
                    Next
                    sHTML = sHTML & vbTab & "</tr>" & vbCrLf
                    .MoveNext
                Loop
            End If
            sHTML = sHTML & "</table>"
        End With
    
        GenHTMLTable = sHTML
    
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not rs Is Nothing Then
            rs.Close    'Close the recordset
            Set rs = Nothing
        End If
        If Not db Is Nothing Then Set db = Nothing
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occured" & 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 Occured!"
        Resume Error_Handler_Exit
    End Function

    Then the entire process can be 100% VBA based.  You could modify the above to add width ... to the Table tag or use it as is and simply throw in a Replace() to swap out <table> with whatever you need in this particular instance.


    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, February 14, 2017 6:29 PM
  • The SendKeys was simply used to add a blank line after each pasted Table.

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Hi,

    Here's what I was saying earlier, hope it makes sense. In pseudocode:

    1. Create email object

    2. Add initial message text with

    .HTMLBody = strEmailText

    3. Add the opening table HTML tag with:

    .HTMLBody = HTMLBody & "<table width='800'>"

    4. Open query

    5. Copy query result to clipboard with

    RunCommand SelectAllRecords and acCmdCopy

    6. Paste clipboard contents into the email body with

    SendKeys "^v"

    7. and so on...

    You said your original code worked except the "<table>" tag was just showing as text.

    It may be okay to see it as text as long as it displays as a table when the receiver opens the email you sent.

    By the way, I would also agree with Daniel in saying you could avoid using SendKeys altogether by "building" the email body using code, probably by looping through a recordset.

    Just my 2 cents... 

    Tuesday, February 14, 2017 6:36 PM
  • Are you assuming my tables appear at the end of the email?  They don't.

    Won't I need </table>

    I tried the below and that gave me a blank email with a blank table with a width of 800.

        DoCmd.OpenQuery "DEPARTING Flight Info", acViewNormal, acEdit
        DoCmd.SelectObject acQuery, "DEPARTING Flight Info"
        DoCmd.RunCommand acCmdSelectAllRecords
        RunCommand acCmdCopy
        DoCmd.Close acQuery, "DEPARTING Flight Info", acSaveNo
        SendKeys "^v", True
        .HTMLBody = "<table width='800'>" & HTMLBody & "</table>"
       End With

    Then I tried the below (notice the . in front of HTMLBody)  and it gave me the email with the three tables but none with the required width.

        DoCmd.OpenQuery "DEPARTING Flight Info", acViewNormal, acEdit
        DoCmd.SelectObject acQuery, "DEPARTING Flight Info"
        DoCmd.RunCommand acCmdSelectAllRecords
        RunCommand acCmdCopy
        DoCmd.Close acQuery, "DEPARTING Flight Info", acSaveNo
        SendKeys "^v", True
        .HTMLBody = "<table width='800'>" & .HTMLBody & "</table>"
       End With

    I'm really leaning towards using PreferredWidth and Distribute Columns Evenly code.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.



    • Edited by DriveEV Tuesday, February 14, 2017 7:04 PM
    Tuesday, February 14, 2017 6:59 PM
  • Have you tried using Replace?

    Something like the following at the very end after you've copied over your query data

    .HTMLBody = Replace(.HTMLBody, "<table>", "<table width=""800"">")

     

    You may also need to tweak it as the Copy/Paste process may actually include other properties in the Table tag, so you may need to adjust it slightly.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, February 14, 2017 7:18 PM
  • Are you assuming my tables appear at the end of the email?  They don't.


    Actually, no. I know exactly where your tables are located. If you follow my pseudocode, you'll see I was placing the <table> tag just before pasting the table data.

    By the way, I just realize, a HTML table also need <tr> and <td> tags. Does merely pasting a copied recordset provide those? If not, then maybe it's why you're not seeing a table in the email - because it's not a properly formatted/constructed HTML table.

    And yes, you would need a corresponding end tag such as </table>, but I was hoping you will be able to figure how to insert it if inserting the opening tag becomes a success for you.

    Just my 2 cents...

    Tuesday, February 14, 2017 8:03 PM
  • Just tried the below. I got the email with the three tables but no width changes.

        DoCmd.OpenQuery "DEPARTING Flight Info", acViewNormal, acEdit
        DoCmd.SelectObject acQuery, "DEPARTING Flight Info"
        DoCmd.RunCommand acCmdSelectAllRecords
        RunCommand acCmdCopy
        DoCmd.Close acQuery, "DEPARTING Flight Info", acSaveNo
        SendKeys "^v", True
          .HTMLBody = Replace(.HTMLBody, "<table>", "<table width=""800"">")
       End With

    Here is a picture of what I get.


    Here is a picture of what I'd like to get.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.


    • Edited by DriveEV Tuesday, February 14, 2017 8:24 PM
    Tuesday, February 14, 2017 8:09 PM
  • What about seeing the width of the heading column since you seem to insinuate the table with isn't being respected.

    What email client are you using?


    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by DriveEV Tuesday, February 14, 2017 11:54 PM
    Tuesday, February 14, 2017 8:34 PM
  • Yes that's what I ended up doing.

    What’s the saying? There’s more than one way to ….
    I was able to get the tables resized by modifying the querys.

    I use AS [_____TO_____] to expand column names to desired widths which expanded the table when pasted into the email.  Not what I was hoping to use but result was close enough.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.


    • Edited by DriveEV Tuesday, February 14, 2017 11:55 PM
    Tuesday, February 14, 2017 11:54 PM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, February 15, 2017 3:57 PM