Solved

How to read a text file?

Posted on 2004-08-17
22
330 Views
Last Modified: 2007-12-19
Hellow everyone,

I wanted to read a textfile into a string varialbe and then replace tokens inside the text. I tried the following 2 different methods but no success!!

Method one:
=================================
Dim mailBodyFile As Stream
   
Set textFileObject = New FileSystemObject
Set mailBodyFile = New Stream
mailBodyFile.Open

mailBodyFile.LoadFromFile "C:\mailBody.txt")
genMailBody = mailBodyFile.ReadText(adReadAll)

mailBodyFile.Close
   
Set Cnn = CurrentProject.Connection
         
strSqlResult = "SELECT DISTINCT......"
   
Set mailingMsgList = New ADODB.Recordset
mailingMsgList.Open strSqlResult, Cnn, 1
     
Do Until mailingMsgList.EOF
      Set mailMsgs = CreateObject("Outlook.Application")
        Set OutmailMsg = mailMsgs.CreateItem(olMailItem)
       
        'rename general templat to modify
        spMailBody = genMailBody
       
         'replace the tokens in textfile to personalize it
         'spMailBody = Replace(spMailBody, "[[Full-Name]]", maillingMsgList("FullName"))
         'spMailBody = Replace(spMailBody, "[{Due-Date}]", maillingMsgList("ServiceEndDate"))
               
            OutmailMsg.To = mailingMsgList("email")
            OutmailMsg.Subject = "Broadband fee due date"
            OutmailMsg.Body = spMailBody
            OutmailMsg.Display
           
        TotalMailSent = TotalMailSent + 1
        mailingMsgList.MoveNext
    Loop
=============================

This method open the file but with lots of garbage not the required text. and the replace method gives error so I commented them out!

2nd method:
===============
Dim mailBodyFile As TextStream
   
Set textFileObject = New FileSystemObject
   
Set mailBodyfil= textFileObject.OpenTextFile("C:\mailBody.txt", ForReading, True)
   
genMailBody = mailBodyFile.ReadAll

======================

This ones give error on :"genMailBody = mailBodyFile.ReadAll"

I can not figure it out.

Can some one help me please
0
Comment
Question by:Hakim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
  • 2
22 Comments
 
LVL 5

Expert Comment

by:jmacmicking
ID: 11820017
You've got a type in the second method; check the third line down.  "Set mailBodyfil" should read "Set mailBodyFile".  

To help avoid problems like this put the line:
  Option Explicit
at the very top of your code, before ANYTHING else.  This forces the compilier to error-out on any undeclared variables.  Since a variable was never declared for mailBodyfil you'd have gotten an error there (where the actual problem is) instead of the next line.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11820213
Hakim,

jmacmicking is absolutely right - Option Explicit is essential to save hours of debugging silly mistakes.

the following is hacked from code i wrote a while back to do a similar thing. I haven't run it as is but if have any problems, post them up here and I'll go through them. I originally copied this from code on the M$ knowledge base, btw.

Regards,

s46.


Public Function Import() As Integer

    Dim strBody As String, strBodyPersonalised As String
    Dim strFilePath As String
    Dim strName As String, strDueDate As String, strEMail As String
    Dim intMailCount As Integer
   
    Dim rst As DAO.Recordset
   
    Dim fs As Scripting.FileSystemObject, a As Scripting.File, s As Scripting.TextStream
    Dim otl As Outlook.MailItem
   
    strFilePath = "c:\mailbody.txt"
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.GetFile(strFilePath)
    Set s = a.OpenAsTextStream
   
    strBody = s.ReadAll
   
    'made up this bit; use whatever query you have to get the name and due date...
    intMailCount = 0
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM Customers")
    Do Until rst.EOF
        'set the personalised parameter values
        strName = rst!Name
        strDueDate = Format(rst!DueDate, "dd mmmm yyyy")
        strEMail = rst!EMail

        'replace the tokens
        strBodyPersonalised = strBody
        strBodyPersonalised = Replace(strBodyPersonalised, "[[Ful-Name]]", strName)
        strBodyPersonalised = Replace(strBodyPersonalised, "[{Due-Date}]", strDueDate)
       
        'build the outlook mailitem
        Set otl = New Outlook.MailItem
        otl.To = strEMail
        otl.Subject = "Broadband fee due date"
        otl.Body = strBodyPersonalised
        otl.SenderName = "Your Broadband Supplier"          'or whatever
        otl.Send                                            'or display if you want to show them all.
       
        'move on to the next customer
        intMailCount = intMailCount + 1
        rst.MoveNext
       
    Loop
   
    'tidy up
    Set fs = Nothing: Set a = Nothing: Set s = Nothing
    Set rst = Nothing
    Set otl = Nothing

