[AccessD] Embed Query Results in an Email

Well, I wrote a little function that accepts a value, converts it to
text (if necessary), and pads and justifies it according to the
requirements of the layout.  I just pass each column's value (or header
literal) to the function along with its formatting specs, and
concatenate the results together for each row.  Terminate each row with
a CrLf as you go, and there you have it.  

Here's my function.  It works well in my controlled application, but you
might want to modify it to handle values longer than MaxLen.  

Function PadText(varValue As Variant, strDataType As String, lngMaxLen
As Long, strJustify As String) As String
'-----------------------------------------------------------------------
----------------
' Procedure : PadText
' Purpose   : builds a string from the passed value per the passed specs
' Arguments : varValue = the value to be processed
'             strDataType = the type of data being passed - acceptable
values = Text, Num, Date
'             lngMaxLen   = the maximum number of characters allowed for
the resulting string
'             strJustify  = the justification spec for the resulting
string - acceptable values = L, C, or R
' Returns   : The input value formatted as desired
'-----------------------------------------------------------------------
----------------
'
On Error GoTo ErrorHandle 
    Dim strResult As String, blnError As Boolean
    If lngMaxLen <> 0 Then
        Select Case strDataType
            Case "Text"
                strResult = varValue
            Case "Num"
                strResult = Format(varValue, "#,###")
            Case "Date"
                strResult = Format(varValue, "mm/dd/yyyy hh:nn:ss")
            Case Else
                blnError = True
        End Select
        If blnError = False Then
            Select Case strJustify
                Case "L"
                    strResult = strResult & Space(lngMaxLen -
Len(strResult))
                Case "C"
                    strResult = Space(Int((lngMaxLen - Len(strResult)) /
2)) _
                        & strResult & Space((lngMaxLen - Len(strResult))
- Int((lngMaxLen - Len(strResult)) / 2))
                Case "R"
                    strResult = Space(lngMaxLen - Len(strResult)) &
strResult
                Case Else
			  blnError = True
            End Select
        End If
    Else
        blnError = True
    End If
    If blnError = False Then PadText = strResult Else PadText = "Error"
FunctionExit:
    Exit Function
ErrorHandle:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " " & Err.Description & vbCrLf & vbCrLf
_
                & "Error in Function 'PadText' of Module
'basSendCycleConfirmations'."
    End Select
    Resume FunctionExit
End Function



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie
Johnson
Sent: Tuesday, November 29, 2005 3:47 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Embed Query Results in an Email


How do you go about formatting and doing headers?

"Mcgillivray, Don [IT]" <Donald.A.McGillivray at sprint.com> wrote:
Lonnie,

I've done something similar to what you are trying to do. In my app, I
send out daily activity summaries using a procedure that opens a
recordset and steps through the resulting rows, formatting and appending
the values to a string variable. The procedure adds column headers and
explanatory text to the string before using it as the body of an email
message that is sent using "Blat." In my case, the process is triggered
automatically once a day, but you could just as easily put something
like this on a button.

HTH,

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie
Johnson
Sent: Tuesday, November 29, 2005 3:07 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Embed Query Results in an Email


The two queries hold the results of stats for case workers that are
mailed to the whole company. The requestor was wanting two embedded
tables vs attachements. The two queries are both three columns wide and
never have any more than 15 rows. It's inner office so everyone can read
HTML format.

Thanks for the response.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us





 





		
---------------------------------
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com