Display the Recipient Email Address in the Sent Items folder

Last reviewed on February 20, 2014

I'm frequently asked how to display the recipient's email address in the Sent Items folder. The answer is this macro, which adds a custom field containing the addresses the message was sent to. The custom address field is a text field and you can sort or group by it.

Recipient addresses

Note that when a message is sent to multiple people, all addresses will be entered in the field as one long string, as seen in this example.

/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=43dfb970e3b54f85942d1348b58581e6-drcp;billy@domain.net;

Exchange server addresses are the x.500 address (as seen in the example above), not the SMTP address, however, you can use the Right() function to keep just the alias.

Public Sub GetRecipientAddress()
' http://slipstick.me/9vjgj
    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim obj, objMail As Object
    Dim objProp As Outlook.UserProperty
    Dim strDomain
    Dim Recipients As Outlook.Recipients
    Dim recip As String
    Dim i
    Dim prompt As String
     
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
 
    On Error Resume Next
 
For Each obj In Selection
         Set objMail = obj
         strDomain = ""
Set Recipients = objMail.Recipients
  For i = Recipients.count To 1 Step -1
     recip$ = Recipients.item(i).Address
     
   ' To use only the alias from the x.500 address
   ' If InStr(1, LCase(recip), "/ou=") Then recip = Right(recip, Len(recip) - InStr(1, LCase(recip), "recipients") - 13)

' Use semicolon separator if there is more than 1 address
     If i = 1 Then
         strDomain = strDomain & recip
     Else
         strDomain = strDomain & recip & "; "
     End If

  Next i
     Debug.Print strDomain
          
         Set objProp = objMail.UserProperties.Add("Recipient Email", olText, True)
         objProp.Value = strDomain
         objMail.Save
  
        Err.Clear
    Next
  
    Set currentExplorer = Nothing
    Set obj = Nothing
    Set Selection = Nothing
End Sub

Add the field automatically

To add the recipient address field automatically, you need to use an ItemAdd macro. These macros need to be in ThisOutlookSession. To test, click in the Application_Startup macro and click Run then send a message.


Dim WithEvents olSent As Items
  
Private Sub Application_Startup()
   Dim NS As Outlook.NameSpace
   Set NS = Application.GetNamespace("MAPI")
   Set olSent = NS.GetDefaultFolder(olFolderSentMail).Items
   Set NS = Nothing
End Sub
  
Private Sub olSent_ItemAdd(ByVal Item As Object)
    ' From http://slipstick.me/1
    Dim objProp As Outlook.UserProperty
    Dim strDomain As String
    Dim Recipients As Outlook.Recipients
    Dim recip As String 'Outlook.Recipient
    Dim i
                   
strDomain = ""
Set Recipients = Item.Recipients
  For i = Recipients.count To 1 Step -1
     recip$ = Recipients.Item(i).Address
     
    'If InStr(1, LCase(recip), "/ou=") Then recip = Right(recip, Len(recip) - InStr(1, LCase(recip), "recipients") - 13)
     If i = 1 Then
         strDomain = strDomain & recip
     Else
         strDomain = strDomain & recip & "; "
     End If
  Next i
      
  Set objProp = Item.UserProperties.Add("Recipient Email", olText, True)
    objProp.Value = strDomain
    Item.Save
  
    Err.Clear
         
    Set objProp = Nothing
    Set Recipients = Nothing
         
 End Sub

How to use this macro

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

  1. Right click on Project1 and choose Insert > Module
  2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

