How to send an outlook email based on raw data

[RajeshT (Rajesh)]

Hi Team,

I need to send an email to users based on status of text file line items as below:
G0001359|NOR ZIHAM BINTI KASMIN|Approved|2,670.50 MYR|DH/00053/19|12-Apr-2019|test@gmail.com
G0001360|NOR ZIHAM BINTI KASMIN|Rejected|2,670.50 MYR|DH/00053/19|12-Apr-2019|test1@gmail.com
G0001361|NOR ZIHAM BINTI KASMIN|Approved|2,670.50 MYR|DH/00053/19|12-Apr-2019|test2@gmail.com
G0001362|NOR ZIHAM BINTI KASMIN|Pending|2,670.50 MYR|DH/00053/19|12-Apr-2019|test3@gmail.com
G0001363|NOR ZIHAM BINTI KASMIN|Approved|2,670.50 MYR|DH/00053/19|12-Apr-2019|test4@gmail.com
Example
Send an email to test@gmail.com,if status is approved and the values i want to key in email content(Save its as string) .
I have converted the text file in to excel file as below,after that i need your inputs:

[KarthikByggari]

1. Read the excel file using Read Range activity under excel application scope. The output of this activity is a datatable.

2. Filter the datatable using Filter Datatable activity. If you want to send email for each status, you no need to filter. If you want to send email only particular status then you need to filter the datatable.

3. Loop thru the filtered rows of datatable using For Each activity and send an email inside the body of the for each activity.

Regards,
Karthik Byggari

[RajeshT (Rajesh)]

Hi @KarthikByggari ,

Thank you for your response,
I have converted text file to excel and filtered the data based on status,after that could you please help to add the code for loop.
I want to add the each cell data as string and send an email as below.
Dear Sir/Madam,

This is to bring to your kind notice that we have received the claim filed by you on 12-Apr-2019 corresponding to the policy # G0001359 for NOR ZIHAM BINTI KASMIN .

The status of your claim is Approved . The amount payable is 2,670.50 MYR

Please quote your Claim No. DH/00053/19 for any further conversations or clarifications.

ClaimNotification.xaml (10.8 KB)

[RajeshT (Rajesh)]

@KarthikByggari,

Appreciate if you help to get the excel data in to strings,so that which i can use the data for email content as above.

[KarthikByggari]

Will send you soon. Sorry for delay.

Regards,
Karthik Byggari

[Manjuts90]

@RajeshT You can use string format method like below.

Str = "Dear Sir/Madam,

This Is To bring To your kind notice that we have received the claim filed By you On {0} corresponding To the policy # {1} for {2} .

The status Of your claim Is {3}. The amount payable Is {4}

Please quote your Claim No. {5} For any further conversations Or clarifications"

Str = String.Format(Str,ClaimDate,PolicyNumber,PersonName,Status,Amount,ClaimNo)

Here ClaimDate,PolicyNumber,PersonName,Status,Amount and ClaimNo are variables which contains required data.

[RajeshT (Rajesh)]

String.Format(Str,ClaimDate,PolicyNumber,PersonName,Status,Amount,ClaimNo)

@Manjuts90 Thank you for the inputs.
How to assign the excel values to the above mentioned variables.
I have tried as attached,please check and revert back.
ClaimNotification.xaml (15.5 KB)

[Manjuts90]

@RajeshT I am unable to open your workflow due to version problem.

String.Format(Str,ClaimDate,PolicyNumber,PersonName,Status,Amount,ClaimNo)

If using for each datarow and placing the values then you can try like below.

You can replace ClaimDate variable with row(“claimno columname/column index”).ToString

You can do similar things for other variables also.

Where row is for each datarow variable.

[RajeshT (Rajesh)]

Hi @Manjuts90,

With your inputs i have changed and i am getting below error:

Code as below:

[RajeshT (Rajesh)]

Hi @KarthikByggari ,

Do you got time to look in to my request.Appreciate your help.

[Manjuts90]

@RajeshT Among PolicyNo or Column1 which one is column Name.

As per your code Column1 is column name of policy number. Where PolicyNo is variable which contains value from Column1.

Either use row(“Column1”).ToString or PolicyNo Instead of row(“PolicyNo”).ToString.

Replicate the same with other variables.

The Reason behind your error is PolicyNo doesnot exists in your datatable. Since column names in your datatable is like Column1,Column2 etc.

[KarthikByggari]

In the Body of Send Outlook Mail Message, why you entered row("PolicyNo") in string.Format. It is not required.

You have to just enter PolicyNo in the string.Format

[RajeshT (Rajesh)]

Hi @Manjuts90,

You are correct,my column names are Column1,Column2…
Based on the inputs i have changes as below ,but getting outlook error:

[RajeshT (Rajesh)]

Hi @KarthikByggari,

I am getting same error,if i changes as below also:
String.Format(body_Text, PolicyNo, PolicyHolder, PolicyStatus, PolicyAmount, ClaimNo, PolicyDate)

[KarthikByggari]

what is the value of body_Text ?

[RajeshT (Rajesh)]

Hi @KarthikByggari ,

For bold_text ,there is duplicate variables i have removed one and it is working fine.
Also i have added .To String to the variables as below:
String.Format(body_Text, PolicyNo.ToString, PolicyHolder.ToString, PolicyStatus.ToString, PolicyAmount.ToString, ClaimNo.ToString, PolicyDate.ToString)
Thanks a ton!

[KarthikByggari]

Glad to know you figured it out.

Regards,
Karthik Byggari