Need help to email a .txt file as attachment from access

[June7]

No, I believe that is a limitation of SendObject.

[ItsMe]

I don't know of an acformat that would dictate delimited, qualifiers and such. I would use DoCmd.TransferText acExportDelim to save a file and then some code to attach the file. June provided a link to create an Outlook object and use Outlook to attach docs and send emails. Another option would be to use CDO's schemas.

[Pasi]

OK Thank! I wish I could do it with ms access but only for one limitation I have to turn around and do extra work. Ah... MS never makes thing easy. why not text delimited option!

[ItsMe]

OK Thank! I wish I could do it with ms access but only for one limitation I have to turn around and do extra work.

Not sure I understand what the extra work is about. Yes, you will have to create a text file becuase the built in method for SendObject will not know how to format the Delimited file. You can use the export wizard and save the procedure with a meaningful name. With the saved export procedure you can include the TransferText method. So not a lot of work really.

I believe it would be something like this
DoCmd.TransferText acExportDelim, "SavedProcedure", "AccessObjectName", "Path"

[Pasi]

Sorry I am not following you? Don't mean to act dumb!

[June7]

By 'extra work' do you mean the procedure that manipulates an Outlook object? Yes, it is a bit more code but not so difficult with the adequate examples provided.

I just reread the first post. You said you are trying to run a script to find latest file in a folder. Why? If you just ran Access procedure that saved a text file then you have the file name available for use in subsequent code to send it as attachment to email.

[Pasi]

Thanks. Yes at first I was trying to email it with .vbs code cause could not find a way to export the file from access as text delimited so I had to export it first and use a vbs method to email it. I am not too familiar with access to save the procedure? Sorry I am just learning access.

[June7]

You said your 'script out put the .txt to a the folder' - what script? This is not a VBA procedure in the Access file? Post the code you are using.


Maybe this will help http://office.microsoft.com/en-us/ac...010341717.aspx

[Pasi]

I meant I have a vb/module script within my access file that exports my executed file into a my folder see below vba codes: this code exports the output file as text delimited with the time stamp and date file that I want. Now I just need a way to access this file in this folder---> "C:\Users\xxx\Desktop\Reports\Press-" & Format(Date, "mm-dd-yyyy") & ".txt" to send email as attachment to me.

Public Function ConvertToText()
DoCmd.TransferText acExportDelim, , "My file", "C:\Users\xxx\Desktop\Reports\Press-" & Format(Date, "mm-dd-yyyy") & ".txt"
End Function

[June7]

Consider:

Public Function ConvertToText()
'export data to text file and send email
Dim strFile As String
strFile = "C:\Users\xxx\Desktop\Reports\Press-" & Format(Date, "mm-dd-yyyy") & ".txt"
DoCmd.TransferText acExportDelim, , "My file", strFile
'more code here to open Outlook object and send email with strFile as attachment
End Function

[Pasi]

Just wanted to thank you helping me with this! I am all set ! I was able to tailor the code to generate email and worked out some bug. I am up and running! Thanks again! You and June are great!

[ItsMe]

Glad to hear you got it sorted out. Once you have some code examples in your files, it is not too difficult to implement in future projects.

[Pasi]

Absolutely. That's what I was looking for. Thanks again!

[Pasi]

Sorry I have to ask you this but when I run this code

strFile = "C:\Users\xxxx\Desktop\Reports\Press-" & Format(Date, "mm-dd-yyyy") & ".txt"
DoCmd.TransferText acExportDelim, , "Myquery", strFile

this runs fine but when I change the format date to & Format(Date, "mm/dd/yyyy") & ".txt" I get an error saying not a valid path???

Any idea?
Thanks,

[June7]

Can't use / character in the file name.