Operation failed when attaching file to email

  • Good Morning, I have a macro that I'm adapting to send a range of reminders out of an Excel sheet. I'm trying to attach a file and I've never had any issues before but with this one, I get an error operation failed and I can't see where the problem is. The file I'm attaching is on a shared drive. But have put a test.docx on my desktop and tried that and still get the same error. The rest of the code works fine, i.e. if I convert the Add.Attachment line into a comment, the code runs through. I've also inserted a message box with the file coordinates and they seem fine to me.

    The file name has 2 variables: the project folder and the file name as these will vary for each project. - B5 contains the path for the project folder and B1 the project name.

    I have to admit that even though I have done a number of macros, I consider myself quite pedestrian with VBA. I'm hoping somebody will see where the code goes wrong.

    Christine, Auckland

  • Is this happening EVERY time you run the macro, or just occasionally? I would guess that the path to the file or the file name itself is incorrect, or contains some invalid characters...

    Right before

    .Attachments.Add =strAttachment


    msgbox DIR(strAttachment)
    .Attachments.Add = strAttachment

    That msgbox will display the full path and file name IF and ONLY IF the file name can be found/reached. Otherwise if there is something wrong with path or filename it will just return an empty string/message box

    Also, for example, the filename or folder name that you construct as part of the string strAttachments can not contain "*" or "/" or "."

    See here, for a full list of invalid fileame characters.


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Hi Ger Plante

    Thanks for that. The message box shows the file name but not the path name. Does that mean there's something wrong with the path?

    I actually already had a message box and that one will show the entire path:
    MsgBox sh1.[B5].Value & "\Meetings\Kick-Off Meeting Minutes - " & sh1.[B1].Value & ".pdf"
    The results looks okay to me. I have to admit it's not how I would contruct a file name but I'm working with somebody else's data and can't change it.

    Also, my file is on a shared drive. Does that matter in any way?

    Thanks. Christine

  • Yes - if the message box is displaying the filename only then it should be fine. That means that VBA can "reach" the file.

    It could be an issue with the shared drive. Is there a chance that someone is editing /creating / locking the file that you are trying to attach?

    The only other thing to try and ensure the path is OK... is instead of
    msgbox DIR(strAttachment)
    debug.print strAttachment

    Then press Control key+G to go to the interactive debug window and copy/paste the value that is displayed there into the clipboard. Now go to the windows Run screen (or Windows Key+R), paste the string into the run window and press enter. That should immediately load the file. If it doesnt it could be something wrong with the path to the file, or the file itself could be locked... Regards Ger

    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Hi Ger

    The file opens without a hitch so the issue is somewhere else. As a workaround I have changed my code to open the folder and I will add the file/s manually.

    Thanks for your help. Christine

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!