PDA

View Full Version : Stuck in OFFICE12 folder after SendMail



Felix Atagong
12-21-2010, 07:21 AM
(This item has also been posted at http://www.mrexcel.com/forum/showthread.php?t=513788)

I have an Excel 2000 macro (that runs smoothly on Excel 2000) but that creates a 1004 run time error in Excel 2007 but only if a mail has been send (with Outlook 2007).

Basically it is a text parser that copies certain data to a worksheet "Country" if a certain countrycode is met and sends this worksheet by mail. The macro loops through several 'countries' and opens, saves, mails and closes files. But once a mail has been send using the Sendmail command there will be a 1004 error at the next run in the loop (but only in Excel 2007).

The error given is as follows (while saving):

Run time error 1004
Microsoft Excel cannot access the file
C:\Program Files\Microsoft Office\Office12
There are several possibilities
The file name or path does not exist
The file is being used by another program
The workbook you are trying to save has the same name as a currently open workbook

I did some testing and it appears that I am stuck in the OFFICE12 folder, unable to change it with CHDIR, CHDRIVE or whatever.

My file ABCTemp.xls is located at the MyDocuments folder on the server. MsgBox (CurDir) gives \\SERVER\RedirectedFolders\UserName\My Documents (UserName changes of course per user).

When I send a txt file with Outlook (also saved in MyDocuments) the current directory changes to
C:\Program Files\Microsoft Office\Office12
the macro resumes but I am unable to change it back to MyDocuments with
ChDir (WshShell.SpecialFolders("MyDocuments")).

As a result the macro looks for files in Office12 while it should look at MyDocuments.

By putting the MyDocuments path in a variable I am able to open, close and save in the redirected My Documents folder. But at the second run in the loop I get the following Outlook error:


Microsoft Office Outlook
NOT IMPLEMENTED

Further analysis (debug) gives:


General Mail Failure. Quit Microsoft Excel, restart the mailsystem and try again.

Probably this is more an Outlook problem than an Excel one, but is there a solution?

Felix Atagong
12-21-2010, 07:49 AM
Solved, I guess. Adding Outlook Object and putting in some waiting time between changing directories did the trick.