Excel VBA help....please :)

[leemcder]

Hi, I am running this script along with a rule. When an email is received from a specified person and it contains an attachment, it will run the script. The script saves a copy of the attachment and renames it as the subject. The format of the attachment received in the email is in .XLS format and I want to keep that format. The script is saving the file to my path and renaming it as the subject but I can't open the file, I get "the file format and extension of file name don't match. The file could be corrupt or unsafe"

Can anyone help me?

Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "Y:\accounts\success fee bills"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "" & itm.Subject & ".XLS"
Set objAtt = Nothing
Next
End Sub

[Paul_Hossler]

1. Your title is very not specific about the problem

2. You can use the [#] icon to insert CODE tags and paste the macro between them -- I did it for you this time

3. Seems more of an Outlook question and maybe the Outlook gurus can help - let me know if you want it moved

4. Try running this and see how Outlook wants to name the file

Sub saveAttachtoDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String

    saveFolder = "Y:\accounts\success fee bills"

    For Each objAtt In itm.Attachments
        objAtt.SaveAsFile saveFolder & "" & itm.Subject & ".XLS"

       MsgBox saveFolder & "\" & itm.Subject & ".XLS" ' I added the \

    Next

End Sub

[leemcder]

thanks for your reply, I have ran this script and it no longer saves any files to my selected location. I also get a pop up which I hadn't received previously, saying Y:\accounts\success fee bills\459232.XLS

Do you have any other suggestions? your help is very much appreciated

[Paul_Hossler]

1. The pop up was from the MsgBox line - just to verify the file path and name

2. Are you sure that the attachment is an Excel file. If you save it manually, can Excel open it?

[leemcder]

Hi, yes its an excel file, XLS format. I can open the attachment and if I save and rename the attachment manually I am still able to open. I just can't work out why, if outlook is only saving and renaming the attachment and keeping XLS format why it won't open. I thought it would have been pretty straightforward but its causing me a bit of a headache!