We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

VB Code To Loop Through Sent Items And Extract Recipients Email Addresses

ShinyApples
ShinyApples asked
on
Medium Priority
817 Views
Last Modified: 2012-06-27
Hi,
I need a quick way of scanning a users sent items and extracting all the recipient email addresses (as strings) and saving them to a database. I'm not used to VB app programming and am having real trouble finding a way of extracting the recipient email address(es) as a string from a mailitem object. Basically I can't find the right property of the mailitem object - I have tried the recipient (and recipient.addressitem) property but this doesn't work - Run time error '438', Object doesn't support this property or method. I suspect this is because the property is not of a string type and the variable and the email_addresses variable is. I just can't find the property I need in any of the help files! Or don't know how to convert the property to a string. I'm using the following code (I will add in the code to extract and save to the DB later).

Sub HarvestSent()
 Dim myOlApp As Outlook.Application
 Dim SentItems As Outlook.MAPIFolder
 Dim obj As Outlook.MailItem
 Dim email_addresses As String
 Dim i As Integer
 Set myOlApp = CreateObject("Outlook.Application")
 Set SentItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail)
 For i = 1 To SentItems.Items.Count
  If SentItems.Items(i).Class = olMail Then
   Set obj = SentItems.Items.Item(i)
    email_addresses = obj.Recipient            'this is where it fails
  End If
 Next
End Sub

Please help this is doing my head in!!!

Thanks.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Shiny Apples,

Bear in mind, this will likely trigger the outlook security warning.  Consider using the Redemption object instead to avoid it: http://www.dimastr.com/redemption/home.htm

But you need to get the .address of the .recipient

Sub HarvestSent()
 Dim myOlApp As Outlook.Application
 Dim SentItems As Outlook.MAPIFolder
 Dim email_addresses As String
 Dim i As Integer, j As Integer
 Set myOlApp = CreateObject("Outlook.Application")
 Set SentItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail)
 For i = 1 To SentItems.Items.Count
  If TypeName(SentItems.Items(i)) = "MailItem" Then
   For j = 1 To SentItems.Items.Item(i).Recipients.Count
    email_addresses = SentItems.Items.Item(i).Recipients(j).Address
    Debug.Print email_addresses
   Next
  End If
 Next
 Set SentItems = Nothing
 Set myOlApp = Nothing
End Sub

If you want the name of the recipient, use .AddressEntry

Matt

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you very much matt that worked perfectly. The points are yours!

CERTIFIED EXPERT

Commented:
Glad to help!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.