End Function
0
 
LVL 9

Expert Comment

by:solution46
ID: 11820238
oops, you'll need to add the following line at the end to return the count of e-mails sent. Also, 'Import' isn't an overly appropriate function name in this case!

s46.

Import = intMailCount
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:Hakim
ID: 11821032
Very many thanks for the repply.

Option explicit and the varialbes you are talking about all declared but I did put it here, to make it a bit shorter. I you want I will post the entire code.

I can send email, whhat I want is to load the body of the email from file and then change the details for record in the database.

Theoritically I should be able to open the file and read it into a string varialbe and replace the tokens in the string variable as loop moves on.

The problem which I described is that: it is either compiler error or type mismatche which I can get it!


0
 
LVL 9

Expert Comment

by:solution46
ID: 11821677
Hakim,

are you still getting the same error when you run the code I posted? If so, can you post the full error description and I'll try to work out what the problem is.

Rgds,

s46.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11822717
Hakim,

following code is tried, tested and working in Access 2000.

Comments:
1. I'm using Outlook 2000 (referencing the Outlook 10 Object Library) so if you're using a different version of Outlook you may need to change this bit around.
2. I have written the following example to use DAO rather than ADO. Your code looks like this too, but if not, you will need to change the recordset round.
3. I have written a table called 'Customers', with fields [ID] (auto number, not really necessary), [Name] (string), [EMail] (string) and [DueDate] (date/time). You will need to duplicate this for my example, or change the field and table names to suit your database.
4. The filesystem object relies on the MS Scripting Runtime (scrrun.dll) so make sure you have this referenced.
5. I had to take out the otl.SnederName line as this property is read only and set by Outlook.
6. I had the format for the Set otl = line a bit wrong; it's correct in the code below.


Think that's it; good luck!

s46.




Public Function GenerateMailShot() As Integer

    Dim strBody As String, strBodyPersonalised As String
    Dim strFilePath As String
    Dim strName As String, strDueDate As String, strEMail As String
    Dim intMailCount As Integer
   
    Dim Msg$
   
    Dim rst As DAO.Recordset
   
    Dim fs As Scripting.FileSystemObject, a As Scripting.File, s As Scripting.TextStream
    Dim otl As Outlook.MailItem
   
    On Error GoTo Err_GenerateMailShot
   
    strFilePath = "c:\mailbody.txt"
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.GetFile(strFilePath)
    Set s = a.OpenAsTextStream
   
    strBody = s.ReadAll
   
    'made up this bit; use whatever query you have to get the name and due date...
    intMailCount = 0
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM Customers")
    Do Until rst.EOF
        'set the personalised parameter values
        strName = rst!Name
        strDueDate = Format(rst!DueDate, "dd mmmm yyyy")
        strEMail = rst!EMail

        'replace the tokens
        strBodyPersonalised = strBody
        strBodyPersonalised = Replace(strBodyPersonalised, "[[Full-Name]]", strName)
        strBodyPersonalised = Replace(strBodyPersonalised, "[{Due-Date}]", strDueDate)
       
        'build the outlook mailitem
        Set otl = Outlook.CreateItem(olMailItem)
        otl.To = strEMail
        otl.Subject = "Broadband fee due date"
        otl.Body = strBodyPersonalised
        otl.Display                                            'or display if you want to show them all.
       
        'move on to the next customer
        intMailCount = intMailCount + 1
        rst.MoveNext
       
    Loop
    GenerateMailShot = intMailCount
   
   
Exit_GenerateMailShot:
    'tidy up
    Set fs = Nothing: Set a = Nothing: Set s = Nothing
    Set rst = Nothing
    Set otl = Nothing
   
    Exit Function
   
   
