I am using the following module in my database. The module was working fine for about two weeks and then it started giving me a run-time error and would not complete the operation. The run-time error is 2147287036 (80030004). The debug program highlights the Set my Item = my Ol App.CreateItem(olMailItem). I looked this error up on the internet and it says: There are insufficient resources to open another file.
Here is the module:
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)
'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
MyName = Dir
'send the email
'release memory allocated for object variables
Set myOlApp = Nothing
Set myItem = Nothing
Set myAttachments = Nothing