saveas run time error 1004 after sendmail

[Felix Atagong]

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 certain conditions are met.

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 (but only if data has been copied). The macro loops through several 'countries' and opens, saves and closes files as long as they are empty. 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 (on the save ABCTemp.xls code):

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

If I disable this code the macro halts on the next save line (the ABC & ww(5) .txt) but with a slightly different error:

Run time error 1004
Cannot acces read-only document ABCDE.txt

Here is the code that used to work fine in Excel2000:

ChDir (WshShell.SpecialFolders("MyDocuments"))
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs FileName:="ABCTemp.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=True
       
ChDir (WshShell.SpecialFolders("MyDocuments"))
Sheets("Country").Move
ActiveWorkbook.SaveAs FileName:="ABC" & ww(5) & ".txt", _
    FileFormat:=xlTextWindows, CreateBackup:=False
' ww(5) is an ISO country-code BE, FR, UK, DE, etc... 

Application.DisplayAlerts = True
If Range("A9999").End(xlUp).Row > 1 Then
Application.Dialogs(xlDialogSendMail).Show _
    arg1:="abcde@atagong.com", _
    arg2:="LIST " & ww(5)
End If
ActiveWorkbook.Close SaveChanges:=False

Any ideas why this doesn't work in 2007?

 

[daverunt]

Hi,

Have you tried stepping through the code in the failure condition to see if the code fails when running slow?

 

[Felix Atagong]

Yes, I manually stepped through the macro (well at least where it gives the error) and it doesn't matter if I go slow or not.

I think the problem could be on the 'redirected folders' on the server though, the Office2000 machines still saved everything locally, but the new ones save everything on a central place. But it still is a weird error.

 

[Felix Atagong]

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

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.

(I'm going to try to store the path \\SERVER\RedirectedFolders\UserName\My Documents in a variable and change the directory like that, keep you posted.)

 

[Felix Atagong]

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 got the folliwng pop up (when attempting to send a second page by mail):

Microsoft Office Outlook
NOT IMPLEMENTED

Further analysis (debug) gives:

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

Any Outlook specialists here?

 

[Felix Atagong]

Solved: adding Outlook Object Library and some seconds of waiting time (between changing directories) did the trick.

 

[Felix Atagong]

Previous method didn't help. SendMail & Redirected Folders will bug the macro as it fails to change directory (it stays in Office12 after SendMail).

Solution: not using SendMail but Outlook object instead.

Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "xyz@mailaddress.com"
        .CC = ""
        .BCC = ""
        .Subject = "subject"
        .Body = "add some text"
        .Attachments.Add ("LIST" & ww(5))
        .Send 
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing