Hi everyone!

I'm trying to automate a process that sends a fixed-width, text file (a
query). If I use "sendobject", it formats it as a delimited text file which
is unacceptable. Currently it is set up as a two-step process - (1) export
to desktop using an export spec, and (2) email the text file. However this
is a manual process. I need this to run at midnight. Isn't there a way
using
Access or VBA to send a fixed-width text file?

TIA

(E-Mail Removed)

Yes, there's a way, but it's not quite as easy as one
might think.

The below function can be run to export your fixed width
text file(s). This is the easy part. This function will
use the fixed width file export spec that you specify in
the strFileSpec argument. The strDestination argument
requires the fully qualified path and desired name for
your output (txt) file.

Public Function fnExportFixedWidth(strQuery As String,
strFileSpec As String, strDestination As String)
On Error GoTo Err_fnExportFixedWidth

'NOTE: Comma delimiters are included in the header
row if you choose to include
'column headers. To exclude column headers, change
the last argument to False.
DoCmd.TransferText acExportFixed, strFileSpec,
strQuery, strDestination, True

Exit_fnExportFixedWidth:
Exit Function

Err_fnExportFixedWidth:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number &
vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_fnExportFixedWidth

End Function

Regarding how to send the file as an email attachment, the
below link contains two procedures. The 2nd procedure, at
the bottom of the web page, shows how to send a file as an
Outlook email attachment. This particular procedure is
written to send a chart, but the chart references can be
removed. The msgbox text can also be modified to suit
your needs. The Access Web is an excellent web site. I
recommend that you bookmark it for future reference.

http://www.mvps.org/access/general/gen0032.htm

Assuming you are running a relatively current version of
Outlook, the above code will trigger Outlook's security
prompt which requires the user to click a button to allow
the VBA code to access protected Outlook objects. You can
use one of the below products as a solution depending on
your particular needs.

1) The good thing about Express ClickYes is that it's
free. The most significant disadvantage is that this
software stops processing commands while the Windows NT,
Windows 2000 Windows XP or Windows 2003 workstation is
locked. If your app is going to run at night, then the
workstation could very well be locked.

http://www.contextmagic.com/express-clickyes/

2) Outlook Redemption is the robust alternative. It's
free to test. To use for commercial (production)
purposes, it costs only $200. I have used this product
and it works very well. You will need to make some small
modifications to your code to take advantage of the
Redemption DLL.

http://www.dimastr.com/redemption/


>-----Original Message-----
>Hi everyone!
>
>I'm trying to automate a process that sends a fixed-
width, text file (a
>query). If I use "sendobject", it formats it as a
delimited text file which
>is unacceptable. Currently it is set up as a two-step
process - (1) export
>to desktop using an export spec, and (2) email the text
file. However this
>is a manual process. I need this to run at midnight.
Isn't there a way
>using
>Access or VBA to send a fixed-width text file?
>
>TIA
>
>(E-Mail Removed)
>
>
>.
>