Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.
Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.

SharePoint

Unable to assign this e-mail address to the list, because the address is in use – FIXED

I came across this WSS error message today while working on incoming email updates and was stuck.   The browser clearly showed the list has no inbound email enabled and offered no alternatives on how to enable.   My enable failed, the address is taken, and I have no way to remove.

SQL Studio to the rescue!

addr in use

OK.  First of all I know, I know I know.   TSQL against the databases even in read only mode is frowned upon.   What I am doing here is far beyond supported, not safe, and I am a crazy man.  Again, I know this.   I had no other options and needed a solution.

The config database holds a central table the incoming email references to get the site, web, and list GUID for where to place the message.    I found my old email alias in here despite the fact the web GUI showed it was disabled.  

Disappointed

  I wrote a select statement and copied the output to notepad in case I needed to INSERT this row back.   I then changed “SELECT *” to “DELETE” and killed the one row.   Flipping back to IE and clicking OK, it worked perfectly!   Never had any issues since and this was weeks ago.   Like they say on TV … “don’t try this at home”

Extracting hyperlink destinations from Excel (Document Library item URLs)

I recently needed the destination URL for several hundred items across layers of nested folders in a giant SharePoint Document Library.    Right clicking with “copy link location” just wasn’t going to work here.   The datasheet view, Excel, and Access all came to mind as possible tools.   In then end a quick Excel UDF (User Defined Function) helped get the job done.   This is easily done against SharePoint Document Libraries because the default Title column links to the file item URL.

  1. Choose “Export to Spreadsheet” from your document library
  2. Open the .IQY file when prompted
  3. Press Alt+F11, then Insert > Module
  4. Paste in this code:
  5. Save the workbook.   Note:  You might be prompted to save as XLSM for a macro enabled workbook, change the file type, and save.
  6. Use the =getaddress(cell) function to see the URLs  (screenshots below)

 

 

clip_image002
clip_image004
clip_image006
clip_image008
clip_image010
clip_image012
clip_image014
clip_image016
clip_image018
clip_image020

 

Sources Cited

http://www.ozgrid.com/VBA/HyperlinkAddress.htm

Function GetAddress(HyperlinkCell As Range)

    GetAddress = Replace _

    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

End Function

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