Err_GenerateMailShot:
    Select Case Err.Number
        Case Else       'anything we're not expecting to get
            Msg$ = "" & _
                "Unexpected Error... " & vbCrLf & _
                "Name:" & strName & "; Date:" & strDueDate & "; EMail:" & strEMail & vbCrLf & vbCrLf & _
                Err.Description
               
            MsgBox Msg$, vbExclamation, "Unexpected Error"
           
            Resume Exit_GenerateMailShot
           
    End Select
           
End Function
0
 
LVL 2

Author Comment

by:Hakim
ID: 11828282
Many thanks s46. I did not test it yet, but it looks pretty much the same as my own code so far. I have to have a closer look today what is going wrong.

In original description, the first method is working, but when the file is opened it is not read-able text but all garabage!!! I thought I was doing something wrong with opening the textfile or reading it!

I did not post the whole code to make the post shorter. I think I was better off posting it, to be debuged by the board.

Have a nice day.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11828320
Hakim, there is another thing - check the text file in notepad. If it was generated by word or another program that uses its own mark-ups to sotre formatting and similar information, the file will probably be full of apparently meaningless control codes....

s46.
0
 
LVL 2

Author Comment

by:Hakim
ID: 11828589
s46,

I was thinking along a similare line, since the file was actually generated using word. What I did was open the file in notepad and saved it as txt from withing the notepad, but still it did not work which I did not get!!

0
 
LVL 2

Author Comment

by:Hakim
ID: 11828598
s46,

I will post my code for you to see where I going wrong. Just late me tidy it up a bit cuz it is every where at the moment.
0
 
LVL 2

Author Comment

by:Hakim
ID: 11828675
Hi S46, Here is my code, if you could see where gone wrong.

Sub SendEmails()

   
    'oulook related
    Dim OutmailMsg As Outlook.MailItem
    Dim outmailRecip As Outlook.Recipient
    Dim mailMsgs As Outlook.Application
   
    'database related
    Dim mailingMsgList As ADODB.Recordset
    Dim Cnn As ADODB.Connection
    Dim dueDates As Date
   
    'textfile related
    Dim genMailBody, spMailBody, strSqlResult  As String
    Dim textFileObject As FileSystemObject
    Dim nameToken, dueDateToken As String
       
    Set textFileObject = New FileSystemObject
   
    '===============================
    'this version  works but, the text file is
    'not read-able.
    '=================================
    'Open mailBoy text file method 1
    Dim mailBodyFile As Stream
    Set mailBodyFile = New Stream
    mailBodyFile.Open
    mailBodyFile.LoadFromFile ("C:\mailBody.txt")
    genMailBody = mailBodyFile.ReadText(adReadAll)
   
    '======================================
    'this version generates an eror. The error is generated is "Object required"
    'at this "genMailBody = mailBodyFile.ReadAll" point
    '======================================
   
    'Open mailBody text file method 2
    'Dim mailBodyFile As TextStream
    'Set mailBodyfil = textFileObject.OpenTextFile("C:\mailBody.txt", ForReading, True)
    'genMailBody = mailBodyFile.ReadAll
   
    If textFileObject.FileExists("c:\mailBody.txt") = False Then
        MsgBox "The file does not exist"
    End If
     
    mailBodyFile.Close
   
    Set Cnn = CurrentProject.Connection
    dueDates = Date + "7"
   
    ' Get list of people to send email to
    strSqlResult = "SELECT DISTINCT Customer.FullName, Contacts.email, Account_Transactions.ServiceEndDate " & _
          "FROM Customer, Contacts, Account_Transactions " & _
          "WHERE Account_Transactions.ServiceEndDate=#" & dueDates & "# AND Contacts.CustomerRef=Customer.Id AND Customer.ID = Account_Transactions.CustomerRef;"
   
    Set mailingMsgList = New ADODB.Recordset
    mailingMsgList.Open strSqlResult, Cnn, 1
   
   '================================
   'There is an error generated here!
   '================================
    If (mailingMsgList = Null) Then
        MsgBox ("There is no one to email")
        Exit Sub
    End If
   
    Do Until mailingMsgList.EOF
        Set mailMsgs = CreateObject("Outlook.Application")
        Set OutmailMsg = mailMsgs.CreateItem(olMailItem)
       
        'rename general templat to modify
         spMailBody = genMailBody
       
        '=====================================
        ' there is an error generated here. the error is "type mismatch"
        '=====================================
         'replace the tokens in textfile to personalize it
         'spMailBody = Replace(spMailBody, "[[Full-Name]]", maillingMsgList("FullName"))
         'spMailBody = Replace(spMailBody, "[{Due-Date}]", maillingMsgList("ServiceEndDate"))
                 
            OutmailMsg.To = mailingMsgList("email")
            OutmailMsg.Subject = "Broadband fee due date"
            OutmailMsg.Body = spMailBody
            OutmailMsg.Display
           
        mailingMsgList.MoveNext
    Loop

