Solved

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

Posted on 2008-10-13
17
435 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

803 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