Solved

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

Posted on 2008-10-13
17
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
question about results where i dont have a match 3 36
Setting variables in a stored procedure 5 53
SQL Server syntax 11 41
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

696 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