kalbal
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thats ok i got this *.txt
You got it, use "Kill ("C:\*.txt")". Hope all works out.
ASKER
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?
I have made sure that MICROSOFT OFFICE 12.0 OBJECT LIBRARY HAS BEEN ADDED IN REFERNCES, still no good, anythign else i can try?
See if this helps: From the VBA editor check tools > references > Microsoft Outlook object library
ASKER
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...
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
.Attachments.Add Source:="Y:\Yarra Valley Farms Retail\EDI\RetailElectInvo ice0.txt", Type:=olByValue
should look like
.Attachments.Add "Y:\Yarra Valley Farms Retail\EDI\RetailElectInvo ice0.txt"
should look like
.Attachments.Add "Y:\Yarra Valley Farms Retail\EDI\RetailElectInvo
ASKER
still no good
It should actually work, what does your code look like now? show me your code.
ASKER
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
ASKER
the email comes through but with no attachment
ASKER
ok, sorry, i made a silly mistake this time, the path is incorrect
ASKER
Thanks so much tchamtieh. I am just testing the whole app once again and will close the ques if no issues..thanks again
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.
No problem, you're welcome.
-Thomas
-Thomas
ASKER
THANKS SO MUCH FOR HELPING!!
ASKER
any idea?