Outlook VBA - Need to grab SMTP address, not Contact Name

13 Dec 2004 2:16 AM

djsanq

Hi all. I am writing a macro that scans a specified Outlook folder and
grabs the email addresses from the To, From and CC fields of the emails
in the folders and stuff's it into an excel file.

My macro works except if the addresses were entered through someone's
contact list, I end up grabbing their contact name, and not their
actual email address.

can anyone assist?? thanks!

here's a code snippet:

'loop through the folder contents
If fld.Items.Count > 0 Then
For Each Item In fld.Items

If Item.SenderEmailAddress <> "" Then
Set rng = wks.Cells(i, 1)
rng.Value = Item.SenderEmailAddress
i = i + 1
End If

If Item.CC <> "" Then
Set rng = wks.Cells(i, 1)
rng.Value = Item.CC
i = i + 1
End If

If Item.To <> "" Then
Set rng = wks.Cells(i, 1)
rng.Value = Item.To
i = i + 1
End If

Next Item

End If

13 Dec 2004 3:10 AM

Sue Mosher [MVP-Outlook]

For To and CC, iterate the Recipents collection. Each Recipient has an
Address property.

In Outlook 2003, you can use the MailItem.SenderEmailAddress, but note that
it's subject to security prompts.

In earlier versions, there is no Outlook property that returns the sender's
email address. You can either use CDO (or Redemption to avoid security
prompts -- http://www.dimastr.com/redemption/) to get the From address or
use Outlook to get the Reply To address. Sample code at
http://www.outlookcode.com/d/code/getsenderaddy.htm

To get the SMTP address from an Exchange sender or recipient, use CDO or
Redemption and the PR_EMAIL (&H39FE001E) MAPI property to obtain the SMTP
address from the AddressEntry object. See
http://www.outlookcode.com/d/code/getsenderaddy.htm#redemption and
http://www.cdolive.com/cdo5.htm#EMailAddressOfSender for examples.

--
Sue Mosher, Outlook MVP
Author of
     Microsoft Outlook Programming - Jumpstart for
     Administrators, Power Users, and Developers
     http://www.outlookcode.com/jumpstart.aspx

<djs***@excite.com> wrote in message

Show quoteHide quote

news:1102904172.519763.69770@z14g2000cwz.googlegroups.com...
> Hi all. I am writing a macro that scans a specified Outlook folder and
> grabs the email addresses from the To, From and CC fields of the emails
> in the folders and stuff's it into an excel file.
>
> My macro works except if the addresses were entered through someone's
> contact list, I end up grabbing their contact name, and not their
> actual email address.
>
> can anyone assist?? thanks!
>
>
> here's a code snippet:
>
> 'loop through the folder contents
> If fld.Items.Count > 0 Then
> For Each Item In fld.Items
>
> If Item.SenderEmailAddress <> "" Then
> Set rng = wks.Cells(i, 1)
> rng.Value = Item.SenderEmailAddress
> i = i + 1
> End If
>
> If Item.CC <> "" Then
> Set rng = wks.Cells(i, 1)
> rng.Value = Item.CC
> i = i + 1
> End If
>
> If Item.To <> "" Then
> Set rng = wks.Cells(i, 1)
> rng.Value = Item.To
> i = i + 1
> End If
>
> Next Item
>
> End If
>