How to get a report as txt into a string? 1

[Loekje]

Hi,

I'm trying to send a report via e-mail (GroupWise). The various interfaces makes it relatively easy to send a report as attachment. But what I want is to send a report in the body of the e-mail. GroupWise token sendmail(MessageIsFile) is badly broken, so that path is cut off.

A workaround is to send it via the SMTP gateway with a commandline SMTP mailer like BLAT. Drawback is that this introduces yet another 3rd party software.

But if anyone of you knows an elegant way to
get a report (acFormatTXT) into a string variable? All the GroupWise interfaces expect a string variable as message body...

TIA,
Loek Gijben

 

[kathryn]

Loek,

At a very high level, how does this sound:

Create the report in Access.
Send it to Word.
Open it in Word.
Select all in Word.
Copy in Word
Paste into the email message body.

Totally taking a different approach, have you looked at the Snapshot Viewer?
Here is a KB article (Q172348) about it:

http://support.microsoft.com/support/kb/articles/Q172/3/48.asp

We use Snapshot viewer to email Access reports all over the place. Kathryn

 

[Loekje]

Thanks Katryn, but no thanks...

Your solution sounds like more like a kludge than using the commandline SMTP mailer.
I take it that there are no easy ways to get a txt file into a string variable?

Cheers!
Loek

 

[kathryn]

Not that I can think of. All the solutions I can come up with are really only getting the path or name of the textfile, not the actual file itself. Kathryn

 

[MichaelRed]

but if you know the path\name of the file (AND it is really a text file) just open it for input and read in the lines until you are at the EOF. As a STRING variable it is (of course) limited to 64K characters, however if you REALL need to have the possability of more, you could make it a memo filed, or an array of string fields ...

OBVIOUSLY, I don't understand something here, because it is just to easy!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Loekje]

Hi MichaelRed,

I'm not a real programmer (ah well, in assembler for microcontrollers, and that's a different sport) so it may be just tooo easy for you.

I've seen only snippets of code dat read in a character at a time. Can you point me to some code, or if it's really that simple give me the few lines that read in a 2kB txt file into a string??

In return I'll post the various solutions to mailing from MS-Access through GroupWise.

Thanks in advance,
Loek Gijben

 

[MichaelRed]

This is NOT intended to be a "soloution". It needs more error checking and some elaboration, depending on your specifics. Items you DO need to consider even to "try" it in exploring VB.

1.[tab]you MUST supply the FULL path and filename. the procedure does not include any facillity for any other file retrieval mechanism.

2.[tab]As shown below, the procedure will NOT retain Line Breaks. If you want to retain these, you need to 'uncomment' the part of the line to "re-Append" to each line as it is read in.

3.[tab]Thei procedure is a FUNCTION. The accumulated string is returned by the FUNCTION. To "set a text box to display this, do something like:

mytextbox = basReadText(FileName ) where FileName is the path\name of your file. Not that the text box would need to be "MultiLine"

Public Function basReadText(FileName As String) As String

    Dim MyFil As Integer
    Dim LineIn As String
    Dim MyText As String
    Dim MyMsg As String
    Dim MyTitle As String

    If (Len(FileName) = 0) Then
        MyMsg = "You MUST specify a file to read from"
        MyTitle "Read Text"
        MsgBox MyMsg, vbCritical, MyTitle
        Exit Function
    End If

    MyFil = FreeFile
    Open FileName For Input As #MyFil
    While Not EOF(MyFil)
        Line Input #MyFil, LineIn
        MyText = MyText & LineIn            '& vbcrlf   '<== Add this to retain Linefeeds
    Wend

    basReadText = MyText

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[booyakkajoe]

Here's a snippet I picked up somewhere, just place it in a module.
You shouldn't have to worry about adding or removing carrige returns.

example
mytextfilestring = ReadFile("C:\myexportedreport.txt&quot


Function ReadFile(ByVal sFileName As String) As String
'Reads file into string
Dim fhFile As Integer
fhFile = FreeFile
Open sFileName For Binary As #fhFile
ReadFile = Input$(LOF(fhFile), fhFile)
Close #fhFile
End Function

 

[mflancour]

Hey Booyakkajoe. Great bit of code there, thanks! It works great for me.

I was actually going to suggest he import the file to access first then use the bit I have below but yours is way easier/better.

Dim BodyString As String
Dim MyDB As Database, Mytable As Recordset, MyTable2 As Recordset ', StrSql
Set MyDB = DBEngine.Workspaces(0).Databases(0)

Set MyTable2 = MyDB.OpenRecordset("concatenate query name&quot ' Open table.
BodyString = ""
MyTable2.MoveFirst
Do Until MyTable2.EOF
BodyString = BodyString & Chr(10) & MyTable2![concatenated field]
'Use a query to concatenate the
'imported fields into one string using chr(9) to seperate
'fields
MyTable2.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , _
"Address;second address;.....", , , _
"Subject Line here", "string blurb in body here" & Chr(10) & "Field1 heading" & Chr(9) & "Field2 heading" & Chr(9) & "Field3 heading" & Chr(10) & BodyString, True