Creating a Monthly Customer Summary Invoice

[Jackson_Hew (JH)]

Hi,

I am stuck on creating a solution as I am at a beginner level and I do not know how to implement the solution for my company.

Objective: Create an email draft using Studio to automate and generate a customer invoice every Monday based on the customer’s purchased history and summary. By refencing the Excel workbook “Summary.xlsx”

1. Start by opening the Excel workbook with all the table data, read the values and create an Outlook mail draft.
2. Each customer is assigned to a sales person (unique). There are no duplicate customers for each salesperson.
3. There is a point system whereby if a customer purchased a certain amount of products they can obtain points and they can claim a discount amount.

> “Eg. If Customer A purchased Product A, 10 points is given. Need 100 points to claim a 10USD discount which will be stated inside the email draft notifying the customer that they need 90 more points to claim the 10USD discount voucher.”

4. The output:-

Within the Outlook draft,
(Body Content)

Dear <cust_code> <cust_name>

Thank you for the purchase!
Your current point is <total_accu_points>
You require additional <req_points> to obtain <voucher_amount>

*As of purchased <current_date>

5. The email draft will be sent to the sales person when I run the UiPath Assistant.

The end result should look like this:-

The logics,

For each sales_person,

if Sales 1 is found, do For Each cust_code {
Where by referring to Sales worksheet
cust_code = vlookup(B2, Sales, A:B, 2,0)
cust_name = vlookup(B2, Sales, A:C, 3,0)
total_accu_points = vlookup(B2, Sales, A:D, 4,0)
req_points = total_accu_points - gain_points
voucher_amount == req_points, vlookup(D2, Points, A:C, 3,0)
}

If Sales 2 is found, do For Each cust_code {
Where by referring to Sales worksheet
cust_code = vlookup(B2, Sales, A:B, 2,0)
cust_name = vlookup(B2, Sales, A:C, 3,0)
total_accu_points = vlookup(B2, Sales, A:D, 4,0)
req_points = total_accu_points - gain_points
voucher_amount == req_points, vlookup(D2, Points, A:C, 3,0)
}

If Sales 2 … and so on

*As each sales person is handling multiple customer, how do I send multiple email drafts to the salesperson?

Summary.xlsx (14.1 KB)

[pratik.maskar]

Hi @Jackson_Hew ,

Glad you reached out to #UipathCommunity for help.
Here are the steps you should refer,

1. Create a new UiPath project and name it “Customer Invoice Automation”.
2. Use the “Excel Application Scope” activity to open the “Summary.xlsx” workbook.
3. Use the “Read Range” activity to read the data from the “Summary” sheet into a datatable variable.
4. Use a “For Each Row” activity to loop through each row in the datatable.
5. Within the “For Each Row” activity, use an “If” activity to check if the purchase date of the current row falls within the previous week (i.e. Monday to Sunday).
6. If the purchase date falls within the previous week, add the product name, quantity, and price to a string variable that represents the invoice summary.
7. Once the “For Each Row” activity is complete, use the “Send Outlook Mail Message” activity to send an email to the customer with the invoice summary as an attachment.
8. Fill in the required email fields such as the recipient, subject, and body.
9. Attach the invoice summary as an attachment to the email.
10. Save and run the automation.

Hope it helps, Happy Automation
If helpful mark it as a solution so that it will help others as well

Regards,
@pratik.maskar

[Jackson_Hew (JH)]

Hi @pratik.maskar ,

Thanks, I have found the way to get the question done. Thanks for the suggestion.