Solved

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

Posted on 2008-10-13
17
436 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:kalbal
  • 10
  • 7
17 Comments
 
LVL 6

Accepted Solution

by:
tchamtieh earned 500 total points
ID: 22707610
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
 

Author Comment

by:kalbal
ID: 22707688
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
 

Author Comment

by:kalbal
ID: 22707700
thats ok i got this *.txt
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 6

Expert Comment

by:tchamtieh
ID: 22707732
You got it, use "Kill ("C:\*.txt")". Hope all works out.
0
 

Author Comment

by:kalbal
ID: 22707967
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
 
LVL 6

Expert Comment

by:tchamtieh
ID: 22708033
See if this helps: From the VBA editor check tools > references > Microsoft Outlook object library
0
 

Author Comment

by:kalbal
ID: 22708161
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
 
LVL 6

Expert Comment

by:tchamtieh
ID: 22708186
.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
 

Author Comment

by:kalbal
ID: 22708195
still no good
0
 
LVL 6

Expert Comment

by:tchamtieh
ID: 22708391
It should actually work, what does your code look like now? show me your code.
0
 

Author Comment

by:kalbal
ID: 22708444
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
 

Author Comment

by:kalbal
ID: 22708449
the email comes through but with no attachment
0
 

Author Comment

by:kalbal
ID: 22708474
ok, sorry, i made a silly mistake this time, the path is incorrect
0
 

Author Comment

by:kalbal
ID: 22708482
Thanks so much tchamtieh. I am just testing the whole app once again and will close the ques if no issues..thanks again
0
 
LVL 6

Expert Comment

by:tchamtieh
ID: 22708498
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
 
LVL 6

Expert Comment

by:tchamtieh
ID: 22708501
No problem, you're welcome.

-Thomas
0
 

Author Closing Comment

by:kalbal
ID: 31505729
THANKS SO MUCH FOR HELPING!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA name newly created sheet 4 32
SQL Error - Query 6 41
Copy a range from 1..n excel sheets to one destination sheet 2 56
Filtering characters in an SQL field 2 16
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn the different options available in the Backstage view in Excel 2013.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question