Set mailMsg = Nothing
mailingMsgList.Close
Set mailingList = Nothing
Set OutmailMsg = Nothing
 
End Sub

waiting for your comments
0
 
LVL 9

Accepted Solution

by:
solution46 earned 125 total points
ID: 11828889
Hakim,

I've gone through your code and added the comments below. Note, however, that the majority of your errors are typos in variable names that would have been picked up at compilation if you had Option Explicit declared at the top of the module. If you had followed jmacmicking's advice this problem would have been resolved yesterday.

To make sure Option Explicit is always set, check the 'Require Variable Declaration' option in VB.

Your corrected code follows....

Regards,

s46.





Sub SendEmails()

   
    'oulook related
    Dim OutmailMsg As Outlook.MailItem
    Dim outmailRecip As Outlook.Recipient
    Dim mailMsgs As Outlook.Application
   
    'database related
    Dim mailingMsgList As ADODB.Recordset
    Dim Cnn As ADODB.Connection
    Dim dueDates As Date
   
    'textfile related
    Dim genMailBody, spMailBody, strSqlResult  As String
    Dim textFileObject As FileSystemObject
    Dim nameToken, dueDateToken As String
       
    Set textFileObject = New FileSystemObject
   
    '===============================
    'this version  works but, the text file is
    'not read-able.
    '=================================
    'Open mailBoy text file method 1
   
    '****** s46: Not familiar with this stream object, but you're right - it doesn;t work!
'    Dim mailBodyFile As Stream
'    Set mailBodyFile = New Stream
'    mailBodyFile.Open
'    mailBodyFile.LoadFromFile ("C:\mailBody.txt")
'    genMailBody = mailBodyFile.ReadText(adReadAll)
   
    '======================================
    'this version generates an eror. The error is generated is "Object required"
    'at this "genMailBody = mailBodyFile.ReadAll" point
    '======================================
   
    'Open mailBody text file method 2
    Dim mailBodyFile As TextStream
   
    ' ****** s46: Error here has already been pointed out in a previous reply...
    ' ****** s46: mailbodyfil should be mailBodyFile
    'Set mailbodyfil = textFileObject.OpenTextFile("C:\mailBody.txt", ForReading, True)
    Set mailBodyFile = textFileObject.OpenTextFile("C:\mailBody.txt", ForReading, True)
    genMailBody = mailBodyFile.ReadAll
   
    If textFileObject.FileExists("c:\mailBody.txt") = False Then
        MsgBox "The file does not exist"
    End If
     
    mailBodyFile.Close
   
    Set Cnn = CurrentProject.Connection
    dueDates = Date + "7"
   
    ' Get list of people to send email to
    strSqlResult = "SELECT * FROM Customers;"
   
    Set mailingMsgList = New ADODB.Recordset
    mailingMsgList.Open strSqlResult, Cnn, 1
   
   '================================
   'There is an error generated here!
   '================================
   
   '****** s46 - the error here is mailingMsgList is an object and cannot be Null.
   'You can try checking if it is Nothing, or a neater way is to check the recordset
   'i have commented this section out.
    'If (mailingMsgList = Null) Then
    '    MsgBox ("There is no one to email")
    '    Exit Sub
    'End If
   
    If mailingMsgList.EOF And mailingMsgList.BOF Then
        MsgBox ("There is no one to email")
        Exit Sub
   
    End If
   
   
    Do Until mailingMsgList.EOF
        Set mailMsgs = CreateObject("Outlook.Application")
        Set OutmailMsg = mailMsgs.CreateItem(olMailItem)
       
        'rename general templat to modify
         spMailBody = genMailBody
       
        '=====================================
        ' there is an error generated here. the error is "type mismatch"
        '=====================================
         'replace the tokens in textfile to personalize it
         
         ' ****** s46: Thr problem with both of these lines is the mis-spelled mailingMsgList!!!!
         'spMailBody = Replace(spMailBody, "[[Full-Name]]", maillingMsgList("FullName"))
         'spMailBody = Replace(spMailBody, "[{Due-Date}]", maillingMsgList("ServiceEndDate"))
         spMailBody = Replace(spMailBody, "[[Full-Name]]", mailingMsgList("FullName"))
         spMailBody = Replace(spMailBody, "[{Due-Date}]", mailingMsgList("ServiceEndDate"))
                 
                 
                 
            OutmailMsg.To = mailingMsgList("email")
            OutmailMsg.Subject = "Broadband fee due date"
            OutmailMsg.Body = spMailBody
            OutmailMsg.Display
           
        mailingMsgList.MoveNext
    Loop


