Email attachment run-time error

[BadgerLikeSpeed]

Hi,
I'm pretty new to Access, and teaching myself as I'm going along, so any help will have to be explained in simple terms. Sorry.
To the point...
I'm trying to get access to export my tables to a new database, attach that to an email, send it and then delete the file once it's been sent. (There is a good reason for this)
Initially I had Access export the tables to an excel sheet and email it. Everything worked fine! Then I decided to change it to an Access database, and my problems began.
This is my code:

Private Sub OpenFormExport_Click()
Dim ws As Workspace
Dim db As Database
Dim LFilename As String
Set ws = DBEngine.Workspaces(0)
'Create new database in current folder
LFilename = CurrentProject.Path & "\ExportWatwick.mdb"
Set db = ws.CreateDatabase(LFilename, dbLangGeneral)
'Export tables to new database
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatCrew", "WatCrew"
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatElect", "WatElect"
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatEngDataMonth", "WatEngDataMonth"
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatEngDataWeek", "WatEngDataWeek"
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatJobList", "WatJobList"
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatMiscTab", "WatMiscTab"
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename, acTable, "WatOOP", "WatOOP"
 
'Export via Email
Dim olLook As Object 'Start MS Outlook
Dim olNewEmail As Object 'New email in Outlook
Dim strContactEmail As String 'Contact email address
'Open Outlook
Set olLook = CreateObject("Outlook.Application")
Set olNewEmail = olLook.createitem(0)
strEmailSubject = "Watwick Data Report"
strEmailText = ""
strContactEmail = "EMAIL ADDRESS"
'strCc = "EMAIL ADDRESS"
With olNewEmail 'Attach template
.To = strContactEmail
'.CC = strCc
.body = strEmailText
.subject = strEmailSubject
.attachments.Add (LFilename)
.display
'.send
End With
'Delete File
Kill (LFilename)
 
End Sub

When I run this I get a run-time error '-2147024864 (80070020)' at
.attachments.Add (LFilename)
Help gives me this information:
Automation error (Error 440)



This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010. When you access Automation Objects, specific types of errors can occur. This error has the following cause and solution:

• An error occurred while executing a method or getting or setting a property of an object variable The error was reported by the application that created the object.
  Check the properties of the Err object to determine the source and nature of the error. Also try using the On Error Resume Next statement immediately before the accessing statement, and then check for errors immediately following the accessing statement.

The confusing point for me is that if I split the code to run from 2 seperate buttons then it works fine, as in one button to create the database, and then a sperate button to send the email. Unfortunately I need it all to happen from one button.
I presume that Access is still trying to access/edit the newly created database when it is also trying to attach it to the email. How do I force it to leave it alone? (Sorry for the terribly technical language!)
Any help would be greatly appreciated...

 

[BadgerLikeSpeed]

On further investigation it seems that the new database is still open when the code tries to attach the database to the email. I've tried putting a pause in the code (tried up to 10 seconds) after the last export command, but that has made no difference.
Is there a command to close a non-active database? Since it is creating a new database I can't switch to it, run code to close it, and then email it. I need to be able to close it from the initial database.

 

[PeterF]

Don't know if it works but try a "DoEvents" before sending the mail.

 

[BadgerLikeSpeed]

Thanks for the response, just tried DoEvents, no change
Also just tried making the email creation part a seperate subroutine, and calling it from the original process. No joy either...

 

[BadgerLikeSpeed]

I've cheated, and got the first button click to open a form with 'Data Ready to Send' and an Ok button, which then closes the form and sends the email. It's not the most elegant solution in the world, but it works.
If anyone has a better way of getting around this then I'd be pleased to hear it as I'd prefer to do this more neatly!

 

[PeterF]

Sorry, missed the part where you use "set db".
You need to close the db by:

db.close
set db nothing
'also cleanup your workspace by:
ws.close
set ws.nothing

 

[BadgerLikeSpeed]

Thanks a lot... That's cured it all!
Learning all the time.