send email by user name in Excel file

[Anonymous]

Hi Team,

Is it possible in microsoft flow to send email by excel column (only project manager name not email address),because our manager dont want to add email address manually in excel file.

Status         Project      Item      Type      Start Date            End date              project manager      
Pending       SwS          Java                    15/05/2018           15/08/2018               Dominique philippi
Pending       SI              Python                 08/05/2018           17/08/2018               Arnaud ferrand
Pending       GSS          Ruby                    06/05/2018           05/08/2018               Janice Lagundi

Please reply me.

Regards
Vikas kaushik

[vecerpa · Accepted solution]

Hi @Anonymous,

It is possible to search for user via function in Office 365 Users connector.

I was playing with it and it seems like you have to search just for surname of user that is filled in project manager columnt of table as the whole name will not deliver result in my case. This is caused by that we have in display name specific format, not just a name.

The whole function to get UPN and email of user by name can look like this:

Expression in search is just splitting the Name of project manager from excel that I previously stored in variable Surname:

last(split(variables('Surname'),' '))

After searhc user action you can use email notification with email that was found by User Search.

Hope this helped you

P.

[Anonymous]

Hi vecerpa,

Thanks for your reply..
please check below concerns regarding this....

1. this file comes everyweek in excel format......

2. Need to create event reminder flow between start date and End date,

3. inside excel only PM full name is available (no provision for first name and surname).

4. Team dont want to add PM email address manually everweek in excel file (on onedrive for business).

5. is it possible to create 50 users email id in other location like one drive for business (in excel file) then fetch user email id from there to run the flow....because this flow is only for our team.....in office 365 user group there might be possiblity for dulication of surname .........and also surname is not available in the excel file......

6. everything is ok but not able to solve this issue.....(how to send email with PM name only) without email address in excel file.

Please reply....because its a urgent task.

[Anonymous]

Hi @vecerpa,

Thanks for your reply..
please check below concerns regarding this....

1. this file comes everyweek in excel format......

2. Need to create event reminder flow between start date and End date,

3. inside excel only PM full name is available (no provision for first name and surname).

4. Team dont want to add PM email address manually everweek in excel file (on onedrive for business).

5. is it possible to create 50 users email id in other location like one drive for business (in excel file) then fetch user email id from there to run the flow....because this flow is only for our team.....in office 365 user group there might be possiblity for dulication of surname .........and also surname is not available in the excel file......

6. everything is ok but not able to solve this issue.....(how to send email with PM name only) without email address in excel file.

Please reply....because its a urgent task.

[vecerpa]

Hi @Anonymous,

1. this file comes everyweek in excel format......

Better to use one shared file that is stored in OneDrive for Business or inside SharePoint document library as for now it is possible to load data from groups (Sharepoint team site document library) or sites (SharePoint site document library). Define specific excel file where each editor have access to. No need to have multiple files... etc....

2. Need to create event reminder flow between start date and End date,

There is no problem to create Flow that will be scheduled to trigger at specific time and compare actual date (utcnow expression) against start date and end date.

3. inside excel only PM full name is available (no provision for first name and surname).

Ok, so that is reason why I used split and last expressions to get for example last name. If there is First Name + Last Name format of Project Manager name that is stored in excel file column.

4. Team dont want to add PM email address manually everweek in excel file (on onedrive for business).

If those users (Project Managers) are internall one then there is no problem to look for valid email that is configured for such user via Office 365 Users connector.

5. is it possible to create 50 users email id in other location like one drive for business (in excel file) then fetch user email id from there to run the flow....because this flow is only for our team.....in office 365 user group there might be possiblity for dulication of surname .........and also surname is not available in the excel file......

Yes it is possible, but again as I write in previous point if those users are in your Office 365 tenant with assigned email addresses the excel file you want to create is useless, such information is stored with your user account - in Office 365.

P.

[Anonymous]

Hi @vecerpa

Thanks for your help and reply....
yes all the users is in office 365.....
can you please share me more details and screenshot, how can i achieve this target....

