September 16, 2020

Outlook VBA: How to get Appointment Organizer or Email Sender Email

I have struggled to find out this one: How to get a Microsoft Outlook Appointment Organizer in VBA.

I needed this for this nice Outlook to Teams Chat Macro.
I share here my learning and final solution.
 

Trial and error

Contrary to a MailItem Type there is no SenderEmailAddress Property
(in fact this property isn't also the email address but the same as AppointmentItem..GetOrganizer.Address see below)
Although there is an Organizer Property https://docs.microsoft.com/en-us/office/vba/api/outlook.appointmentitem.organizer, you can not get the email directly from it.

I've found then the GetOrganizer method and thought GetOrganizer.Address will do it but it returns something like: 
oItem.GetOrganizer.Address /o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=c035bc5647d64d89aecbc6d3ddb5580b-Name

So not the direct email. 

I have found also that doing a loop of the Appointment Recipients and looking for Recipient.Type = OlMeetingRecipientType.olOrganizer does not work because unexpectedly the Organizer has the type OlMeetingRecipientType.olRequired (this looks like an Outlook VBA bug to me I will try to report if I only knew how to).

Final solution


and came to this final solution:

Function GetFromEmail(oItem As Object) As String
    Dim oAddressEntry As Outlook.AddressEntry
    
    If TypeOf oItem Is Outlook.MailItem Then
        Set oAddressEntry = oItem.Sender
        
    ElseIf (TypeOf oItem Is Outlook.AppointmentItem) Then
        Set oAddressEntry = ApptItem.GetOrganizer
    End If
    
    
    If oAddressEntry.Type = "SMTP" Then
        GetFromEmail = oAddressEntry.Address
    ElseIf oAddressEntry.Type = "EX" Then
        GetFromEmail = oAddressEntry.GetExchangeUser.PrimarySmtpAddress
    End If
End Function

This is a bit more simple than the first answer.
This is a generic solution both for a MailItem (Sender) or an AppointmentItem (Organizer)

This function is available in my Github/outlook-vba repo under the Utils module.

See also

vba - How to get email address from /o=ExchangeLabs/ou=Exchange Administrative Group...? - Stack Overflow

Outlook Trap: Get Real Email of Meeting Organizer (VBA) | Thierry Dalon's Blog

No comments:

Post a Comment