• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Access 2007 - vb code

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 & ";"
    rst.MoveNext
Loop
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
Else
    strDistList = Left(strDistList, Len(strDistList) - 1)

    Me.Text46 = strDistList
End If

    

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
jegajothy
Asked:
jegajothy
  • 2
2 Solutions
 
jegajothyretiredAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
strDistList = strDistList & rst!Email & ";" & vbcr

or

strDistList = strDistList & rst!Email & ";" & vbcrlf
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try changing

lFile = FreeFileHandle

to

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
0
 
jegajothyretiredAuthor Commented:
thank u both, it worked.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now