Solved

How to read a text file?

Posted on 2004-08-17
22
282 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
  • 11
  • 9
  • 2
22 Comments
 
LVL 5

Expert Comment

by:jmacmicking
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:Hakim
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 9

Accepted Solution

by:
solution46 earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
glad it worked.

s46.
0
 
LVL 2

Author Comment

by:Hakim
Comment Utility
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
Comment Utility
mailingMsgList.Edit
mailingMsgList!Sent = 1
mailingMsgList.Update

should do it.

s46.
0
 
LVL 2

Author Comment

by:Hakim
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Very many thanks it actually was the "Distinct" part of the query.

It is working fine now
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now