Get item.recipient with Outlook VBA

Question

I need to get the recipient email address out of an item on Outlook 2010.

My code is as follow:

sSQL = "SELECT id from dbo.database where email_address LIKE '" & Item.RecipientEmailAddress & "'

item.recipientEmailAddress is not valid, but I need something like that.

I know that you have a code call Item.SenderEmailAddress but in this case I need the recipient's email address.

I've seen some other threads on that subject but I did not manage to make any of them work.

Solution 1

For a "quick" way of doing it, you can concatenate the Item.To together with the Item.CC and Item.BCC properties, however, this may not be what you're looking for as sometimes these properties store the display names instead of the SMTP email addresses.

Another way is to use the Item.Recipients collection which contains a Recipient object, which contains an Address property, for every recipient (TO, CC, and BCC).

You could loop through each recipient and concatenate them together. Something like this:

Dim recip As Recipient
Dim allRecips As String

For Each recip In item.Recipients
    If (Len(allRecips) > 0) Then allRecips = allRecips & "; "
    allRecips = allRecips & recip.Address
Next

Solution 2

The Recipients property of the MailItem class returns a Recipients collection that represents all the recipients for the Outlook item.

The Type property of the Recipient class returns or sets an integer representing the type of recipient. For the MailItem the value can be one of the following OlMailRecipientType constants: olBCC, olCC, olOriginator, or olTo.

Also the Recipient class provides the following properties:

• Name - a string value that represents the display name for the recipient.
• Address - a string representing the e-mail address of the Recipient.
• AddressEntry - the AddressEntry object corresponding to the recipient.

Comments

[Ange Loron]

item.to gives me the name of the recipient but in this case I would need the email address

[Ange Loron]

Tks for the edit, that looks good. I am giving it a shot right now. Do you think I could pick only the recipient To and not the CCs, BCCs, etc. As I basically one the email TO address and scan it through a database (if there are more than one address, it won't scan through the database.

[rory.ap]

One of the properties of the Recipient class is Type, which you can use to filter it. As you're looping, you can use If (item.Type=olTo) Then...

[Ange Loron]

Smart, i'll do that. Here is the next dumb question: I put my code: "sSQL = "SELECT id from dbo.dat... " (as written on the original post) after your code and it sets the variable to Unknown. Should I write the code before the "Next"?

[rory.ap]

It shouldn't be as written in your original post. It should use the new string you concatenated together, e.g. allRecips.

[Eugene Astafiev]

Well, copying other answers is not a good idea for earning points in the forum.

[rory.ap]

@EugeneAstafiev -- First off, Stack Overflow is not a forum. Also, it's not about earning points, it's about providing good, complete answers. Just because there is some overlap in our answers doesn't mean I copied yours; I simply was not finished with my answer when you posted yours. I hadn't even looked at your answer until afterwards, to be honest with you.

[Eugene Astafiev]

I didn't notice "it's about providing good, complete answers" in your post and decided to answer to this post. But what you did? Just read other answers and edited your post. If you didn't complete the post, please don't click the SUBMIT button.

[rory.ap]

@EugeneAstafiev -- Sorry, your last comment didn't make any sense.

[Eugene Astafiev]

I didn't see any sense in your reply as well. A weak strategy is to copy other answers and then tell everybody that you didn't notice anything. Moreover, downvote original answers.