Send Outlook message via VBA Macro in Excel

Maplesoft

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.Add john.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">

 

Derek Smyth

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.

Dave K Graham

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

Ron Chrisope

I have a very similar problem that started last night. The VB code "ol=NEW Outlook.Application" causes the "The specified module could not be found." error. This should be functionally identical to the CreateObject command. The changes that I made were to uninstall Norton AV and to install MS OneCare Beta. It still fails with all functions of OneCare disabled.

Ron Chrisope

I have narrowed my problem down. I duplicated the problem on a seperate system by uninstalling Norton AntiVirus 2005. It worked multiple times immediately prior to un-installation and fails when attempted immediately after uninstall. Now to find out what Norton deleted...

BFP

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

Ron Chrisope

I did a total uninstall of MS Office and then did a re-install. My problems are resolved.

I tried a repair install but that didn't help. I am also avoiding Norton (using Windows OneCare.) as their tentacles were the ones that caused this sequence.

asalcedo

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

Did anyone find which file(s) norton modified resulting in this error

duck thing

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.

Pluey

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.

Sohail Ch.

hi,
I also had same problem.
But when i used this code line , everything is running well
Set objOLapp = CreateObject("Outlook.Application", "localhost")

Thanks to all
Good luck