How to read a text file?

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.

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

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

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!

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.

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

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.

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.

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

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.

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

Hi S46,

Miss spelling lol What else do youexpect from halfblind? lol

Thanks a lot I have go check everything again.

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.

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

glad it worked.

s46.

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"

mailingMsgList.Edit
mailingMsgList!Sent = 1
mailingMsgList.Update

should do it.

s46.

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.

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.

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.

Very many thanks it actually was the "Distinct" part of the query.

It is working fine now