Send Outlook message via VBA Macro in Excel
I have some macro code in an Excel workbook. It has been working fine for several years under various combinations of Excel 97, Excel 2000, Excel 2002(XP) and Excel 2003 along with Outlook 97, Outlook 2000, Outlook 2002(XP) and Outlook 2003. We have one new computer that has Office 2003 (SP2) that was recently setup and the macro generates the following error:
Run-time error '-2147024770(8007007e)':
Automation error
The specified module could not be found.
Here is the VBA code in the Excel Macro. The error happens when it reaches the line that reads:Set theApp = CreateObject("Outlook.Application"). I tried to run the macro on two other Office 2003 SP2 computers and they worked OK, which shot my theory that it was strictly related to Office 2003 SP2. So far it's only one user who had this problem.
Dim theApp, theNameSpace, theMailItem, myAttachment, MessageBody, subject
'create a new Outlook Application Object,
'direct it to the proper NameSpace,
'create a new Mail Item and set the attachments collection
Set theApp = CreateObject("Outlook.Application") 'error occurs here
Set theNameSpace = theApp.GetNameSpace("MAPI")
Set theMailItem = theApp.CreateItem(0)
Set myAttachment = theMailItem.attachments
'add recipients to MailItem
theMailItem.Recipients.Addjohn.doe@hotmail.com
theMailItem.subject = subject
theMailItem.Body = MessageBody
myAttachment.Add fName, 1, 1, subject
theMailItem.Send
theNameSpace.Logoff
Any advice, solutions, or suggestions are welcomed and appreciated.
Thanks!
P.S. Here is a screen shot of the error
<img src="http://www.asi.com/vbaerror.jpg">
what to do is open the workbook on the machine with the problem. Bring up the VBA Macro Editor and check the References (Tools->References) for anything thats missing. The entry will be there but a message of MISSING will be beside it. Select it and take a note of the location of the file thats missing. The problem is either the file isn't there or it hasn't be registred correctly.
Do a search for the file on the computer and find out where it has been installed, it will be somewhere. Now you can copy it to the location specified in Excel, do not move it, make sure the original file stays were it is. Once you copy it to the location try your code again, in theory it should work now, it depends on what file is missing.
If the spreadsheet still doesn't work you might need to register the copied file with Regsvr32.exe tool but I'm unsure what would happen in that event to the original registry entry for the original file. I'd only do this as a last resort and I'd read up on the consequences, if any, of doing it... one way to ensure you can rollback registration is make a full back up of the registry before running Regsvr32.exe.
I have exactly the same problem for code that worked a few months ago. It is from the published example, ExportAccessContactsToOutlook in article 290658.
I cannot find any references that are missing, or marked missing.
Since the code ran, I have upgraded Norton, deinstalled the MS Beta Spyware, and applied the available hot fixes.
Thanks
Dave
I too am now getting this issue on an updated computer, and today, got another message on the next line of code that starts Outlook...
Runtime Error -2147467239 (80004005)
The operation failed.
I also have narrowed it down to a virus protection/script prevention problem but have had no success in figuring this out. Any suggestions or new findings would be helpful!
David
BFP at 2007-8-31 >
I also started getting error message when trying to send an Outlook email with VBA from Excel.
It happened twice on two different computers after installing Norton Internet Security 2006.
It was solved only after uninstalling MS Office and re-installing again
 mhhough wrote: |
| Did anyone find which file(s) norton modified resulting in this error? |
|
Odds are that Norton simply disallows the instantiation of Outlook.Application objects. This is probably done through the registry, but don't quote me on that. If you NEED a new instance of Outlook, try using the Shell function to start one. If there's already one running, either use GetObject (if it works) or work through the Windows API.
I pretty much had the same problem today. I think I've got a similar situation... I have Outlook 2003 and Norton 2005, plus Business Contact Manager for Outlook.
I tried early binding and late binding. Couldn't get either to work, so I tried it within Excel on my laptop (which has earlier versions of Office and Norton) and it worked fine. Didn't understand why it worked on one computer but not the other, so I was trying anything and finally stumbled on this solution:
Set objOLapp = CreateObject("Outlook.Application", "localhost")
Everything worked great after I used that code. Hope it helps.