'****** s46: you don't have mailMsg defined anywhere!
'Set mailMsg = Nothing

'****** s46: Note, if the rst has failed to open, this will return an error.
mailingMsgList.Close

'****** s46: you don't have mailingList defined anywhere - this should be mailingMsgList
'Set mailingList = Nothing
Set mailingMsgList = Nothing

Set OutmailMsg = Nothing
 
End Sub
0
 
LVL 2

Author Comment

by:Hakim
ID: 11828927
Hi S46,

Miss spelling lol What else do youexpect from halfblind? lol

Thanks a lot I have go check everything again.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11828945
just replace it with the code above. you'll need to change the query string back but apart from that it will work fine.

just make sure you have that Option Explicit line in; if it is there the compiler wont let the code run and it will report all the mis-spelled variables as 'Variable Not Defined' or something similar. That should save you loads of time trying to figure out what's wrong with it as it runs.

s46.
0
 
LVL 2

Author Comment

by:Hakim
ID: 11829077
HI S46,

Cross ref the variables for spelling and yet it was miss spelling of the variables.

It seems i always have problme with my SPALLING.1 LOL

0
 
LVL 9

Expert Comment

by:solution46
ID: 11829185
glad it worked.

s46.
0
 
LVL 2

Author Comment

by:Hakim
ID: 11880034
Hello S46,

It did work well, but Now I am stack somewherelse. I want to update a filed but it gives me a run time error. I hope you could help me there.

I want update a flag field when the email is sent. Since the record set I am working with is result of a querry from three differen talbes and I am updating only one field in one talbe, I can not figure how to use the udate command!

Currently the code is like this:
 mailingMsgList.Update ("Sent" = 1)

It gives me runtime error: "type mismatch"




0
 
LVL 9

Expert Comment

by:solution46
ID: 11884067
mailingMsgList.Edit
mailingMsgList!Sent = 1
mailingMsgList.Update

should do it.

s46.
0
 
LVL 2

Author Comment

by:Hakim
ID: 11901013
Thanks,

edit method is ot supported in ado.

However, I used mailingMsgList("sent")= True, this gives me an error i.e. "Database or Object is read-Only"

I tried to change the parameter in open method but with no success.

I used forwaredonly, keyset, and static cursor and I also used pessimistic, optimistic locks non seems to work.

0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 11901661
Some query types (DISTINCT being one of them) are not updatable.  Since one record on the query could represent multiple source records Access disables updates.
0
 
LVL 2

Author Comment

by:Hakim
ID: 11901729
It happens that I used distinct in the query. But if I don't use it, i will end up with multiple recorde from the same customer cuz I am get the result from morethen one record.

What is the work round it?

I find some info on MSDN site which says I can use the following code to for making the record set editable.

"Attribute VB_Name = "modEditingData" "

Above code gives error on its own, and I don't know where/how to use it.

0
 
LVL 2

Author Comment

by:Hakim
ID: 11901747
Very many thanks it actually was the "Distinct" part of the query.

It is working fine now
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question