• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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?
0
kalbal
Asked:
kalbal
  • 10
  • 7
1 Solution
 
tchamtiehCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now