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

[Pasi]

HI,



I am trying to run a script to look for the latest file in the path: "C:\Users\me\Desktop\Reports\ and grab the latest file and email it to recipient? Right now my script out put the .txt to a the folder but can figure out how to send that file as attachment?
Thanks,
Pasi

[ItsMe]

Do you need to look for file properties like modified date or date ctreated? You state that you created a file and you need to email that file. If that is the case use the same path that created the file to add an attachment to your email code.

[June7]

Attaching external files to email requires VBA code that opens Outlook as an object and manipulates that object. Fairly common topic in forum. Here is one https://www.accessforums.net/access/...ect-17713.html

Why do you save textfile? Is this a query that is exported? DoCmd.SendObject will attach a query as a text file and send email.

[Pasi]

Hello June7,

Yes this is the exported file to a folder using DoCmd.TransferText acExportDelim, so you saying there is a command to send this as email?

[June7]

Explore the SendObject method, otherwise the other code in referenced thread.

[Pasi]

Yes I need file properties to look for latest file withd ate. right now my out put file is stamp with the date like "C:\Users\me\Desktop\Reports\myfile-" & Format(Date, "mm-dd-yyyy") & ".txt"

[Pasi]

What is the right command to use SendObject method can you give me an example?
Thanks.!

[June7]

The SendObject will not allow you to name the attachment.

Access/VBA Help has guidelines on SendObject method. Examples are all over the web.

[Pasi]

Ok Thanks.

[ItsMe]

Here is some example code for SendObject. I have not tested the acFormatTXT but I believe this is correct.

Dim strSubject As String
Dim strBody As String
Dim strReport As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
strSubject = "Insert Subject Line Here"
strBody = "Insert text to be displayed in the email body"
strReport = "rptEquip"
strTo = "you@gmail.com"
strCc = ""
strBcc = "me@gmail.com"
DoCmd.SendObject acSendReport, strReport, acFormatTXT, strTo, strCc, strBcc, strSubject, strBody, True

[Pasi]

Thank you! Do I put this code into my current Access code or create a .vbs file to include this in it?

[June7]

Code is for Access, maybe button Click event. A .vbs file would not be useful.

[ItsMe]

Why are you considering running script? How would this benefit you? I would do ALL of the work from Access and take advantage of the available libraries in Access.

[Pasi]

Thank you so much!

[Pasi]

BTW, this code works file but the output is a text column delimited, Do you know of a way to set the format to "Text delimited? I tried it but I get a popup asking for format?