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.
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.
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.
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.