Auto rotation in sequence and auto-mails from Excel

Dear Excel Experts,
I have Excel workbook with 3 worksheets i.e. PC list, PC Confirmers and Mail Format..
Requirement

1. Process Confirmation (PC) listed in Worksheet "PC list" are to be assigned automatically in rotation i.e. Process Confirmation (PC) are to be assigned in sequence to listed PC Confirmers in Worksheet "PC Confirmers" for example in this case, now we have 25 PC Confirmers, so 25 Process Confirmation (PC) in sequence are to be assigned to 25 Process confirmers and then again from 26th Process Confirmation (PC) onwards, same 25 PC Confirmers in sequence are to be allocated and so on in same manner till end of the Process Confirmation (PC) list i.e. in tis case we have 71 No of Process Confirmation (PC). First week is to be defined based on value in cell L2 in worksheet "PC list"; 1st 25 Process Confirmation (PC) to 25 PC Confirmers and 2nd 25 Process Confirmation (PC) to same 25 PC Confirmers and so on till end of the Process Confirmation (PC) list.

2. Auto-mails are to be sent to all the PC Confirmers to whom Process Confirmation (PC) is assigned as per Worksheet "PC List" in given mail format as per Worksheet "Mail Format"

This task we are doing manually every week, which is hectic

I am very grateful if you help me to do this task automatically i.e. auto-assignment and auto-mails.

Attached for details or reference (text and source is also mentioned in Worksheet "Mail Format", Kindly help

Thanks you very much advance.

RavindraK

Dear Excel Experts,

In case requirement no. 1 is difficult, i'll do it manually, request you to please help me to do "Auto-mails" as mentioned in requirement 2.

Thanks you very much advance.

RavindraK

Dear Excel Experts,

Kindly help,

Thank you very much in advance.

RavindraK

This solution addresses your 2nd request. It will ask you for what week you want to send emails.

Please Login or Register  to view this content.

Dear Sir,
Thanks a lot for your super code and help !!
Apart from below minor errors.

1. In mail body, from and to date is not appearing
Process Confirmation (PC) for Week - 18 of Year 202 (26-Apr-2021 to 02-May-2021)
3. All mails just preparing but not sending by clicking “Send Email” button
3. If possible…below word are formatted in BOLD font

Dear [Name],
You are requested to complete Process Confirmation (PC) as per details below
Process Confirmation (PC) for Week - 18 of Year 2021 ([from date and to time])
Process Confirmation (PC) name - [PC Name]
Process Confirmation (PC) ID or Number - [PC ID]
Function - [Function]
Type - [Type]
After completion of above assigned Process Confirmation (PC) you are requested to submit your observations in MAVIM and ….
1. Please Provide Feedback to confirmee on execution process, if any
2. Please ask confirmee for improvement ideas, if any
3. Kindly place (Documentation) Improvement idea(s) and training need(s) VMS board/functional meeting.
and lastly
Sometime we use to send Fresh mail like you defined in vba and some time Reminder; in Reminder mails only changes in subject i.e. prefix will be Reminder to defined subject

for example
Fresh mail Subject :
Process Confirmation (PC) assigned to Alok Mishra for calendar week 18 of Year 2021

Reminder mail subject :
Reminder - Process Confirmation (PC) assigned to Alok Mishra for calendar week 18 of Year 2021

Kindly make provision for the same

-----

Sir, Thanks in a advance for your great code as usual.

RavindraK

Here is the updated code

Please Login or Register  to view this content.

@maniacb amazing! Sir many thanks for your great codes and superb help as usual, You are Champ and my favorite Excel Guru !!! Thank you so much once again !!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon (Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.