Posts by JimmyB

    Hi. I'm stuck near the last line of code. All of the rest works great but I can't get the syntax right to delete a row of data on the line: Active sheet.rows(Sres).Delete Shift:= shiftxlUp

    When I step through it I get the error "Type Mismatch

    Any help will be appreciated


    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

    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)


    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 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

    Application.ActiveWorkbook.Worksheets("Default Sheet").Select

    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

    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

    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'


    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

    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.


    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:


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

    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).


    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

    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

    Well You know That's a good idea. I can use an if condition like you recommended if I have to, but it's driving me nuts. The code I'm using should work and it is cleaner than going around the problem. I just can't figure out why I get the error. I googled it and not getting any results either.

    Thanks Ashu, but that isn't an option. I'll be distributing the apps thousands of times (plan) and I need it to automatically delete this line of code after I've assigned a password to the file. I can't load the program each time...really not feasible...But thanks for the suggestion. really appreciate it