Find All Incoming E-Mail Enabled Lists and Libraries in SharePoint

Requirement: During Migration, Needed a List of location where Incoming E-Mail is enabled. So Here is the script which gives that!

PowerShell Script to find all incoming Email Enabled Lists and Libraries:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Web Application URL  
$WebAppURL="http://intranet.crescent.com/"
$Report="c:\IncomingEmails.csv"

# Enumerate all lists in the web application
Get-SPWebApplication $WebAppURL | Get-SPSite -limit ALL | Get-SPWeb -Limit ALL | ForEach-Object {
#Get the Current Site
$Site = $_
#Get all Lists with Incoming Email enabled
 $Site.Lists | Where-Object { ($_.CanReceiveEmail) -and ($_.EmailAlias) }| ForEach-Object {
      New-Object -TypeName PSObject -Property @{
              ListName = $_.Title
              SiteUrl = $Site.Url
              Email = $_.EmailAlias }
   }
} | Export-CSV $Report -NoTypeInformation

Write-host "Incoming Emails Report Generated!"

Get All Incoming Email Lists in SharePoint 2007:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
#Get the web app
$SPWebApp = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup(http://sharepoint.com)
 
#create a CSV file
"E-Mail,List,Site" > "EMail-Enabled.txt" #Write the Headers in to a text file
 
foreach ($SPsite in $SPwebApp.Sites)  # get the collection of site collections
{ 
    foreach($SPweb in $SPsite.AllWebs)  # get the collection of sub sites
        {
            foreach ($SPList in $SPweb.Lists)
                {
                    if ( ($splist.CanReceiveEmail) -and ($SPlist.EmailAlias) )
                        {
                            # WRITE-HOST "E-Mail -" $SPList.EmailAlias "is configured for the list "$SPlist.Title "in "$SPweb.Url
                            $SPList.EmailAlias + "," + $SPlist.Title +"," + $SPweb.Url >> EMail-Enabled.txt  #append the data
                        }
                }
        }
}

Don't have PowerShell? Want to achieve the same using C# code: (console application).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.IO;

namespace GetEnvironmentDetails
{
    class FindIncomingEMailEnabled
    {
        static void Main(string[] args)
        {
            string site;

            try
            {
                if (args.Length == 0)
                {
                    Console.WriteLine("Enter the Web Application URL:");
                    site = Console.ReadLine();
                }
                else
                {
                    site = args[0];
                }

                //objects for the CSV file generation
                StreamWriter SW;
                SW = File.AppendText("c:\\IncomingEmails.csv");

                //Write the CSV Header
                SW.WriteLine("Site Name, List Name, List URL, E-Mail");
                SPSite tmpRoot = new SPSite(site);
                SPSiteCollection tmpRootColl = tmpRoot.WebApplication.Sites;

                //Enumerate through each site collection
                foreach (SPSite tmpSite in tmpRootColl)
                {
                   //Enumerate through each sub-site
                    foreach (SPWeb tmpWeb in tmpSite.AllWebs)
                    {
                        //Enumerate through each List
                        foreach (SPList tmpList in tmpWeb.Lists)
                        {
                            //Check Incoming Email is enabled
                            if(tmpList.CanReceiveEmail & (!string.IsNullOrEmpty(tmpList.EmailAlias)))
                            {
                            //Log the details to a file
                            SW.WriteLine(tmpWeb.Title.Replace(",", " ") + "," + tmpList.Title.Replace(",", " ") + "," + tmpSite.Url + tmpList.DefaultViewUrl + "," + tmpList.EmailAlias.ToString());
                            }
                        }                 
                    }
                }
                                                
                //Dispose of the bjects
                SW.Close();
            }

            catch (Exception ex)
            {
                System.Diagnostics.EventLog.WriteEntry("Get Incoming Emails Detail", ex.Message);
            } 
        }
    }
}

Alternatively, You can write & run the below SQL Query on each content database, However I wont recommend it (Even with No Lock!)

SELECT Webs.FullUrl, AllLists.tp_Title, AllLists.tp_EmailAlias
FROM AllLists With (NOLOCK)
Inner join webs With (NOLOCK) on AllLists.tp_WebID = Webs.Id
where AllLists.tp_EmailAlias is not null