for example any PM name which email ID on office 365 exists (only name in excel file),how can i send the email to that Project manager, please consider full name not first name or surname.
can you please share further steps in screenshot (only for one or two PM).

Thanks in advance....

[Anonymous]

Hi @vecerpa
Please reply if no first name or last name provision in excel file and with same name other user is also available in office 365 tenant,
so how can flow get exact user email address for our team,

please share some steps or screenshot....

Regards
Vikas Kaushik

[vecerpa]

Hi @Anonymous,

So the whole Flow can look like this:

1. I use manual button trigger just for test purposes, you should change it to something like schedule or whatever you want to trigger your Flow.

2. As input i choosed Excel Online Business connector it can load excel files not only from OneDrive for Business but also SharePoint document library.

There is need to have table inside excel file, but this is not a problem.

3. I have to prepare some variables inside Flow as the date that comes from Excel Online is in specific format - it represent count of days since 01/01/1900 so there is need to transform it to something comparable to utcnow(} that returns datetime in ISO format.

All variables are strings.

4. I load all rows from excel sheet and for each item I compare StartDate and EndDate against current date - utcnow(). And of course as I mention there is need to transform dates from excel online to ISO format. It can be done with this expression

addDays('1900-01-01T00:00:00Z',int(item()?['Start Date']))

and the equal expression to be sure that we are inbetween StartDate and EndDate

@and(greaterOrEquals(utcnow(),variables('StartDateInt')),lessOrEquals(utcnow(),variables('EndDateInt')))

so the whole part of Flow looks like this

5. If condition result is true there is need to store FullName in variable and then I use Office 365 users connector to lookup for Surname that I parsed from FullName of project manager via split() and last() functions, whole expression looks like this

last(split(variables('Surname'),' '))

This part of Flow looks like this

I expect that FullName of project manager is combination of FirstName and Surname so I can use last after I split names by as there I expect space in firstname and lastname (surname).

Whole description of Search for Users is available via this link - https://docs.microsoft.com/en-us/connectors/office365users/

P.

[Anonymous]

Hi @vecerpa

i tried with your solution but it was disaster for me,
flow is sending email to every person in company with similar surname.
i just receive my manager email to stop these mails....
please try to understand my question....how can i send a mail to PM (name in excel file) without email address.
if i will use sharepoint list for this....can i create this type of flow,but the issue is no body wants to create item manually in sharepoint list.if yes please share some idea.
or in flow,its only for one excel file or one list or how can i use lookup column in sharepoint list to manage email ID one time in another list.

Thanks in advance

[vecerpa]

Hi @Anonymous,

Yes I understand you.

First of all, there is option to test your Flow, before you will make it available in production. And this is highly adviced as the impact to end users can be huge.

I don't know your enviroment so the solution that I provide should be updated or changed on your sight as you play with it and test it.

So if you have same surname for many persons there should some naming policy that is inside your company to create for example prefix for your UPN (user principal name) that is in format prefix@yourdomain and that prefix is somehow created and that UPN is unique for each person in your company. That can be used to get email of such user.

There can be also some information about role of such person like for example JobTitle that also delivered from Search User function as return body. So there can be information like Project Manager or something like that. So you can use it as another condition for email sending.

There is always need to find out step by step what leads to your goal. No copy paste provided solution as each enviroment is different...

P.

[Anonymous]

Hi @vecerpa

Thanks for your reply,
yes, agree for step by step...
but for the surname i used your idea....
and in current environment i am......not able to check the naming convention of each user in our team....

do you think this flow is possible or not ? in any simple way ?if yes please guide me exact solution.....
i tried with sharepoint list also.....with below some steps
1. create new list in sharepoint
2. copy excel items in sharepoint by using flow.
3. but pm name is text you know that...in list and excel file both....
4. How can i use pm email id in flow from second list which is also present in sharepoint in same site (contains all team member email id).
5. if possible please reply me.
6. can we fetch data from two sharepoint list in Flow and then send the mail to respective person.(please reply me but not in yes or NO....)
May be i am disturbing you....but this is urgent and pending task for me