26 responses to “Display the Recipient Email Address in the Sent Items folder”

  1. Scott

    This is great, thanks so much. It looks like I have to run the macro manually for all emails that come in. Is there a way to have this be automatic for all incoming emails? Also is there a way to remove the ; that is after the email? I understand if there is more than one recipient but I am getting a ; after all of them. Thanks again.

  2. Scott

    Thank you. I removed the old macro and added the new one, now the ; is not listed. However I am unclear how to add the automatic macro. Currently I have the following tree in Visual Basic:

    Project 1
    - Microsoft Outlook Objects
    - ThisOutlookSession
    - Modules
    - Module1

    I copied the GetRecipientAddress Macro in Module1 and the same text also appears in ThisOutlookSession. Do I paste in the automatic macro text below this under ThisOutlookSession? I do not see an option for adding an ItemAdd macro.

    Lastly, will this be saved so that it is automatically running each time I start up my computer and open Outlook?

    Thanks again.

  3. Scott

    Sorry I cannot figure out how to delete the Macro. I am on Outlook 2010. Can you please advise?

  4. Scott

    I also noticed another problem. The macro puts anyone on the CC line into the Recipient Email field. Is there anyone to exclude this and only include only the email addresses in the To field? Thanks.

  5. Scott

    I am sorry, I am just not that good with this. I tried to make the modifications but it did not work. Can you please assist. I would like to go back and manually change all the old emails and then do the automatic. Thanks again.

  6. Scott

    Can you please post what the exact text should be for both the manual and automatic? I cannot figure out this part:

    and change the line with Next to
    Nexti:
    Next i

    Everything I try reports back errors.

    Thanks.

    Scott

  7. Scott

    I figured it out. The Next i line has to be changed, not the Next line. Code here works.

    Public Sub GetRecipientOnlyAddress()
    ' http://slipstick.me/9vjgj
    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim obj, objMail As Object
    Dim objProp As Outlook.UserProperty
    Dim strDomain
    Dim Recipients As Outlook.Recipients
    Dim recip As String
    Dim i
    Dim prompt As String

    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection

    On Error Resume Next

    For Each obj In Selection
    Set objMail = obj
    strDomain = ""
    Set Recipients = objMail.Recipients
    For i = Recipients.Count To 1 Step -1
    If Recipients.Item(i).Type = olTo Then
    recip$ = Recipients.Item(i).Address
    Else
    GoTo Nexti
    End If

    ' To use only the alias from the x.500 address
    ' If InStr(1, LCase(recip), "/ou=") Then recip = Right(recip, Len(recip) - InStr(1, LCase(recip), "recipients") - 13)

    ' Use semicolon separator if there is more than 1 address
    If i = 1 Then
    strDomain = strDomain & recip
    Else
    strDomain = strDomain & recip & "; "
    End If

    Nexti:
    Next i
    Debug.Print strDomain

    Set objProp = objMail.UserProperties.Add("Recipient Email", olText, True)
    objProp.Value = strDomain
    objMail.Save

    Err.Clear

    Next

    Set currentExplorer = Nothing
    Set obj = Nothing
    Set Selection = Nothing
    End Sub

  8. Scott

    Now I am trying to get the automatic macro to work. I copied the code into a class module in VBA and then went to ThisOutlookSession, and in the Application_Setup section, I hit the play button to run the Macro. It does not do anything. I am pretty sure I deleted the other macros as nothing shows up under Macros in Outlook except the auto one now. Please advise. It would be great to have this automatic.

  9. Scott

    When I try to paste into ThisOutlookSession, it automatically moves me to Module1. When I paste into there and go to Application_Startup and hit the run/play button, I get:

    Complie Error: Only valid in object module

    Please advise.

    Thanks.

    Scott

  10. Kristian

    Is there a quick way to modify this to show only the domain of the first recipient?

  11. Kristian

    Thanks for your quick reply Diane, however I'm unable to get it to function.

    The macro executes without any error, but no data is populated in the recipient email field. I'm working in a exported PST opened in Outlook 2010 if that makes any difference.

    I don't know if there might be a conflict, but I'm trying to use this in conjunction with your 'Sort message by sender domain', which works perfectly.

    This is all very new to me, so I greatly appriciate your help.

    My modified code:

    Public Sub GetRecipientAddress()
    ' http://slipstick.me/9vjgj
    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim obj, objMail As Object
    Dim objProp As Outlook.UserProperty
    Dim strDomain
    Dim Recipients As Outlook.Recipients
    Dim recip As String
    Dim prompt As String

    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection

    On Error Resume Next

    For Each obj In Selection
    Set objMail = obj
    strDomain = Right(recip, Len(recip) - InStr(1, recip, "@"))
    Set Recipients = objMail.Recipients
    recip$ = Recipients.Item(1).Address

    Debug.Print strDomain

    Set objProp = objMail.UserProperties.Add("Recipient Email", olText, True)
    objProp.Value = strDomain
    objMail.Save

    Err.Clear
    Next

    Set currentExplorer = Nothing
    Set obj = Nothing
    Set Selection = Nothing
    End Sub

    Thanks, Kristian

  12. Kristian

    Please disregard my previous reply. It sort of works now, but only when I select multiple emails and it doesn't behave as expected.

    It takes the domain from the preceding email in the selection.

    Example:
    Selection 1 - Email: lorem@ipsum.com Domain:
    Selection 2 - Email: ipsum@lorem.com Domain: ipsum.com
    Selection 3 - Email something@else.com Domain: lorem.com

    -Kristian

  13. Kristian

    Works like a charm.

    Thank you very much Diane!

  14. williamlambton

    I have put an item at the bottom here:

    http://www.outlookforums.com/threads/86266-customised-to-cc-bcc-and-have-replies-sent-to-columns-in-message-lists/.

    That's my own thread dated 10th July 2011 (eleven), though I first started hunting for this facility in 2010 (I think). Very good to see the question at last addressed!

Leave a Reply

If the Post Coment button disappears, press your Tab key.