Access 2007 - vb code

Posted on 2011-04-24
Last Modified: 2012-05-11
my OS is windows 7 and I use Access 2007, can a guru plese debug the attached code as it is bombing at
Open CurrentProject.Path & "\EmailList.txt" For Output
At the directory, I have deleted the old Emaillist.txt files, so as to make life easier.
thank u
Private Sub mailemailreport_Click()
Dim rst As DAO.Recordset
Dim strDistList As String
Set rst = CurrentDb.OpenRecordset("Seniors Club")

Do While Not rst.EOF
    strDistList = strDistList & rst!Email & ";"
If MsgBox("Print Email distribution list to file?", vbYesNo, "Print?") = vbYes Then
  Dim lFile As Long
  '/get the next available File handle
  lFile = FreeFileHandle
  '/delete the file if it exists
  If Dir(CurrentProject.Path & "\EmailList.txt") <> "" Then Kill CurrentProject.Path & "\EmailList.txt"
  '/Now open the file
  Open CurrentProject.Path & "\EmailList.txt" For Output As #lFile
  '/write to that file
  Print #lFile, strDistList
  '/now close the file
  Close #lFile
    strDistList = Left(strDistList, Len(strDistList) - 1)

    Me.Text46 = strDistList
End If


Set rst = Nothing

End Sub

Open in new window

Question by:jegajothy

    Author Comment

    Sorry to add the following o the above, at the following line what is the syntax to add a CR after each email.
    strDistList = strDistList & rst!Email & ";"

    thank u.
    LVL 119

    Assisted Solution

    by:Rey Obrero
    strDistList = strDistList & rst!Email & ";" & vbcr


    strDistList = strDistList & rst!Email & ";" & vbcrlf
    LVL 21

    Accepted Solution

    Try changing

    lFile = FreeFileHandle


    lFile = FreeFile

    MS DOS Text files use CR and LF as a record terminator. You probably want to use capricorn1's second suggestion

    strDistList = strDistList & rst!Email & ";" & vbcrlf

    Author Closing Comment

    thank u both, it worked.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    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.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    729 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

    21 Experts available now in Live!

    Get 1:1 Help Now