Need help with getting my own outlook mail address to excel using macro

[karthik R]

Guys...I want my own outlook mail address to be extracted and shown in excel cell . This is for my ofc use. kindly help e this.. simple macro or fuctions to show my own outlook mail address in excel cell.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

This code gets three strings. At least one of them should be what you are looking for:

' Requires Tools-->References-->Microsoft Outlook 15.0 Object Library
Sub getEmailAddress()

    Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
    
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olRec As Outlook.Recipient
    Dim olPA As Outlook.PropertyAccessor

    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olRec = olNS.CurrentUser
    Set olPA = olRec.PropertyAccessor
    
    Range("A1") = olNS.CurrentUser
    Range("A2") = olNS.Accounts(1)
    Range("A3") = olPA.GetProperty(PR_SMTP_ADDRESS)
    
End Sub

Note that the way this code is written you will need to set a reference to the Outlook Object Library. Mine is v15.
In the VB Editor select: Tools-->References-->Microsoft Outlook 15.0 Object Library

 

[karthik R]

Thnak you... please help me to get the below address u mentioned above..... where to get that??

Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"

 

[RickXL]

Hi,

You can look up PropertyAccessor at the Microsoft sites but the best list I have found is on Diane Poremsky's site slipstick.com: Read MAPI properties not exposed in Outlook's Object Model

Regards,