Last edited by TraceyH; 06-27-2019 at 12:11 PM.
Can you be a little more specific?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
LOL - apologies! I put a comment that I couldn't work out how to delete so had to edit it and leave 1 character in it. The head banging is because I had just spotted thatthe Approved save was carrying over the original requestors username and date but I managed to fix it all by myself, which I am fairly chuffed about.
ANYWAY - WOW! Thank you so much for the email addendum - can't believe how quickly you managed to work that out! Have been studying the code and there's a lot that is WAY over my head, but I can sort of understand the logic!?!
Not wanting to be greedy, or take the proverbial, but would there be any way to add a similar thing to the Approved macro, to email another address? I've updated the list so that the Pending Macro will send to the Budget Holders Email, but the Approved macro needs to go to the Processing Team's email, so I added an additional column to list.
Have a look and see what you think. Could I just copy what you have done to Pending to make it work in Approved?
1. Glad you can handle some changes yourself.Sometimes I wish others would make the effort
2. The Outlook eMail is something I have in a 'toolkit' of macros I use a lot
3. It's basically a copy, but there are some changes that needed to be made. I think I understood, so check this out
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Am now testing it at work (this is where the head banging emoji will REALLY be needed!)
When I run Pending, I get a "Compile error: can't find project or library" and it's highlighting the 'Function PendingPath() As String" in yellow and then Environ in blue?
When I run Approved, I get a "Compile error: can't find project or library" and it's highlighting the 'Sub SaveDocumentApproved()" in yellow and then Environ in blue?
What on earth am I doing wrong?!
Probably nothing - compiles and works here
1. In the Editor, check Tools, References just in case
2. Restart the computer
3. Try this version - I ran it through Code Cleaner which just gives back clean code text -- no changes to macro
But my money is on #2
Capture.jpg
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Oh. My. At home, that's not an issue (I'm running Excel 2016) but at work I'm running Excel 2013 and so only have MS Office 16.0 Object Library - this applies to every user in the organisation as well, so now what?!?!
Ignore me - I just had to untick the MISSING notification in References and all is good.
Will every user have to do that before the form will work for them?
PS - is there any way to make the reference in the email a hyperlink, or am I expecting to much / being greedy?!?!?
PPS - have a grovelled adequately so far for all your help? I cannot express to you enough my gratitude!!!
Ignore me again - worked this out for myself too!Originally Posted by TraceyH
Would still love to get it set up with a hyperlink, mind...
Try this
Not 100% sure about fomatting since if you're using an Exchange server, it might add some text
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Interestingly, the Approved one works but the Requesting on comes back with "We can't find 'HL-MGT-SVR02\ChildrenCentreGeneral\Budgets Family Centre Service\2019-20\test\PendingApproval\blah blah blah" - I'm guessing that theHL-MGT-SVR02\ChildrenCentreGeneralpart is something to do with how we're set up? Shame really as if it could just point to m:\\ instead of that, all the rest of it is OK.
I assume the Approved one works because it comes from a Pending version?
Works for me
Make sure that there's no leading of trailing spaces in the folder names
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi Paul
It's become apparent that I need to include a time in the file name, as there are cases where someone may complete or approve more than one request on the same day. I've had a look and thought I could perhaps just change the format part of the paths from Format(Date, "dd-mmm-yy") to Format(Now, "dd-mmm-yy_hh:mm") but I just get back a Run-time error '1004' Method "'SaveAs' of object '_Workbook' failed
Clearly I am doing something wrong and was hoping you could help me (again!!)
You can't use a colon in a path
Try something like
Format(Now, "dd-mmm-yy-hh-nn")
although personally I like
Format(Now, "yyyymmdd-hhnn")
since it sorts better
NB: for 'minutes' the placeholder is n 'nancy' to differentiate it from months 'm' Mary
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
You're a star - thanks!
Posting Permissions
Reply With Quote
