Exporting Access data to Text file

  • Thread starter Thread starter Mat
  • Start date Start date
M

Mat

Could anyone help with a simple answer please ?

I have an Access db that schedules the sending of emails
with attachments.
I run a query on the table that returns all active users
I then need to create a separate text file for each
active user - in the format as shown below.
Each file is called "ddmmXX.epe"
- where dd=day,mm=month,XX=sequential number starting 01.
The file needs to be formatted ascii text file.
The file extension must be .epe

FORMAT:text
TO:{email}
FROM:{from}
CC:{cc}
SUBJECT:{subject}
ATTACHMENT:{attachment}
BODY:{Message}

All the fields within the {} are fields in the Access
table
Each file must then be saved to a directory c:\mail\out
(eg C:\mail\out\141201.epe)

Sample file would look like this:

FORMAT:text
TO:{[email protected]}
FROM:{[email protected]}
CC:{[email protected]}
SUBJECT:{Daily Newsletter}
ATTACHMENT:{c:\mail\news.zip}
BODY:{Please find attached today's newsletter}


Can anyone help - it would be much appreciated, as I am
still learning the slightly more advanced methods of VBA.

Thanks
 
Mat,

Here's some sample code to do it:

Sub export_text_files()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("MyQuery")
If rst.RecordCount = 0 Then
MsgBox "Sorry, no records found.", vbExclamation
GoTo end_process
End If
ctr = 1
dt = Format(Date, "ddmm")
rst.MoveFirst
Do Until rst.EOF
fn = dt & Format(ctr, "00") & ".epe"
Open "C:\mail\out\" & fn For Output As #1
Print #1, "FORMAT:text"
Print #1, "TO:" & rst.Fields("To")
Print #1, "FROM:" & rst.Fields("From")
Print #1, "SUBJECT:" & rst.Fields("Subject")
Print #1, "ATTACHMENT:" & rst.Fields("Attachment")
Print #1, "BODY:" & rst.Fields("Body")
Close #1
rst.MoveNext
ctr = ctr + 1
Loop
end_process:
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

This code assumes the adta comes from a query called MyQuery, containing
the fields To, From, Subject, Attachment, Body. Change the query and
field names to the actual ones.
Note: To run this code, it is required to have an appropriate DAO Object
Library reference. While in the VB editor window, go to menu item Tools
References; check if a Microsoft DAO reference is present among the
Click to expand...
ones checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
Back
Top