Solved

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

Posted on 2008-10-13
17
434 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now