I am using the following module in my Access database:
Public Function SendTextFile(strpath, strTableName, strRecipient, strSubject)
Dim myOlApp As Object, myItem As Object, myAttachments As Object
Dim MyPath As String, MyName As String
'First, export the text file using a saved Export Specification
DoCmd.TransferText acExportDelim, strTableName & " Export Specification", _
strTableName, strpath & strTableName & ".txt", True
'Second, create an instance of outlook, new message and attachment object variables
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments
'add the recipient(s)
myItem.Recipients.Add strRecipient
'add the subject line
myItem.Subject = strSubject
MyPath = strpath ' Set the path.
MyName = Dir(MyPath) ' Retrieve the first entry.
'Loop through the files in the directory to attach all applicable files
'(this is really for more than one attachment but it will work for one file too...)
Do While MyName <> "" ' Start the loop.
'this is where you decide what test you need... I'm just using the name itself, but you can do things like
'attach all .txt files, etc...
If MyName = strTableName & ".txt" Then
myAttachments.Add MyPath & MyName
End If
MyName = Dir
Loop
'send the email
myItem.Send
'release memory allocated for object variables
Set myOlApp = Nothing
Set myItem = Nothing
Set myAttachments = Nothing
End Function
This module has been working for a couple of weeks and then it started displaying the run-time error 2147287036 (80030004) and saying the operation failed. When I ran the debugger, it highlighted the Set my Item =my OlApp.CreateItem(olMailItem) line. I looked up this error code on the internet and it said: There are insufficient resources to open another file. We did have a couple of errors happen prior to this error but now we are stuck here. I am wondering if the memory is not being released after the operation fails but I thought when you shut down Access the memory was cleared. So confused, please help.
Thanks,
B&R
Public Function SendTextFile(strpath, strTableName, strRecipient, strSubject)
Dim myOlApp As Object, myItem As Object, myAttachments As Object
Dim MyPath As String, MyName As String
'First, export the text file using a saved Export Specification
DoCmd.TransferText acExportDelim, strTableName & " Export Specification", _
strTableName, strpath & strTableName & ".txt", True
'Second, create an instance of outlook, new message and attachment object variables
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments
'add the recipient(s)
myItem.Recipients.Add strRecipient
'add the subject line
myItem.Subject = strSubject
MyPath = strpath ' Set the path.
MyName = Dir(MyPath) ' Retrieve the first entry.
'Loop through the files in the directory to attach all applicable files
'(this is really for more than one attachment but it will work for one file too...)
Do While MyName <> "" ' Start the loop.
'this is where you decide what test you need... I'm just using the name itself, but you can do things like
'attach all .txt files, etc...
If MyName = strTableName & ".txt" Then
myAttachments.Add MyPath & MyName
End If
MyName = Dir
Loop
'send the email
myItem.Send
'release memory allocated for object variables
Set myOlApp = Nothing
Set myItem = Nothing
Set myAttachments = Nothing
End Function
This module has been working for a couple of weeks and then it started displaying the run-time error 2147287036 (80030004) and saying the operation failed. When I ran the debugger, it highlighted the Set my Item =my OlApp.CreateItem(olMailItem) line. I looked up this error code on the internet and it said: There are insufficient resources to open another file. We did have a couple of errors happen prior to this error but now we are stuck here. I am wondering if the memory is not being released after the operation fails but I thought when you shut down Access the memory was cleared. So confused, please help.
Thanks,
B&R
