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

  • Hi Roy From your questions:

    Question OnLine 28: The program has the built in feature to send email with the CDO and g-mail requires the user to activate “Allow less secure apps”. I’m giving the user the option to opt out in case they don’t feel safe. Sub: GYFamilyLessApps that I’m calling before this, uses msgbox to determine this... and place value in J45 (Works Perfectly)


    Question OnLine 40: This bolsters the previous decision and allows them to either continue to email or opt out (Works Perfectly)

    I didn't see anything new except for the questions (above) in the sub that you included in your last post...did I miss something

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hey Uncle Stringer & Dave. Sorry I didn't get a chance to try your suggestions. It seems reasonable. Will look harder at them when I clear my mind. LOL Working on Roy's recommendations

    I need it to open as xlsx, as I don't want the copy to be sent from the user to the physician, with error messages [opping up when they hit the form control buttons. It must be clean. It will be initially sent from me to the user, then altered and sent to their recipients (redacted) for viewing only.

    The whole program works great except for this: "Cannot find path specified" and highlights the line just before the 'Send' line. If not trying to delete the selected sheets and change to xlsx it sends great as xlsm.

    I've found where it's changed the file but the sub can't find it when time to 'SEND'

    arggghhh

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Uncle Stringer

    I tried your suggestion. it saved as xlsx as I wanted, but it doesn't send. I get the error: 'Process cannot access the file because it is being used by another process'. It does come up as the xlsx file on my screen...therefore that's likely why I'm getting the error. Need it to not open on my screen somehow?

    Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Any suggestions will try tomorrow...gotta go for now...Thanks all

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • BTW I used your code from #11 thread

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hey guys. this is the closest that I've ever gotten on this problem. I tried some ideas from Uncle Striker. and have come to this: The file saves as xlsx in the proper location in my folder. It does not alter the original file, The file shuts down as needed. This is great...only problem is, it doesn't 'SEND' the file. I don't want to touch it until I get direction. ALSO: I'm not getting any errors, it just isn't sending the file. Ignore the rem statements. While altering the sub I just changed some of the code that I replaced to rem statements, so as not to lose it if I need to return.

    Here's the code:

    Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • You are starting out as a .xlsm workbook, then saving that workbook as .xlsx then closing the workbook before the code finishes.

    Once you saveas a new name that workbook, you are no longer on it.

    This is why UncleString was trying to show you how to create a separate workbook so you still stayed on the original workbook.


    Fortunately, your last post has given us enough information that we can create and save a workbook on the fly.

    That part of the code is this.

    Code
    1. '----------------Save selected sheets as a new workbook--------------------
    2. Sheets(Array("Disclaimer Then", "Sign In", "Glucose Data", "INR Data", _
    3. "Exercise Data", "Glucose Chart", "INR Chart", "Exercise Chart", "LOG ENTRIES", _
    4. "Meals Data", "Weight Data", "BP Data", "Meals Chart", "Weight Chart", "BP Chart", _
    5. "Default Sheet")).Copy
    6. ActiveWorkbook.SaveAs AtchFile
    7. ActiveWorkbook.Close
    8. '----------end of save selected sheets as new workbook---------------

    The correct file does show up at the addattchment line



    For me, I can't get past the ".send", it has nothing to do with the attachment but has something to do with the gmail settings possible an app password, not sure though.


    Here is the full code now, I had to comment out the call macro lines


  • Hi Dave. I completed the saveas line as AtchFile, 51

    I copied the code you provided into a copy of my program. (Is there a way to copy this without line numbers? When I copied it looked as though there was no line numbers, but when I pasted, somehow it included the line numbers, causing syntax error on every line. I went line for line and deleted the numbers LOL)

    ...Anyway

    In the amended sub, you made a bit of an error on one of the sheets that you copied into the Array. You put "Disclaimer Then"...should be just "Disclaimer". You had mentioned that you had problem with the 'send' and I hoped that this was the problem, but no luck.

    You mentioned that there may be a problem with the email code, but the email works perfectly when sending the full file...no issues at all.


    Also there is personal content in the 'Default" sheet, such as email password etc., that must also be cleared before creating the new workbook (Lines 73 & 74) of my code That I sent in the #26 post above.

    By creating the new workbook with no email password, should not be an issue, as we are using the original file to 'send'


    I inserted the deletion just above the... Sheets(Array... line

    Code
    1. Application.ActiveWorkbook.Worksheets("Default Sheet").Select
    2. Range("B38,B44").ClearContents


    When I run the program I get an error "Subscript out of range" on the new code: Sheets(Array.....

    I really appreciate your help.

    Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • I moved the save new workbook down the code to after you have edited the sheets.

    Cleaned up the code a bit

    All the sheet names in the code need to match the sheet names on the sheet or you will get subscript out of range


    If you do copy the code and it pastes with the line numbers, experiment with pasting it on a sheet and using text to columns to remove that first space.

    Oh, I commented out the .send so I can get to the end of the code.

  • Hi Dave.

    I did find one of my sheet names was incorrect. "Meal Chart" should have been "Meals Charts" (Plural). argghhh.

    Line 63 of your code is where the password is cleared. I moved it to just before the rest of the delete happens...before line 142

    I even tried to remove this line altogether to see if it may have been the issue and still got the below results

    I update my code with yours. I got error on (set sh as wb.sheets... Object does not support method so I removed the wb and that worked. so set as:

    sh = Sheets("Home Sheet")...seems good.

    I placed a few msgbox's after the variables: User, Pass & Em. the first two show as .844444444 & the Em shows blank. I made sure that the ranges are correct. The code runs right to the 'Send' statement...error: "At least one of the from or sender fields is required and neither was found"

    The original file has the password deleted from "Default Sheet". Can't see why. (Probably the problem) The new file is saved properly as necessary) in my folder but just not sent.

    Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • HEY Dave...Thanks...I didn't know about the 'Copy Icon' very very helpful info.haha

    Actually I re-copied your sub again using the 'Copy Icon' and must have missed something when I originally did it manually. The copied file in in my folder . Only problem now is that I'm getting a transport error when it tries to send, because the email password is deleted prematurely in the original file. But we're much closer...almost got it

    I moved the deleted line to after the saveas and it sent but not with deleted content. Tonight I'll just ad a resave line of code after the cell deletion so it saves before it sends.

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • HEY DAVESEXCEL...IT WORKED!

    perfectly. You are a genius. I don't know how you guys do this with the limited information you get from us (me especially I guess).

    Thanks to all of you that contributed Dave, Uncle S, RoyUK & Carim (originally)

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)