Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HTML E-mail with Query Grid

Status
Not open for further replies.

lameid

Programmer
Jan 31, 2001
4,212
US
I have a email function to send CDO e-mails with an html body similar to the htmlbody property of an Outlook message. If I automate Outlook, I do receive the dreaded security prompt so I would prefer to avoid it.

This first bit of code opens and copies the contents of SomeQuery to the clipboard. If I then past it into Excel through the user interface, I have a formatted table which tells me it is there.

Code:
  Docmd.openquery "SomeQuery"
  DoCmd.SelectObject acQuery, "SomeQuery"

  DoCmd.RunCommand acCmdSelectAllRecords

  DoCmd.RunCommand acCmdCopy

While I realize I could write a function to read the query as a recordset and generate an HTML table in code, it seems it should be a relatively simple task to copy the query results and somehow read out the table as an HTML string that I could turn around and send as an e-mail.

Next I ran across the following on Microsoft's site for reading and writing to the clipboard. The caveat is that it comes across as just text without formatting.

Code:
Option Compare Database
Option Explicit

Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long

Public Sub SetClipboard(sUniText As String)
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Const GMEM_MOVEABLE As Long = &H2
    Const GMEM_ZEROINIT As Long = &H40
    Const CF_UNICODETEXT As Long = &HD
    OpenClipboard 0&
    EmptyClipboard
    iLen = LenB(sUniText) + 2&
    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
    iLock = GlobalLock(iStrPtr)
    lstrcpy iLock, StrPtr(sUniText)
    GlobalUnlock iStrPtr
    SetClipboardData CF_UNICODETEXT, iStrPtr
    CloseClipboard
End Sub

Public Function GetClipboard() As String
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Dim sUniText As String
    Const CF_UNICODETEXT As Long = 13&
    OpenClipboard 0&
    If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
        iStrPtr = GetClipboardData(CF_UNICODETEXT)
        If iStrPtr Then
            iLock = GlobalLock(iStrPtr)
            iLen = GlobalSize(iStrPtr)
            sUniText = String$(iLen \ 2& - 1&, vbNullChar)
            lstrcpy StrPtr(sUniText), iLock
            GlobalUnlock iStrPtr
        End If
        GetClipboard = sUniText
    End If
    CloseClipboard
End Function

So then I noticed the CF_UNICODETEXT parameter of GetClipboardData. Looking at the list of possible parameters, I do not understand what half of them mean.

Any insights to making this work somewhat easily? This is as much a curiosity to me now as anything else but would prefer to send an inline HTML table instead of an Excel attachment or looping over records.

 
I wouldn't copy and paste records from Access to Excel using code. I would probably build a query in Excel that connects to an Access query or use automation to push records from Access to Excel. Copy and Paste seems way to complex.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
lameid said:
but would prefer to send an inline HTML table instead of an Excel attachment or looping over records.

Since this is an e-mail, if Excel were desired I would just use docmd.transfertext to save the query and then send the attachment. If Mapi would work there is also docmd.sendobject - unless I am misremembering as I don't think I have used it in 10 years now.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top