Roll back vba + excel functions for use in 2003

[cjeccles]

When I initially coded the excel sheet and vba for those sheets, I was working in Excel 2007. Work has now decided to "update" the computers, but they have backdated our office suite from 2007 to 2003. This has caused a bit of an annoyance for some of the macros and things that I had coded in 2007

I have a command button that checks the emails in Outlook over the past 6 days to find a specific email. If it can find it, it tries to save the attachement. If its unable to find it, it says that it cant find it and exits the sub without doing anything.

I have pretty much the same code in 2 different workbooks. One workbook works ok, and the other doesn't. When I run the one that doesn't work it comes up with the following error:

Run-time error '-2147024891 (80070005)':

Cannot save the attachment. You don't have appropriate permission to perform this operation.

This is the code that says I dont have permission to save the file. The part the gets highlighted in the debugger is the "SaveAsFile" part.

    i = 0
    Do While i < 7
        For Each msgMail In mpiFolder.Items
            If msgMail.Class = 43 Then
                If msgMail.Subject Like "*" & "Store" & "*" & "Shipment Notification" & "*" & Format(Date - i, "DD/MM/YYYY") & "*" Then
                    todaysDate = Format(Date - i + 1, "DD/MM/YYYY")
                    strSubject = msgMail.Subject
                    msgMail.Attachments.Item(1).SaveAsFile Trim(strAQSPath & "\DELIVERY.xls")
                    FileSaved = True
                    Exit For
                End If
            End If
        Next
        If Range("A65536").End(xlUp).Value Like "*" & Format(Date - i + 1, "DD/MM/YYYY") & "*" Then
            MsgBox ("This delivery has already been added to your stock.")
            FileSaved = True
            AlreadyAdded = True
            i = 7
        End If
    i = i + 1
    Loop

Here is the code from the workbook that works properly, and saves the file without any errors.

For Each msgMail In mpiFolder.Items
        If msgMail.Class = 43 Then
            If msgMail.Subject Like "*" & "Store" & "*" & "Serial Information" & "*" & Format(Date, "DD/MM/YYYY") & "*" Then
                strSubject = msgMail.Subject
                msgMail.Attachments.Item(1).SaveAsFile Trim(strAQSPath & "\LATEST.xls")
                MsgBox ("Today's (" & Format(Date, "DD/MM/YYYY") & ") Serial Information sheet has been saved from the email." & vbNewLine & vbNewLine & "Please begin scanning all contract stock.")
                FileSaved = True
                Exit For
            End If
        End If
    Next
    
    If FileSaved = False Then
        MsgBox ("Sorry, I am unable to find today's audit sheet in your emails.")
        Exit Sub
    End If

Any help with this will be greatly appreciated. I've got no idea what could be wrong because as you can see, the code is pretty much the same in both workbooks.

 

[SydneyGeek]

In the one that doesn't work...

Do any of the emails get created?
Are there any subject lines with an apostrophe? That can cause havoc in VBA routines...

Denis

 

[cjeccles]

In the one that doesn't work...

Do any of the emails get created?
Are there any subject lines with an apostrophe? That can cause havoc in VBA routines...

Denis

It seems to find the emails perfectly fine by the subject. It just appears to be when saving the attachment on the email that it throws out the error.

If msgMail.Subject Like "*" & "Store" & "*" & "Shipment Notification" & "*" & Format(Date - i, "DD/MM/YYYY") & "*" Then
                    todaysDate = Format(Date - i + 1, "DD/MM/YYYY")
                    strSubject = msgMail.Subject
                    [COLOR=Yellow][B]MsgBox (strSubject)[/B][/COLOR]
                    msgMail.Attachments.Item(1).SaveAsFile Trim(strAQSPath & "\DELIVERY.xls")
                    FileSaved = True
                    Exit For
                End If

when I use a message box to show the subject when it finds an email that matches the query, it shows the subject (in this case) as "Store 2096 Shipment Notification - 28/02/2011". Could that be any reason why it wouldn't work? I'm open to any and all suggestions here

The code that works would find the subject "Store 2096 Serial Information - 28/02/2011"?

 

[SydneyGeek]

Have permissions changed on the directories? You may have read/write permission on one, and read-only (or no access) on the other.
You can test that by temporarily changing the save path to the one that works.

Denis

 

[cjeccles]

Have permissions changed on the directories? You may have read/write permission on one, and read-only (or no access) on the other.
You can test that by temporarily changing the save path to the one that works.

Denis

The strange thing there is that they both save into the same directory. C:\Documents and Settings\User\Desktop\Audits

I went and had a look anyways, and it seems that it could have been the file itself that was read-only or something? As I have checked the properties of that folder and removed any read only that was in there, and it now works!

Thanks very much for your help