Error: Cannot find path specified when sending my attachment with CDO

  • Happy New Year

    I need to send my excel file through my macro. It seems to do everything that I need it to but won't send. I get the error: "Cannot find path specified" and highlights the line just before the 'Send' line. The file needs to be converted to xlsx, because the receiver will probably not have Macros activated. . When it comes to sending the file, it can't find it.

    Any help would be greatly appreciated. This problem has been a thorn in my side for quite a while. Thanks so much

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

    Edited once, last by royUK ().

  • You reset the FileName variable using the FullName property of the workbook, which includes the file path, so you don't need to add anything when adding the attachment - just use FileName.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hey Roy...Hi Rory...hey Dave

    In answer to Roy... Just before the 'send line', I msgbox the FilePath and then the FileName. The path was exactly what I picked in the 'folder Picker' area of the sub c:\User\Me\Documents\Recipes Main File\

    Msgbox FileName (as Rory suggested) had the full filepath and filename c:\User\Me\Documents\Recipes Main File\ jj.xlsm02012020.xlsx (which I think is perfect)

    So, I tried changing the line before the send from FilePath & FileName to Just FileName and now getting the error: 'Process cannot access the file because it is being used by another process'. I think we're getting close.

    Dave. the name must be changed because the original file is to be retained by the user and the altered file is to be sent to the recipient.

    Thanks to you all

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Yeah Roy the FileName does look weird, but the file does exists in the specified path as xlsx (non macro as I need... but the code block on lines 184 to 200 does not activate my deletion... (all of the sheets are still in the program)). If you look back on the code in thread #1, you can see on lines 69 to 74 is where the 'saveas' is done. I got the code from someone here, when I asked how to convert & saveas from xlsm to xlsx.

    (I want xlsx, as the programs' recipient will likely not have macros enabled).

    Thanks

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Quote

    See comments in code above

  • Try this instead of your current saveas code. FileFormat 51 will save as .xlsx, you don't need the extension. Also, the code below gets the name of the file without the extension


    Code
    1. ''/// get the path and name of the saved copy to attach
    2. FileName = Workbooks(Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5), FileFormat:=51)
  • Hi Roy

    I tried to just change up the line that you gave me and I get an error: 'Wrong number of arguments or invalid property assignment.'

    In your thread you mentioned I should change my current saveas code. Not too sure exactly how to fit in the fileformat:=51 code. currently my saveas line is below:

    Thanks

    Code
    1. wb.SaveAs FilePath & FileName & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:= _
    2. xlOpenXMLWorkbook, CreateBackup:=False

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Did you read post #8?


    Might be best to create another variable string with the complete workbook name instead of confusing everything.


    Check out this code sample.

  • Sorry Uncle Stringer... With all due respect, I'm in the middle of working with RoyUK and don't want to jeopardize the advancements that we've made. He's been helping me all along with many facets of my complex program, and knows what I need to accomplish this feat.

    I'll contact you if necessary.

    Thanks for your consideration

    Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Please don't take this the wrong way Uncle Stringer...If I jump back and forth that would be counter productive and rude. :|The program has over 30 sheets all interacting with each other and I'm very close to completion. Thanks anyway

    I'm hoping to hear from RoyUK over-night with further suggestions :)

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • This is a public forum, anybody can jump in whenever they want.

    It would be "counter productive and rude" not to acknowledge other members who are trying to help out.


    Everybody who has supplied thoughts into this thread are virtually telling you the same thing, read all the posts in this thread, I am sure you will get a "Eureka!" moment.

  • Dave, for pete's sake. I'm not trying to be rude to anyone. If you are offended I'm sorry. That was not my intent. I am a newby to VBA and not as well versed as the rest of you are. I can't see the connection to all three posters. In the instructions of the forum we are compelled to be patient. I was just being patient. I meant to get back to you but I was just waiting for Roy to respond. RoyUK was advising me in one direction...I was trying out his suggestions and he was responding. You are giving me suggestions that I don't understand, from another direction. uncle stringer advising that I'm just confusing everything. Well...Yeah...I'm confused.

    So thanks so very much for your help, but I need to choose something. Please don't be offended...not my intention at all.

    Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Please don't take this the wrong way Uncle Stringer...If I jump back and forth that would be counter productive and rude. :|

    Pretty sure davesexcel was quoting you, I really doubt you offended him.


    Were you able to experiment with the code I provided, explain how it doesn't work for you so we can help.



    Learning how to step through a code is important as well, when you step through the code you will see what is happening and then you will know where the problem is.


    Debugging code

  • Jimmy


    I've amended you code. There's quite a lot I can't see what should be happening and it should not be necessary to select sheets.


  • I believe the problem is saving this workbook as a .xlsx file because it is a .xlsm file.


    I do see that you have considered SaveCopyAs and noticed you can't change the file extension to .xlsx


    Here is a consideration.

    Save a copy to the temp folder, open that copy and saveas a .xlsx workbook to the desired location.


    See this sample