How to retrieve of all email addresses that might belong to a group or distribution list that you see in outlook?

[tddttran]

I've looked everywhere and didn't find anything on this.

In outlook, then you compose a message you can click To: field and browse through emails/groups/distribution list.

How to retrieve all actual email addresses of a possible group or distribution list or single email address.

I need to do this because 

in Start and Wait for approval process just accepts email addresses, and when you enter for example a distribution list in there.

it goes to the list but but the approval buttons don't work because it's not your own email.

I am so confused right now 

[RezaDorrani]

Hi @tddttran 

Check https://youtu.be/WT08hJ1zDyw 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

[tddttran]

Thanks so much for trying to help but this is for Members email addresses of a SharePoint Group.

I want email addresses of any outlook's group or distribution list.

[RezaDorrani]

@tddttran 

Video description has link to download the code for email enabled groups and office 365 groups.

[tddttran]

Thanks I'll look some more and see if it helps with Outlook's Distribution Lists....will accept yours as solution if it helps for now I just upvoted your answer.

[RezaDorrani]

Outlook distribution lists is not covered. Not sure if that is possible with existing flow connectors. Might have to go down the custom connector way

[tddttran]

What is this customer connector way you speak of? please give me more clues. As I am new the whole sharepoint and all these apps. I would love any more details you can give me. I think my requirement now is if they end any email address, distribution list of multiple members I need to get the expanded recipients list to send approval requests to.

[RezaDorrani]

I meant custom connector 🙂

You can create on to call API endpoints.

I do not have any clue on how to get distribution list member email addresses.

[VictorIvanidze]

Hi @tddttran,

you can use the following GRAPH request

https://graph.microsoft.com/v1.0/groups/{groupId}/transitiveMembers 

See https://docs.microsoft.com/en-us/graph/api/group-list-transitivemembers?view=graph-rest-1.0&tabs=htt...

[tddttran]

Can you point me to a tutorial of how to call this graphAPI, one with a relative path {groupId} I found a tutorial on one that does with a just static request without a variable such as {groupId}.
Thanks

[tddttran]

So I started going down this path.

started hunting for groupId. Found a Graph Request to get groupId by display name. tried that but then it looks like I need Authentication like the bearer and to generate that I need to tenant-Id and we currently don't use Azure so how to get tentant-Id.
Is there any other ways of getting this expanded email distribution list other than Graph-API?
I was hoping it was built in as an action already 😄

[VictorIvanidze]

>Is there any other ways of getting this expanded email distribution list other than Graph-API?

No.

[tddttran · Accepted solution]

I worked it as a work-around since I don't have access to Azure or Powershell

I wrote found some VBA code and scripted it to pull the whole list of groups and distribution list from "Global Address List" using Excel VBA Macro. Then dump that out to .csv.

and just have to upload that .csv since our company doesn't want to use Premium automated trigger to desktop flow.

[tddttran]

Just in case anyone else has the same problem and can use this same work-around, here's the VBA code (Just insert into a module as a macro). (I found 2 different examples that did what I needed and merged them so there's a lot of commented stuff just for my own references).

Option Explicit

Sub PrintDistListDetails()
    
    Dim olApplication As Object
    'Dim olNamespace As Object
    'Dim olContactFolder As Object
    'Dim olDistListItem As Object
    'Dim destWorksheet As Worksheet
    'Dim distListName As String
    'Dim memberCount As Long
    'Dim memberIndex As Long
    'Dim rowIndex As Long
    
    'Dim myol As Object
    Dim myns As Object
    Dim objAL As Object
    Dim objAE As Object
    Dim objAE2 As Object
    Dim olUser As Object
    'Dim GALemail As Object
    
    Dim OutputFileNum As Integer
    Dim PathName As String
    Dim line As String
    Const olFolderContacts As Long = 10
    
    'distListName = "MyDistListName" 'change the name accordingly


    Set olApplication = CreateObject("Outlook.Application")
    'Set olNamespace = olApplication.GetNamespace("MAPI")
    'Set olContactFolder = olNamespace.GetDefaultFolder(olFolderContacts)
    
    Set myns = olApplication.GetNamespace("MAPI")
    'Set olGAL = olNs.AddressLists("Global Address List").AddressEntries
    'For Each objAL In myns.AddressLists("Global Address List").AddressEntries
    Set objAL = myns.AddressLists("Global Address List")
        If (objAL.Name = "Global Address List") Then
            'Open file to write to
            PathName = Application.ActiveWorkbook.Path
            OutputFileNum = FreeFile
            Open PathName & "\group-DL.csv" For Output Lock Write As #OutputFileNum
            Print #OutputFileNum, """Address List""" & "," & """Group DL Name""" & "," & """Name""" & "," & """Email Address"""
            For Each objAE In objAL.AddressEntries
                If Not (objAE.Members Is Nothing) Then ' must be a distribution list
                    For Each objAE2 In objAE.Members
                        Set olUser = objAE2.GetExchangeUser
                        If Not (olUser Is Nothing) Then
                            'sEmail = olUser.JobTitle
                            'ActiveCell.Offset(0, 6).Value = olUser.PrimarySmtpAddress
                            
                            'First we get our line with double quotes while replacing content's possible double quotes with "[QUOTES]"
                            line = """" & Replace(objAL.Name, """", "[QUOTES]") & """,""" & Replace(objAE.Name, """", "[QUOTES]") & """,""" & Replace(objAE2.Name, """", "[QUOTES]") & """,""" & Replace(olUser.PrimarySmtpAddress, """", "[QUOTES]") & """"
                            'Then we replace the "[QUOTES]" with two double quotes so it would put the quote in the content (csv standard)
                            line = Replace(line, "[QUOTES]", """""")
                            Print #OutputFileNum, line
  
                            'If sEmail = specificTitle Then
                            'Set GALemail = olUser.PrimarySmtpAddress
                            'Debug.Print olUser.PrimarySmtpAddress
                            'End If
                            'End If
                            'ActiveCell.Value = objAL.Name
                            'ActiveCell.Offset(0, 1).Value = objAL.Class
                            'ActiveCell.Offset(0, 2).Value = objAE.Name
                            'ActiveCell.Offset(0, 3).Value = objAE.Class
                            'ActiveCell.Offset(0, 4).Value = objAE2.Name
                            'ActiveCell.Offset(0, 5).Value = objAE2.Class
                            'ActiveCell.Offset(1, 0).Activate
                            
                        End If
                    Next
                End If
            Next
        End If
    'Next
    Close OutputFileNum
End Sub