We help IT Professionals succeed at work.

Retrieve sender's email address from Outlook's mail item in VB/VBA

nickwoolley
nickwoolley asked
on
How can I retrieve the sender's email address from Outlook's mail item in VB/VBA code, all I can find is the sendersname property, and I don't want to check that for an entry in all address books?
Comment
Watch Question

Commented:
what you have to do is create a reply object, then interrogate the first item in the addresses collection, then you can delete the reply object without sending it.

I will post similar code in a few minutes when I find it.

Commented:
Dim oReply as Outlook.MailItem
Dim strFrom as String

oReply=msgOutlook.Reply
strFrom =oReply.Recipients(0).Address
set oReply = Nothing


Commented:
Please see http://www.slipstick.com/dev/code/getsenderaddy.htm for a complete run down on getting the Sender's email address.

Author

Commented:
cquinn: I had to use oReply.Recipients.Item(1).Name

Neo_mvps: That's an interesting site for CDO, but can you set the CDO session to have events so that code can be triggered when new mail arrives or is sent as with Outlook?

Commented:
Nope.  In this case you use both Outlook and CDO to get the job done. (Outlook for the events and CDO to get at the stuff the Outlook doesn't expose.)

Author

Commented:
When I said "I had to use oReply.Recipients.Item(1).Name" I found later that oReply.Recipients.Item(1).Address gives me email addresses for external emails but only the Outlook Name for internal emails. Not tested CDO yet to see what that gives me.

Neo_mvps: The oher approach I am looking at is to use Exchange Event Service to trigger a script which starts a VB app which then uses CDO to interrogate the email properties.

Author

Commented:
Both of these options work external emails but not for internal emails. The address property gives me the email address for incoming external emails but for internal emails it gives a string eg '/o=Timeframe Software Limited/ou=TIMEFRAME/cn=Recipients/cn=GrahamF' which I believe is the Obj-Dist-Name or Distinguished Name of the mailbox.

Commented:
If you need to get the SMTP address for internal emails, then you have to do something alone the lines of...

* Get the Sender of a message
* Check the address to see if it is SMTP or X-400.
* If X400, get the addressentry (exposed by the recipient object)
* Use the addressentry.fields method to get the item &H39FE001E



As for the Exchange server event script... while the code you write is VBScript, you do get a intrinsic CDO Session via the EventDetails.Session property.

Author

Commented:
Neo_mvps: Never heard of X-400, the addresses I'm dealing with are SMTP. Have you enough info on getting Exchange email addresses (not just address book entries) for me to ask a separate question?

Commented:
The string you get back (/o=Timeframe
Software Limited/ou=TIMEFRAME/cn=Recipients/cn=GrahamF) is an x400 address - Exchange uses this for internal mails, with the SMTP only being used for external mail - so you will need to use neo_mvps' scheme to get it from the addressentry object

Author

Commented:
Ah ic so for internal X-400 adddresses I need to:
1.get the addressentry (exposed by the recipient object)
2.Use the addressentry.fields method to get the item &H39FE001E

You've both offered very helpful advice thank you, so I propose to give 50pts each.

Is there more info available on the addressentry.fields method?

Commented:
From CDO.hlp:

The Fields collection object contains one or more Field objects.

At a Glance

Specified in type library:       CDO.DLL
First available in:       CDO Library version 1.0.a
Parent objects:       AddressEntry
AddressEntryFilter
AddressList
AppointmentItem
Attachment
Folder
InfoStore
MeetingItem
Message
MessageFilter
Child objects:       Field
Default property:       Item
A Fields collection is considered a small collection, which means that it supports count and index values that let you access an individual Field object through the Item property. The Fields collection supports the Microsoft? Visual Basic? For Each statement. For more information on collections, see Object Collections.

Properties

 
Name       Available since version       
Type       
Access
Application       1.0.a       String      Read-only
Class       1.0.a       Long      Read-only
Count       1.0.a       Long      Read-only
Item       1.0.a       Field object      Read-only
Parent       1.0.a       AddressEntry object, AddressEntryFilter object, AddressList object, AppointmentItem object, Attachment object, Folder object, InfoStore object, MeetingItem object, Message object, or MessageFilter object       Read-only
Session       1.0.a       Session object       Read-only
Methods

 
Name       Available since version       
Parameters
Add       1.0.a       name as String,
Class as Long,
value as Variant,
(optional) PropsetID as String,
PropTag as Long
Delete       1.0.a       (none)
SetNamespace       1.0.a       PropsetID as String
Remarks

Field objects give you the ability to access MAPI properties on the parent object of the Fields collection. These include the predefined underlying MAPI properties and your own custom user-defined properties.
MAPI defines a set of properties with identifiers less than the value &H8000. These are known as unnamed properties because they are accessed using the MAPI property tag rather than a name. You can access these MAPI-defined properties using the Fields collection. All MAPI properties are accessible except those of types PT_OBJECT and PT_CLSID.

Data types are preserved between MAPI properties and CDO fields, with the exception of MAPI properties of type PT_BINARY. These are converted from counted binary in MAPI to character string representation in CDO, where the characters in the string represent the hexadecimal digits of the MAPI property value. The string is converted back into counted binary when you write to the field.
You can also extend the properties available through MAPI by defining your own properties. These user-defined properties, defined using a name and automatically assigned an identifier greater than &H8000 by CDO, are known as named properties. (C++ programmers can access the property name in the MAPI structure MAPINAMEID and convert it to the property tag value.)

All named properties are defined as part of a property set, which corresponds in the context of CDO to a name space.
A property set is defined by a GUID, or globally unique identifier. CDO represents this GUID as a string of hexadecimal characters. Such identifiers are usually referenced using a constant that starts with the characters PS_, such as PS_PUBLIC_STRINGS, the default property set for all properties created using the CDO Library.

You can also choose to organize your custom properties within their own name space by defining your own property set. The Add and Item properties and the SetNamespace method let you specify the property set identifier to be used for named property access.
When creating your own property set, you should be aware that MAPI reserves several property set identifiers for specific purposes. The following table lists reserved property sets:

Reserved property set       Description
PS_MAPI       Allows providers to supply names for the unnamed properties (properties with identifiers less than &H8000).
PS_PUBLIC_STRINGS       Default property set for custom properties added using CDO.
PS_ROUTING_ADDRTYPE       E-mail address types that are translated between messaging domains.
PS_ROUTING_DISPLAY_NAME       Display name properties that are translated  between messaging domains.
PS_ROUTING_EMAIL_ADDRESSES       E-mail addresses that are translated between messaging domains.
PS_ROUTING_ENTRYID       Long-term entry identifiers that are translated between messaging domains.
PS_ROUTING_SEARCH_KEY       Search keys that are translated between messaging domains.
To create your own GUID that identifies your property set, you can either use the Win32? command-line utility UUIDGEN or you can call the OLE function CoCreateGuid to supply one for you, as demonstrated in the following code fragment:

' declarations required for the call to CoCreateGuid

Type GUID

    Guid1 As Long

    Guid2 As Long

    Guid3 As Long

    Guid4 As Long

End Type

Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long

Global Const S_OK = 0

Dim strPropID As String

Dim lResult As Long

Dim lGuid As GUID

 

' call CoCreateGuid, then convert the result to a hex string

    lResult = CoCreateGuid(lGuid)

    If lResult = S_OK Then

        strPropID =  Hex$(lGuid.Guid1) & Hex$(lGuid.Guid2)

        strPropID = strPropID & Hex$(lGuid.Guid3)

        strPropID = strPropID & Hex$(lGuid.Guid4)

    Else

        ' ... handle error ...

    End If

 

For more information on named properties and property sets, see "Named Properties" in the MAPI Programmer's Reference. For more information on UUIDGEN and CoCreateGuid, see "COM and ActiveX Object Services" in the Microsoft Platform SDK documentation.
MAPI stores all custom properties that represent date and time information using Greenwich Mean Time (GMT). CDO converts these properties so that the values appear to the user in local time.

Example

To uniquely identify a Field object in the Fields collection, use the Field object's Name or Index property, or the MAPI property tag:

Set objNamedField = objFolder.Fields.Item("BalanceDue")

Set objNamedField2 = objMessage.Fields.Item("Keyword")

Set objIndexedField = objMessage.Fields.Item(3)

propTag = &H0E180003 ' VB4.0: propTag = CdoPR_MESSAGE_DOWNLOAD_TIME

Set objMAPIField = objMessage.Fields.Item(propTag)

 

Author

Commented:
cquinn: How does this help me with my problem of obtaining the SMTP address from the X-400 address?
Commented:
Look at the last two lines and adjust accordingly -

Dim objMAPIField as MAPI.Field
Dim sSMTP as string
propTag = &H39FE001E


sSMTP = oReply.Recipients.Item(1).Name
if instr(sSMTP,"/o=") > 0 then  'x400 address
  Set objMAPIField = objAddressEntry.Fields.Item(propTag)
  '(objAddressEntry is the AddressEntry object you have obtained from the message)

  sSMTP = objMAPIField.Value  'Should now hold the SMPT address
end if


Author

Commented:
Not sure I'm clear on the AddressEntry object, do I have to interogate the Exchange Address book or can I get a ref from the message object?

Commented:
I think http://www.cdolive.com/cdo5.htm#EMailAddressOfSender will set you on the path of enlightenment.

Commented:
From neo's link

Set objAddressEntry = objMessage.Sender

Author

Commented:
Excellent this works well!

Neo_mvps: There is another related question of mine which cquinn is having a look at which you may want to see and it's at http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20189978 
Could we get some feedback here? Want to award points, nick? Just accept that expert's comment as answer.

We're cleaning up this topic area and need all the help we can get.

Thanks
amp
Community Support Moderator

Author

Commented:
Sorry for the delay guys!

50pts also for Neo_mvps at http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20254848 as promised

Thanks