Outlook VBA - Getting the Recepient Email address from a message 1

[neemi]

I am saving emails as *.msg files and linking them to ms access using filenames etc.

Before doing this I am extracting info from the email such as dates/times/subject etc. Also who the email was to and from using .To and .Sendername and saving this info in a table.

However I need to know the sender and recepient email addresses aswell. I have managed to extract the senders email address but am not sure how I can get the recepients email address?

I am using active inspector to get most of this info.

Does anyone know how I can get the recepients email address from an active email?

Help appreciated.

Cheers.
Neemi

 

[dyarwood]

Have a look at this thread.

thread707-756248

Its saving the body and subject of emails but with a bit of playing it can be made to give you what you want.

 

[neemi]

Thanks for responding but it doesn't help with getting the recepients email address at all. I have used similar code to extract everything I need apart from the email address of who the email was sent to, but using the Inspector.

Thanks for responding though.

Someone must have done this before.. :-(

 

[TonyJollans]

Hi neemi,

This is a bit tricky. A MailItem may be sent to several recipients and can be To, CC, or BCC any of them. Exactly what information do you require and what are your criteria for selecting the appropriate person.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neemi]

All I am trying to do is that all users on a network use a database regarding to cases. If they recieve emails or send emails to a particular case the email is saved using a macro as a msg file. At the same time I am saving certain Email info to a table.

Ie.
Who sent the email
Who it was sent to
subject
etc

In regards to who sent the email and who it was sent to I am trying to save the name as well as the email addresses from the email.

I have got code to work out the email address of who sent the email address but am struggling with the email address of who it was sent to.

Below is how I have done this:

I have put ??????? where I am stuck with getting the email address.

Dim myItem As Inspector
Dim Amt As Attachments
Dim Filename As String
Dim objItem As Object

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector
Set myNameSpace = myOlApp.GetNamespace("MAPI")

If Not TypeName(myItem) = "Nothing" Then
Set objItem = myItem.CurrentItem

'//get Email Parameters
strSubject = objItem.Subject
strFrom = objItem.SenderName
strTo = objItem.To
strFromAddress = GetFromAddress(objItem)
strToAddress = ???????

dtmRecievedDate = Format(objItem.SentOn, "dd/mm/yyyy")
dtmRecievedTime = Format(objItem.SentOn, "hh:mm:nn")
intAttachments = objItem.Attachments.Count

'// Save the email
objItem.SaveAs strPath & strFileName & strType

'// Save the any attachments from the email
Set Atmt = Nothing




Function GetFromAddress(objMsg) As String

'// start CDO session
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, False


'// pass message to CDO
strEntryID = objMsg.EntryID
strStoreID = objMsg.Parent.StoreID
Set objCDOMsg = objSession.GetMessage(strEntryID, strStoreID)

'// get sender address
strAddress = objCDOMsg.Sender.Address
'Debug.Print objCDOMsg.Sender.Address
'Debug.Print objCDOMsg.Sender.Name

GetFromAddress = strAddress

Set objCDOMsg = Nothing
objSession.Logoff
Set objSession = Nothing

End Function

 

[TonyJollans]

Hi Neemi,

I am using Outlook 2003 but I'm pretty sure this is in 2000 as well.

If you only have one recipient then the address should be in ActiveInspector.CurrentItem.Recipients(1).Address

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neemi]

Thanks for your help. You deserve a star for that. So simple when you know how!

Do you know how you can determine if a email is in a state of "Compose" or "Read". ie if the message that is open is one that has been recieved from someone else or one that is going to be sent?

Also if a user is an exchange user it returns the exchange address as apossed to the actual email address. Is there a way around this with out lookups to tables.

Cheers again,
Neemi

 

[TonyJollans]

Hi neemi,

ActiveInspector.CurrentItem.Sent is True or False. It doesn't tell you whether the mail was Sent or Received, but should tell you whether it has been sent or not.

'fraid I don't know about Exchange, and am not on an Exchange Server so can't look.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neemi]

Great stuff...

Just another thing if you don't mind.

Below is a sample of the code I have put together..


Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector
Set objItem = myItem.CurrentItem

Select Case ActiveInspector.CurrentItem.Sent
Case Is = True
strFrom = objItem.SenderName
strTo = objItem.To
strFromAddress = GetFromAddress(objItem)
strToAddress = ActiveInspector.Session.CurrentUser.Address
Case Is = False
strFrom = ActiveInspector.Session.CurrentUser
strTo = objItem.To
strFromAddress = ActiveInspector.Session.CurrentUser.Address
strToAddress = ""
End Select

Is there a better way I can do this. Also do you have any ideas as how I can get the email address of who I am sending the email to if the current item has not been sent yet?

 

[TonyJollans]

Hi neemi,

If you have got a recipient set up - which is not necessarily true in an e-mail being composed (check ActiveInspector.CurrentItem.Recipients.Count) - then, again, ActiveInspector.CurrentItem.Recipients(1).Address should have the recipient's address.

A couple of minor points on your code:
1) As you have set up an object variable, why not use it?
2) Select Case is not really necessary for a simple True/False test - I would just code:

If objItem.Sent = True Then
    [green]' True stuff[/green]
Else
    [green]' False stuff[/green]
End If

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neemi]

Do you know how to get the CC addresses?

 

[TonyJollans]

They're just other recipients. Look at the Recipient.Type to find out whether they are To, cc, or bcc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neemi]

Currently I get the recipients To email address as follows:

For n = 1 To objItem.Recipients.Count
If n > 1 Then
strToAddress = strToAddress & "; "
End If
strToAddress = strToAddress & ExchangeUser(objItem.Recipients.Address, 1)
Next n

I need to have the same for CC recipients.

I know that I should set .Type to OLCC but unsure how to amend what I have and have the To address code as well.

 

[neemi]

Just realised that the last code I placed concatenates the To and the CC addresses which I don't want.

I need to trye and get a string for the To addresses and a seperate on for the CC addresses!

 

[TonyJollans]

In your loop, check objItem.Recipients.Type and add to the appropriate string. Possible values, I think, are olTo, olCC and olBCC - check Help to be sure.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neemi]

Thanks,

For anyone reference below is the code sniplet I created:
Where exchangeUser is a function I created to extract just the user from the address string exchange gives. You can just take that bit out if you use it.

Set myRecipients = objItem.Recipients
            intToCount = 0
            intCCCount = 0
            
            For n = 1 To myRecipients.Count
                Select Case myRecipients(n).Type
                    Case Is = olTo
                        intToCount = intToCount + 1
                        If intToCount > 1 Then
                            strToAddress = strToAddress & "; "
                        End If
                        strToAddress = strToAddress & ExchangeUser(myRecipients(n).Address, 1)
                    Case Is = olCC
                        intCCCount = intCCCount + 1
                        If intCCCount > 1 Then
                            strCCAddress = strCCAddress & "; "
                        End If
                        strCCAddress = strCCAddress & ExchangeUser(myRecipients(n).Address, 1)
                End Select
            Next n