September 11, 2008 at 9:53 am
Hi
I have a button on an Access 2003 form which allows the user to sent an email string which is automatically genterated. This has worked excellently for a long time - until the organisation changed to Outlook 2007 (we still use Access 2003).
The code is used is shown below:
code to create Msg string of recipients and message body.
Set objOLApp = CreateObject("Outlook.Application")
Set objMailItem = objOLApp.CreateItem(0)
' Set objns = olapp.GetNamespace("MAPI")
' Set objfolder = olns.getdefaultfolder(6)
Set objAttach = objMailItem.attachments
objMailItem.To = Msg
objMailItem.BCC = ""
objMailItem.CC = ""
objMailItem.Subject = SN
objMailItem.body = DS
' objAttach.Add "\\staff2\global$\Disciplinedata\sap.dot", 1
objMailItem.display
Set objMailItem = Nothing
Set objOLApp = Nothing
Set Makemail = Nothing
Set conn = Nothing
Sometimes it works - very slowly. Sometimes it returns the error message:
'Automation error. The object involved has disconnected from its clients.'
or
'The messaging interface has returned an unknown error. If the problem persists restart Outlook.
I would be very grateful for some advice.
Thanks
Paul
September 16, 2008 at 11:22 pm
I would put this problem squarely in Microsoft's lap. I tried to have Office 2007 on the same computer as my Office 2002 so I could use the 2007 copy from time to time to get use to the new features and the changed (for no good reason) the user interface. When I would start any Office 2007 program it would take up to 8 (eight) minutes for the program to load. If I tried later to run the same Office 2007 program it would load quickly. BUT, if I used an Office 2002 program before using the Office 2007 program for a second time, then the 2002 program would take about 2 or 3 minutes to load. Then going back to 2007 would again take up to 8 minutes to load again.
I would assume, but I really don't know, that the problem between 2002/2003 and 2007 is where you are running into your problem. I would suggest calling Microsoft Support and seeing if they have any sort of a "fix." When I talked to them a year ago after experiencing the problem, I was assured they were going to work on a "fix." One Microsoft person I talked to said for me to stop complaining because, "We have to deal with it too." I guess he thought that if Microsoft employees had the problem, then it was OK for customers to have it too. I just hope they actually followed through and fixed it.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 17, 2008 at 2:13 am
Thanks, Vic
Good to know that I'm not the only one with these problems. I would have though that if Microsoft had fixed this, it would be in an update - though I can't spot anything. I'm thinking of emailing directly from SQL Server -but would like to avoid this if possible. I'm amazed that Microsoft seem to have got so much wrong in 2007.
Thanks again
Paul
September 18, 2008 at 2:21 am
I'm using this code in an Access 2003 application
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objAttach As Outlook.Attachments
Set olApp = Outlook.Application
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
objMail.To = Generated in code
objMail.Subject = various fields on a form
Set objAttach = objMail.Attachments
objAttach.Add "\\Loki\Mis\Live\Winfolder Upgrade Instructions Jan08.pdf"
objMail.Display
no Garbage Collection
On a system with Access 2003 installed (plus the Office 12 / 2007 converter) the references show up as Microsoft Outlook 11. On a system with Access 2007 installed and Office 2007 only, the reference show up as Microsoft Outlook 12.
The code works perfectly with either Office 2003 or Office 2007. What I suspect is happening is that when you run with a mixed version, the little darling is totally confused. You can see that from the relative start up times.
This isn't specific to 2007, it applies to any combination of old and new Microsoft products. I've got an antique word processing box at home which runs Office 97 and 2000. Office 97 always starts up fast, 2000 brings up a box saying (basically) that it needs to repair itself. We run mainly 2003 at work. We have one Access app which uses the 2000 run time to avoid the 2003 security messages. If you run that and then try and run a 2003 Access database, it brings up the 2000 run time which then complains that the database is in an unrecognised format. To get things back to normal, you have to run Access 2003 which then rejigs all the file associations.
There is no problem running 2003 apps on 2007. I've been doing that almost since 2007 went to manufacture. I tried running both on the same box but gave up because 2007 kept having to "repair" itself because of the contamination of 2003. What I do now is have a 2003 box for development and normal use and a 2007 box to work out how to cope with the interface.
One nice feature of 2007 is that you can set "trusted locations". This means you can load an Access database chock full of code with no warning messages from a trusted location. If you load it from somewhere else then you get all sorts of warnings about unsafe options.
September 18, 2008 at 7:25 am
The code works perfectly with either Office 2003 or Office 2007. ... the little darling is totally confused...
We have one Access app which uses the 2000 run time to avoid the 2003 security messages. If you run that and then try and run a 2003 Access database, it brings up the 2000 run time which then complains that the database is in an unrecognised format. ...
The only way to bypass this "unrecognised" error message is to always open an Access database by referencing the MSACCESS.EXE file, including the path to the version (97, 2000, 2002, 2003, 2007) that you need to use for this database. Microsoft as assumed for us that whatever version we last used, that is the version we want to use next. I spent about 6 to 8 weeks trying to find some way around this Microsoft assumption and never found a way around it, except to always reference the MSACCESS.EXE along with it's path for the version needed to open the database in question. This can either be done with a shortcut, or by opening the version of MSACCESS.EXE needed first. But just double-clicking on the Access database you want to run will cause your PC to try to run that database with the last version of MSACCESS.EXE used on that PC.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 18, 2008 at 8:54 am
The basic problem is that Access is self-registering, so the file association and the dll's are re-written to the registry every time it opens.
At one point, I had a PC with Access 2.0, 97, 2000 and 2002 installed because I had various customers running all four versions. My solution was to write a VB6 program that would open the mdb and inspect some properties and then launch the correct MSACCESS.EXE file using the following logic...
I would set db = OpenDatabase(Command) and then
if db.Properties("AccessVersion") = "09.50" then it is Access 2002
otherwise I would inspect Int(db.Version):
2 = Access 2.0
3 = Access 97
4 = Access 2000
Then I could simply drag and drop the mdb onto the VB6 executable and it would open the file with the right version of Access.
I never updated the VB6 program for Access 2007 but I can post the source code if anybody's interested.
September 18, 2008 at 9:02 am
It's not Access that's the problem!
It's having two different versions of Office on the same machine.
Why not just run the Office 2003 Database in Access 2007 with only Office 2007 on the machine and see what happens.
September 18, 2008 at 9:16 am
jfmccabe,
I didn't say it was Access that was the problem, it's Microsoft that is the problem. As William Mitchell just said, Access is self-registering, therefore, which ever version you last ran, Microsoft, has already made the decision for EVERYONE, that you WILL want the same version next time you want to run Access. And as William noted, he has more than one client, as do I. So we need more than one version of Access on our computer at one time. Microsoft just does not seem to understand how to make life pleasant for consultants.
William,
That looks like a really nice VB6 program! Would you be able to associated ".mdb" with that vb6 program? Then being able to click on the .mdb would run the VB6 program and open the correct version of Access.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 18, 2008 at 9:43 am
Vic Rauch (9/18/2008)
Would you be able to associated ".mdb" with that vb6 program? Then being able to click on the .mdb would run the VB6 program and open the correct version of Access.
No, tried that, it wouldn't work that way because if I registered mdb to open with my executable, Access would turn around & overwrite that when it opened. There are two ways to use my program - either drag and drop the mdb, or add a "send to" shortcut and then right-click on the mdb.
This issue affects all programs & files, not just Access. Windows looks at the file extension, not for any kind of signature that tells it which program to use.
I agree that the problem is Microsoft's way of thinking, and the worst example of that is the ridiculous thing they call Office 2007.
September 18, 2008 at 9:55 am
William,
As with most people, I love it when I find someone that agrees with me, or I agree with! Thanks for your response. I never have, and don't think I will live long enough to, figure out why in the world microsoft sets up rules for the world to live by, then changes the rules for themselves. It must be nice for them to think they are "all knowing." Only problem is, Jehovah already is.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 19, 2008 at 3:11 am
Dear All
Thanks for your great responses.
jfmccabe,
I have just tried your code:
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objAttach As Outlook.Attachments
Set olApp = Outlook.Application
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
objMail.To = Generated in code
objMail.Subject = various fields on a form
Set objAttach = objMail.Attachments
objAttach.Add "\\Loki\Mis\Live\Winfolder Upgrade Instructions Jan08.pdf"
objMail.Display
The references are as you suggest. However, I'm getting the error message:
'Object library feature not supported'
Do you have any idea what I'm doing wrong? Am I missing another reference?
Many thanks
Paul
September 19, 2008 at 5:22 am
Paul,
have you made any changes to the code before you tried it in your application? What you have posted is exactly what jfmccabe posted, but this is not code that is meant to be executed. For example, the line that looks like this: "objMail.To = Generated in code". You will need the "objMail.To =" part, but the "Generated in code" is instructions to the person using this sample to do what it says, Generate within your own code the To e-mail address. The next line is the same idea, "various fields on a form" refers again to generating this info within your own code. Two more lines down is a reference to an attachment with the path and name of that attachment. That needs to be something from your own system, not jfmccabe's system.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 19, 2008 at 5:46 am
Hi Vic
Yes I generate strings to replace the inserts. Unfortunately it still doesn't work on the organisations XP computers with Access 2003 and all other applications 2007. It does work, however, on my Server 2000 laptop which is running just Office 2003. The other machines are obviously missing something as it works for jfmccabe - but I'm not sure what??
Many thanks for your time and trouble, Vic.
Paul
September 19, 2008 at 6:37 am
Which line does the program stop at?
Have you installed the "Office 12 / 2007 converter"?
These two will help us with ideas, unless someone already knows the answer, which someone just might.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 19, 2008 at 10:33 am
Back during the transition from Access 97 to 2000, there was a bug in 2000 that prevented using SendObject from sending multiple emails from VBA. At that time I found some code that used the Collaboration Data Objects (CDO) which created a class that would override the built-in SendObjects in Access. It used the Microsoft CDO 1.21 library, it did not use the Outlook library so I wonder if that might be an alternate approach when mixing Office versions.
I can post that code if you'd like to try it.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply