Hi everyone I would like to seek your help in doing a macro. I have four sheets:
My idea is:
I will send the gift to emails listed in sheet 3 and clear the sheet. Both sheet 1 & 2 will will be updated frequently and emails will be sent after each update.
EXAMPLE:
Sheet 1 (Main database)
Sheet 2 (Optin form: Users who have registered for a free gift which will be sent to them on scoring more than 100.)
Sheet 3 (Outbox: Gifts must be sent to these members. This list will be cleared once emails are sent.)
Sheet 4 (Sent Emails: List of members who received the gift.)
Macro Result:
Sheet 3
Sheet 4 (d & e are newly added)
If anyone has a better idea to simplify the process, please share.
Please help me with this, I will be thankful to you forever.
- The first one is a list of all members and their score.
- Sheet 2 is populated as users fill a optin form on our website. They'll be registering for a free gift which will be sent to them on scoring more than 100.
- Sheet 3 will be empty and will be filled up by the macro.
- Sheet 4 is the list of members to whom the gift email has been sent.
My idea is:
- Macro must first, check if the usernames listed in sheet 2, have scored greater or equal to 100 (in sheet 1).
- Then, check if the username/email is not already listed in sheet 4.
- Finally once the above two conditions are met, copy the filtered rows to 3rd & 4th sheet.
I will send the gift to emails listed in sheet 3 and clear the sheet. Both sheet 1 & 2 will will be updated frequently and emails will be sent after each update.
EXAMPLE:
Sheet 1 (Main database)
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Username | Score | ||
2 | a | 150 | ||
3 | b | 40 | ||
4 | c | 90 | ||
5 | d | 100 | ||
6 | e | 110 | ||
7 | f | 105 | ||
Sheet1 |
Sheet 2 (Optin form: Users who have registered for a free gift which will be sent to them on scoring more than 100.)
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Username | |||
2 | a | a@xyz.com | ||
3 | b | b@xyz.com | ||
4 | d | d@xyz.com | ||
5 | e | e@xyz.com | ||
6 | z | z@xyz.com | ||
Sheet1 |
Sheet 3 (Outbox: Gifts must be sent to these members. This list will be cleared once emails are sent.)
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Username | |||
2 | ||||
Sheet1 |
Sheet 4 (Sent Emails: List of members who received the gift.)
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Username | |||
2 | a | a@xyz.com | ||
Sheet1 |
Macro Result:
Sheet 3
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Username | |||
2 | d | d@xyz.com | ||
3 | e | e@xyz.com | ||
Sheet1 |
Sheet 4 (d & e are newly added)
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Username | |||
2 | a | a@xyz.com | ||
3 | d | d@xyz.com | ||
4 | e | e@xyz.com | ||
Sheet1 |
If anyone has a better idea to simplify the process, please share.
Please help me with this, I will be thankful to you forever.