How to use BOT to automatically send email to separate receiver in a table?

[kvngo94]

Hope you guys can help me with below task

I want to use BOT, send email to send email every morning at 6AM

I have below data base that has 3 unique suppliers :

*all above emails are examples only

SUPPLIER	EMAIL	ITEM NAME
CSK WEBBING	cskwebbing@gmail.com	ITEM 1
CSK WEBBING	cskwebbing@gmail.com	ITEM 2
AAP	aap@gmail.com	ITEM 3
CHUNGA LABEL	chungalabel@gmail.com	ITEM 4

1/ Email 1 How to set up BOT Email so that every morning it sent 3 emails as below 

    to : cskwebbing@gmail.com

    Subject : MATERIAL REQUEST / CSK WEBBING / 2022-04-22 (Today)

   Email body template : 

SUPPLIER	COUNTRY	ITEM NAME
CSK WEBBING	VN	ITEM 1
CSK WEBBING	VN	ITEM 2

2/ Email 2

    to : aap@gmail.com

    Subject : MATERIAL REQUEST / AAP / 2022-04-22 (Today)

   Email body template : 

SUPPLIER	COUNTRY	ITEM NAME
AAP	US	ITEM 3

3/ Email 3

    to : chungalabel@gmail.com

    Subject : MATERIAL REQUEST / CHUNGA LABEL / 2022-04-22 (Today)

   Email body template : 

SUPPLIER	COUNTRY	ITEM NAME
CHUNGA LABEL	KOR	ITEM 4

[WillowMobileSys · Accepted solution]

You will want to create a Bot with an Event of type "Schedule" set to run daily at 6am.  Set the "ForEachRowInTable" property to on.  Set the table to your Supplier table and in the Condition use an expression that selects the list of Suppliers from your "Items" table using the UNIQUE() function to get a list of unique Suppliers.

Set up your Email process step to send an email to the "current" Supplier.  For the body, create a template that selects the list of Items from your "Items" table based on the "current" Supplier and format it accordingly.

Here are some articles to help get started:

https://help.appsheet.com/en/articles/4865380-event-the-essentials#h_05cba92712

https://help.appsheet.com/en/articles/961741-email-templates

https://help.appsheet.com/en/articles/3416532-unique

[kvngo94]

Hi Willow, 

Thanks for your reply

1. Below is my actual event set up

   Could you please advise why I turned on the ForEachRowInTable, but it still send 2 separate emails to the CSK WEBBING suppliers ( because it has 2 rows ? ) ? 

   How to make it gather all related items and send to each supplier at 1 email only ? 

   Do you mean that I need to put a UNIQUE() condition to the below event ? Could you please advise more of the formula ? 

  FYI, SUPPLIER is a ref column to another table

2. Could you please advise more and how to put the UNIQUE() formula ?

    I tried UNIQUE(MATL REQUEST[SUPPLIER]) but it shows below error

   (MATL REQUEST is the name of my table)

[kvngo94]

Hi again, 

Hey do you mean that 2 need to create 2 tables : 

- Supplier tables that have unique suppliers rows

- Items tables that have the column Supplier ref to the original Supplier table ?

[kvngo94]

Ok thank you so much I figured it out

I created 2 tables : Supplier and Items

At Bot Even : I mentioned source table from Suppliers by IN([ID],UNIQUE(MATL REQUEST[SUPPLIER ID]))

At Bot Process : I set up task along as you said and it works now !

[kvngo94]

Hello again, 

Could you please help me more with one issue below ?

What if at the Items table, I add one more column name "EMAIL TYPE" which will be used to specify the email type / subject : 

EMAIL TYPE	SUPPLIER	ITEM NAME
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)
MATERIAL	AAP	100% POLYESTER 75D/72F BRUSHED TRICOT, 170GSM,WITH 0.15MM GRAY PVC BACKING,WITH W/R
MATERIAL	CHUNGA LABEL	WAIST BELT SIZE TAG XS/S 26-32 in (66-81 cm)
PRICE	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)
PRICE	CHUNGA LABEL	WAIST BELT SIZE TAG XS/S 26-32 in (66-81 cm)

for example below CSK WEBBING supplier, how to make it will automatically send 2 separate emails as : 

1. Email 1 

   Subject : MATERIAL REQUEST / CSK WEBBING VN / 2022-04-23

   Email body template : 

EMAIL TYPE	SUPPLIER	ITEM NAME
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)

2. Email 2

   Subject : PRICE REQUEST / CSK WEBBING VN / 2022-04-23

   Email Body Template : 

EMAIL TYPE	SUPPLIER	ITEM NAME
PRICE	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)

[WillowMobileSys]

There are two ways to handle your need for Email Type:

1) Create conditional expressions to decide which text/format to use.  Most areas of the email Task can take an expression to provide dynamic capabilities for constructing the email.  For example in the Subject property of the Email Task you could do something like:

IF([Email Type] = "Price", <<enter Price text here>>, <<enter Material text here>>)

This may mean you need to adjust any templates to be dynamic as well.  There are template IF...END statements that can be used.

https://help.appsheet.com/en/articles/2697069-template-if-expressions

2)  Copy the bot and tailor it to be for only a single type.  This includes adjusting the Condition to query for Items of only that type.  This is the easiest and if you think you will have more email types going forward, it will be the simplest to maintain.  Any issues in a particular email type, you will know exactly where to go for the fix.

[kvngo94]

Thanks Willow, 

In case, could you help me with 1 more situation where : 

- I have one more EMAIL DATE column as below

EMAIL TYPE	SUPPLIER	ITEM NAME	EMAIL DATE
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)	2022-04-23
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)	2022-04-23

What I want is, if EMAIL DATE < TODAY(), after sending email for these rows, this column will increase 3 days like 

EMAIL TYPE	SUPPLIER	ITEM NAME	EMAIL DATE
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)	2022-04-26
MATERIAL	CSK WEBBING	NYLON WEBBING DOUBLE (840*420)	2022-04-26

Could you advise me how to do this? I have an idea using If  EMAIL DATE < TODAY() then EMAIL DATE = EMAIL DATE + 3,  but really don't know where to put this ? in <<Start:expression>><<End>> ? or in the email template docs ??