Mail specific Worksheet from Excel considering specific criteria

Dear Excel Experts,
I have Excel Workbook by name “ASR” in which there is a worksheet i.e. “ASR”, and requirement are as follows;
A. After clicking on button with name “ASR Approved”…
1. “Activity Status” in cell I233 is to be updated/filled with text ”COMPLETE”
2. “Activity Completed on” in cell J233 is to be updated/filled with DATE (when “ASR Approved” Button is clicked).
3. Worksheet “ASR” is to be saved in folder / path “C:\Users\rka085\Desktop\ASR_Approved"
4. Worksheet “ASR” is to be mailed with attachment of “ASR” worksheet ..
.to = "[email protected]"
.CC = "[email protected]"
.BCC = "[email protected]"
.Subject = "ASR 2021-213 has been Approved" (2021-213 is cell value of B233)
.Body = "Dear Team”
“ASR 2021-213 has been Approved"
“Thanks and Regards”
“Documentation - Team ”
“GTI ”
5. Line (B233 to K233) is to be copied and to be pasted into file “ASR_Status”(in folder C:\Users\rka085\Desktop) in available blank row from top data”
--------------------------------------------------------------------------------
B. After clicking on button with name “ASR Rejected”…
1. “Activity Status” in cell I233 is to be updated/filled with text ”REJECTED”
2. “Activity Rejected on” in cell J233 is to be updated/filled with DATE (when “ASR Rejected” Button is clicked).
3. Worksheet “ASR” is to be saved in folder / path “C:\Users\rka085\Desktop\ASR_Rejected"
4. Worksheet “ASR” is to be mailed with attachment of “ASR” worksheet ..
.to = INPUT WINDOW TO BE DISPLAYED for updating E-MAILS & REASON FOR REJECTION
.CC = "[email protected]"
.BCC = "[email protected]"
.Subject = "ASR 2021-213 has been Rejected" (2021-213 is cell value of B233)
.Body = "Dear Team”
“ASR 2021-213 has been Rejected"
“Thanks and Regards”
“Documentation - Team ”
“GTI ”
5. Line (B233 to K233) is to be copied and to be pasted into file “ASR_Status”(in folder C:\Users\rka085\Desktop) in available blank row from top data”


I have tried a vba script however its not working, kindly refer attached file for details
Kindly help
Thanks you very much advance.

RavindraK

Dear Excel Experts,

Kindly help.

Thanks you very much in advance.

RavindraK

Excel Experts,

Please help me to resolve the problem.

Thank you so much in advance.

RavindraK

Try these two subroutines. Only point five has not been incorporated since that master summary file has not been shared.

Please Login or Register  to view this content.

Thank you so much Sir, your code is just excellent !! i have added few things in it/modified (please refer attached file for same - "ASR_29May21 - dummy").
however, Sir, my last request as below is still pending, kindly relook into it and help me.(said master file is attached : "ASR Tracker_Master File.xlsx")
5. Line (B233 to K233) is to be copied and to be pasted into file “ASR_Status”(in folder C:\Users\rka085\Desktop\ASR Tracker_Master File.xlsx) in available blank row (for example here row 11th is blank)from top data”

Once again thank you so much for your great response/code

Thanks a lot in advance.

RavindraK

Here is the updated code

Please Login or Register  to view this content.

Yes Sir,
The code is working as fine.
Only thing that while copying file into folder or data(line) into master file it is just copying and pasting, evenif same ASR No. file is there in folder(ASR_Rejected or ASR_Approved) or same ASR No. data(line) is there in master file is there in ASR Tracker_Master File,

If error message /pop-up will appear saying "File of ASR___is ALREADY there in folder_______" or "data(line) of ASR___is ALREADY there in ASR Tracker_Master File",it will help us to avoid duplicate entry or files, Please help

Thank you so much in advance for your great help as usual.


RavindraK

Here you go. I had to reorder the code to stop sending the email if the ASR already existed in the master file. Let me know if you have questions.

Please Login or Register  to view this content.

Sir,
It's working for APPROVED case, however , not working for REJECTED case; after clicking on REJECTED it is duplicating.

Kindly help.

Thank you so much in advance for your great help so far / as usual.

RavindraK

It appears that the cell reference b233 was changed to b234 without updating the

Please Login or Register  to view this content.

to also hold b234. See if that change fixes the issue.

Exactly Sir,
I have changed the reference as highlighted by you and now code is working with both the cases as expected.

Thank you so much for your great support, valuable time and superb code as usual.

You are the really charm !!

Thanks and regards,

Ravindrak 🙏🙏