How to create / delete notepad files through VBA code in MS Access

Hi
I have a MS access 97 - 2003 application that does the following-
uses VBA code to run a query,
writes results of the query to a .txt file on my C drive

My 2 ques are -
What is the VBA code for sending these .txt files in an email?
After the file is sent in an email, how can i delete the .txt file from my C drive?
kalbalAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
tchamtiehConnect With a Mentor Commented:
Check out this website, seems to answer your question and has some code you could use as well.

http://www.tek-tips.com/viewthread.cfm?qid=1068393&page=1

As far as deleting the file when you're done, just use the kill() function:

Kill ("C:\filename.txt")

Good Luck.
0
 
kalbalAuthor Commented:
ok great, but my application can generate any no of .txt files on C drive, i.e. practically speaking there can be upto 10files, so if i safely code for max 10 files and system doesnt find a file (which doesnt exist) it gives an error...
any idea?
0
 
kalbalAuthor Commented:
thats ok i got this *.txt
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tchamtiehCommented:
You got it, use "Kill ("C:\*.txt")". Hope all works out.
0
 
kalbalAuthor Commented:
ok i read the link that you've sent me, it is useful, except that i face the same problem that the user there was facing, i.e. "USERDEFINED TYPE NOT DEFINED", on the line 'Dim oOutlookApp As Outlook.Application'.

I have made sure that MICROSOFT OFFICE 12.0 OBJECT LIBRARY HAS BEEN ADDED IN REFERNCES, still no good, anythign else i can try?
0
 
tchamtiehCommented:
See if this helps: From the VBA editor check tools > references > Microsoft Outlook object library
0
 
kalbalAuthor Commented:
yeah that did Microsoft Outlook 11.0 Object library. THanks ...
But now the problem is there is no attachment coming with the email..
code is...

Function Send_Mail_with_attachment()
 
Dim bStarted As Boolean
Dim oOutlookApp As New Outlook.Application
Dim oItem As Outlook.MailItem
 
On Error Resume Next
 
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err = 429 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If
 
Set oItem = oOutlookApp.CreateItem(olMailItem)
 
With oItem
    .To = my email address here
    'Set the subject
    .Subject = "Electronic Invoice for " & Date
    'Add the document as an attachment, you can use the .displayname property
    'to set the description that's used in the message
    .Attachments.Add Source:="Y:\Yarra Valley Farms Retail\EDI\RetailElectInvoice0.txt", Type:=olByValue
    .Send
End With
 
If bStarted Then
    oOutlookApp.Quit
End If
 
Set oItem = Nothing
Set oOutlookApp = Nothing
 
  
    
End Function

Open in new window

0
 
tchamtiehCommented:
.Attachments.Add Source:="Y:\Yarra Valley Farms Retail\EDI\RetailElectInvoice0.txt", Type:=olByValue

should look like

.Attachments.Add "Y:\Yarra Valley Farms Retail\EDI\RetailElectInvoice0.txt"

0
 
kalbalAuthor Commented:
still no good
0
 
tchamtiehCommented:
It should actually work, what does your code look like now? show me your code.
0
 
kalbalAuthor Commented:
attached code
Function Send_Mail_with_attachment()
 
Dim bStarted As Boolean
Dim oOutlookApp As New Outlook.Application
Dim oItem As Outlook.MailItem
 
On Error Resume Next
 
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err = 429 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If
 
Set oItem = oOutlookApp.CreateItem(olMailItem)
 
With oItem
    .To = my email id
    'Set the subject
    .Subject = "Electronic Invoice for " & Date
    'Add the document as an attachment, you can use the .displayname property
    'to set the description that's used in the message
    .Attachments.Add "Y:\Yarra Valley Farms Retail\EDI\RetailElectInvoice0.txt"
    .Send
End With
 
If bStarted Then
    oOutlookApp.Quit
End If
 
Set oItem = Nothing
Set oOutlookApp = Nothing
     
End Function
 

Open in new window

0
 
kalbalAuthor Commented:
the email comes through but with no attachment
0
 
kalbalAuthor Commented:
ok, sorry, i made a silly mistake this time, the path is incorrect
0
 
kalbalAuthor Commented:
Thanks so much tchamtieh. I am just testing the whole app once again and will close the ques if no issues..thanks again
0
 
tchamtiehCommented:
Code looks good, I don't understand why it's not sending, try to debug to see what's going on, comment out the "On Error Resume Next" as well to see if you get any errors.
0
 
tchamtiehCommented:
No problem, you're welcome.

-Thomas
0
 
kalbalAuthor Commented:
THANKS SO MUCH FOR HELPING!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.