I'm using some VBA code found in a forum for sending email to multiple recipient's listed in a table.

Here's the code:
Private Sub Shipping_Exit(Cancel As Integer)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strEmail As String
Dim stDocName As String

stDocName = "rptNotShip"

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "tblNotShipEmail", cn

With rs
Do While Not .EOf
strEmail = strEmail & .Fields("Address") & ";"
.MoveNext
Loop
.Close
End With

strEmail = Left(strEmail, Len(strEmail) - 1)

DoCmd.SendObject acReport, stDocName, acFormatSNP, strEmail, , , "Daily Sales and Not Shipped Report", , False
End Sub

The problem is that I receive an error message that states "ActiveX component cannot create object". It refers to the line "Set cn = CurrentProject.Connection".

Any ideas??

Thanks.

I've had the exact same problem, and the bizarre thing was the database worked fine on some machines and not others. Its more than likely to be a corrupt vbe6.ddl file found in

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6

Uninstalling MS Office doesn't solve it as far as i can see because the uninstall program doesn't delete the folders/files that need to be replaced/repaired

What I did was to delete the VBA folder (move it if you're ultra cautious) manually and then do a reinstall of MS Access and hey presto it was fine. If its not this dll then its one of the others that your DB is referencing and again they will have to be manually deleted to resolve the problem

Hope this helps

PS It took me days to work this out .. but on the bright side i've ended up with all the latest service pack updates LOL!

Thanks for the help.

I'll give it a try.

Well, I tried it and it didn't seem to work.

Any other ideas?

Thanks.

Have you checked your references under Tools, References from the menu in any code module? Are any marked Missing? Which ones are checked?

Ben

I have checked the references. None of them appear to be missing.

These are the one's which are checked:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects Recordset 2.7 Library
Microsoft ActiveX Data Objects (Multi-dimensional) 2.7 Library
Microsoft Data Access Components Installed Version
Microsoft Office 9.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3

Couple of things to try, but no real help here....first, are you using DAO in this db? If not, uncheck that one....also try checking the MS Outlook library....again, these are just SWAGs, but it's not too much trouble to do this....

Ben

Check out the following posts and see if they help at all.

post 1

post 2


HTH
Ben

Check CDO and CDOEX