Regards
Vikas

[vecerpa]

Hi @Anonymous,

Yes it can work in way you describe. You can bound a flow to trigger when for example new item in list of projects is being created, such flow can go throught list of PM names and emails and fill column in your list of projects by email of such PM.

There is action to load all items in SharePoint list and then you can loop through it and search for string (FullName). It will take some time to find out how match correctly FullName of PM to list in SharePoint as you mention, there can be some mutations as the name is Filled manually into excel sheet.... I can just advice you to split FirstName and SurName into separate columns and look through both separately. You should have better match.

Another flow will be triggered on some scheduled basis and create email notifications based on filled dates and use email that is already filled by first flow...

P.

[Anonymous]

thanks @vecerpa for prompt reply....
you are nice.....but can you please tell me how can i load two sharepoint list columns (one is original one and second for email id of PM)....can you please share one or two steps in flows (screenshot),i tried but not able to match PM name with email ID....
i know its not difficult for you.....:) 

Regards
Vikas Kaushik

[vecerpa]

Hi @Anonymous,

As inspiration you can do it like this:

SharePoint list of PMs

ProjectList

Flow

P.

[Anonymous]

Hi @vecerpa

Thanks for your support
Going step by step,everything is OK
But in the last step showing error "The execution of template action 'Update_item' skipped: the branching condition for this action is not satisfied." while update list item
Regards
Vikas Kaushik

[Anonymous]

hi again @vecerpa

i tried hard to solve the issue 
atlast i merge condition part in one by using formula "@and(equals(variables('PMFirstName'), items('Apply_to_each')?['FirstName']),equals(variables('PMSurName'), items('Apply_to_each')?['LastName']))"

and skip the yes and No part after condition....but when flow updating the poject list,it is picking any email ID against different PM name.....means if in project list pm name is janice arnaud......its updating the email column by herry.kane@.....com.....
why this happening....i am frustated.....please help...

[vecerpa]

Hi @Anonymous,

You have to change name of variables like 'PMFirstName' to name of variables you use in your Flow. And same is for name of columns from SharePoint list like 'FirstName' and 'LastName'  in your expression for condition. As I used those names in my SharePoint list I used for "table" of PM names and their email addresses.

There is need to adapt it to your Flow (your enviroment).

P.

[Anonymous]

Hi @vecerpa

Thanks for your response,i know you are also frustating from this....but i dont want to leave this topic now when i am very close to solve this issue.....with your help and support

please check the below steps in my environment.....its in very simple way....nothing complicated ....but project manager email picking wrong email id....

1. My project list: -



2. Email list (name is Email for schedule cost) where i am picking Email Address.



3. Flow.....


Flow status Running successfully no error.....but result is not expected....because ProjectManagerEmail is not correctly picking....

Please reply....

Regards
Vikas Kaushik

[vecerpa]

Hi @Anonymous,

There have to be a space in between ' ' as the space is the separator between first name and surname like "Pavel Vecer". So there is space inbtween and that is the thing the split() function should be looking for. Otherwise it is not able to split it. You can see result of your Flow run when you for example test and there is available output of each action inside Flow, also the input. So you can debug it and see step by step what is happening.

First(split(triggerBody()?['ProjectManager'],' '))

P.

[Anonymous]

Hi @vecerpa

thanks for your kind support till now....Thanks for your idea.now i used only one condition with first name only.....in sharepoint list no duplicate name for our team only....
and its works fine when update any item....
can i do smilar like this for another field HOD name in same project list (head of the department)
i need to fetch two email address one is for PM and another is for HOD.
if i save HOD first name and last name with email address in same email list ?
should I enter new condition,variable or merge both for PM and HOD.

project list :- 
.........PM    PMemail   HODname   HODemail........

and email list.....

firstname     lastname    Emailadrress

If yes ,Please reply and thanks for your support....

Regards
Vikas Kaushik