none
Get Outlook To field in SMTP format using vba

    Question

  • I've spent hours trying to find an answer to this.  Another person even asked the question in this forum, but got no answer.

    I have a client who wants to import Outlook email addresses (SMTP) into Access from the To field, From field and the Cc field. He'll be using them to send out 'potential client' emails. He will set up a special Outlook folder (AccessEmails) and copy the emails he wants into it. I've tried creating a linked file in Access, but it only has names...no email addresses. 

    So far, using vba in Access, I’ve been able to get the From and Cc emails ok, but not the To emails.

    For example, I have a To field with this in it: john and emma; rath@matnet.com; Lucy; Mary Hilands.  You’ll note that only one of them has the email address. If I have the email open, select one of the ‘name only’ entries and right click, a popup shows a Send Mail option with the SMTP address in parentheses after it.  Ergo, the data must be in Outlook somewhere.   How do I get hold of it in vba?

    By the way I know how to parse thru the To field and pick the emails up one at a time.

    Office 2003, but, hopefully, also for Office 2010.


    Will Baker
    Wednesday, August 31, 2011 5:54 PM

All replies

  • I have a client who wants to import Outlook email addresses (SMTP) into Access from the To field, From field and the Cc field.

    This is exactly what free OutlookFreeware.com's Save Email Addresses utility does. It can save all emails to the plain text list. After that you can easily import it to Access.

     


    Alexey Kuznetsov,
    Relief Software
    Thursday, September 01, 2011 5:00 PM
  • Thanks, I'll probably end up doing something like that, but if a software operation like that can get hold of it, why can't someone using vba?  If, for some reason, it's something that is impossible to do, I wish some guru in this forum would say so....hopefully saying why not.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    t

     

     

     

     

     

     

     

     

     

     

     


    Will Baker
    Thursday, September 01, 2011 7:17 PM
  • Hi,

    The question about outlook development(VBA) should be post at outlook for developer forum.

    http://social.msdn.microsoft.com/Forums/en-US/outlookdev/threads


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, September 02, 2011 2:18 AM
    Moderator
  • I did a lousy job of testing and have found out that the recipient process gives me both the Cc and the To emails.  I’m not at all a sophisticated vba

     programmer, but perhaps the mods below for both the Sender and theToCc emails will help someone out.

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("SavedEmails")

    Dim OlApp As Outlook.Application

    Dim olns As Outlook.NameSpace

    Dim olFolder As Outlook.MAPIFolder

    Dim MyFolder1 As Outlook.MAPIFolder

    Dim MyFolder2 As Outlook.MAPIFolder

    Dim myItems As Outlook.Items

    Dim NumRecords As Integer

    Set OlApp = CreateObject("Outlook.Application")

    Set olns = OlApp.GetNamespace("MAPI")

    Set MyFolder1 = olns.Folders("Personal Folders")

    Set MyFolder2 = MyFolder1.Folders("AccessEmails")

    Set myItems = MyFolder2.Items

    Dim i As Integer

    NumRecords = myItems.Count

    If NumRecords = 0 Then

        MsgBox "No sender emails to export."

        GoTo finishfunction

    End If

    For i = 1 To NumRecords

        If IsNull(myItems(i).SenderEmailAddress) Then

            MsgBox ("email has no sender email address")

            GoTo finishfunction

        End If

        'don't add dupes

        If Not IsNull(DLookup("[SavedEmail]", "SavedEmails", "[SavedEmail] =" & qte & myItems(i).SenderEmailAddress & qte)) Then

            GoTo getnextaddress

        End If

    rst.AddNew

    rst!SavedEmail = myItems(i).SenderEmailAddress

    rst.Update

    getnextaddress:

        Next i

    finishfunction:

    rst.Close

    End Function

    Function GetToCcEmails()

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("SavedEmails")

    Dim Mailobject As Object

    Dim RecipientObject As Object

    Dim OlApp As Outlook.Application

    Dim olns As Outlook.NameSpace

    Dim olFolder As Outlook.MAPIFolder

    Dim MyFolder1 As Outlook.MAPIFolder

    Dim MyFolder2 As Outlook.MAPIFolder

    Dim myItems As Outlook.Items

    Dim NumRecords As Integer

    Set OlApp = CreateObject("Outlook.Application")

    Set olns = OlApp.GetNamespace("MAPI")

    Set MyFolder1 = olns.Folders("Personal Folders")

    Set MyFolder2 = MyFolder1.Folders("AccessEmails")

    Set myItems = MyFolder2.Items

    NumRecords = myItems.Count

    If NumRecords = 0 Then

        MsgBox "No To or Cc emails to export."

        GoTo finishfunction

    End If

    For Each Mailobject In MyFolder2.Items

        If Mailobject.Class = olMail Then

            For Each RecipientObject In Mailobject.Recipients

                If (InStr(1, RecipientObject.Address, "@")) Then

                    'don't add dupes

                    If Not IsNull(DLookup("[SavedEmail]", "SavedEmails", "[SavedEmail] =" & qte & RecipientObject.Address & qte)) Then

                        GoTo getnextaddress

                    End If

                rst.AddNew

                rst!SavedEmail = RecipientObject.Address

                rst.Update

                End If

    getnextaddress:

            Next

        End If

    Next

    finishfunction:

    rst.Close

    End Function


    Will Baker
    Friday, September 02, 2011 4:50 PM