Retrieving E-Mail

There may be times when you need to retrieve an e-mail that has been sent to you.  Or you may want to summarize e-mails in a certain folder, like the Inbox.  The following example will retrieve e-mails from the Inbox and list them on the ActiveSheet.  The comments section discusses techniques for getting at folders other than the Inbox.

Sub GetFromInbox()

    Dim olApp As Outlook.Application
    Dim olNs As NameSpace
    Dim Fldr As MAPIFolder
    Dim olMail As Variant
    Dim i As Integer

    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
    i = 1

    For Each olMail In Fldr.Items
        If InStr(olMail.Body, "ozark") > 0 Or _
            InStr(olMail.Subject, "ozark") > 0 Then

            ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime
            i = i + 1
        End If
    Next olMail

    Set Fldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

End Sub

Comments:

The purpose of this procedure is to loop through all of the e-mails in the Inbox and, if they contain a certain string in the subject or the body, to record the ReceivedTime in a list on the ActiveSheet.  You can, of course, write any property you like, not just the ReceivedTime.  This particular procedure was created to graph the number of e-mails by day that contain the word "ozark."

There are some new objects introduced in this code, specifically the NameSpace object and the MAPIFolder object.  The NameSpace object isn't a typical object.  There is no physical representation of the NameSpace as there is for a Folder or a MailItem.  It serves as a gateway for access to much of the objects in Outlook's object model.  To get at the NameSpace, you use the GetNameSpace method of the Application object.  The only argument that is supported for this method is "MAPI", so that makes it nice and easy.

The Folder object represents actual folders in Outlook.  To see these folders, choose Folder List from the View menu in Outlook.  You may have nested folders, that is, folders that contain MailItems that are subordinate to the Inbox or folders that contain AppointmentItems that are subordinate to the Calendar.  This procedure uses a default folder which is particularly easy because Outlook gives us the GetDefaultFolder method of the NameSpace object.  If the GetDefaultFolder method wasn't available, that line of code would look like this:

Set Fldr = olNs.Folders("Personal Folders").Folders("Inbox")

Understanding this technique is useful when you need to access a folder that is not a default folder.  Nesting the .Folders property allows access to any folder in Outlook.  You just have to remember that the top folder is Personal Folders and the rest is easy when you see the folder list by using View - Folder List in Outlook.

Note that the olMail variable is declared as a Variant data type.  Since the Fldr variable can point to any number of folders, the items in those folders can be any number of things, MailItems, AppointmentItems, TaskItems, etc.  Declaring olMail as a MailItem will result in a type-mismatch error.  Similar to the "Code early bound, Distribute late bound" theory discussed earlier, I usually declare the variable as a MailItem so I can have access to its properties and methods via Intellisense, then change it to a Variant when I'm finished.

Prev Page: Sending E-Mail        Top of This Page        Next Page: Calendar

 

Saving Attachments

Sub SaveAttachments()

    Dim olApp As Outlook.Application
    Dim olNs As NameSpace
    Dim Fldr As MAPIFolder
    Dim MoveToFldr As MAPIFolder
    Dim olMi As MailItem
    Dim olAtt As Attachment
    Dim MyPath As String
    Dim i as Long

    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
    Set MoveToFldr = Fldr.Folders("CompSurv")
    MyPath = "C:\My Documents\Completed Survey\"

    For i =  Fldr.Items.Count to 1 Step -1
        Set olMi = Fldr.Items(i)
        If InStr(1, olMi.Subject, "Completed Survey") > 0 Then
            For Each olAtt In olMi.Attachments
                If olAtt.Filename = "Book1.xls" Then
                    olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls"
                End If
            Next olAtt
            olMi.Save
            olMi.Move MoveToFldr
        End If
    Next i

    Set olAtt = Nothing
    Set olMi = Nothing
    Set Fldr = Nothing
    Set MoveToFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

End Sub

Comments:

If you send surveys as attachments and expect to receive them back, you might need a handy way to save all those surveys when they arrive.  This procedure loops through the mail in the Inbox looking for those returned surveys by the Subject.  When found, it searches the files attached to that e-mail for Book1.xls and saves it to the specified folder.  Finally, it moves the mail to another folder within Outlook so that it won't be processed again.

Because this procedure moves the mail to a different folder, the typical For Each/Next method of looping through the mails can't be used.  Nor can that normal For/Next loop that loops from top to bottom.  The internal counter changes when you change the contents of the folder.  For this reason, a For/Next loop that loops in reverse is used.

The Instr function is used to find the text "Completed Survey" in the .Subject property.  It hopes to catch those surveys that come back with the subject changed; either to "Re: Completed Survey" or "Dick's Completed Survey."  Certainly the responder can change the subject more drastically than that and there is not fool proof method for catching all of them.

Another method would be to search for the specific attachment.  This can lead to problems if the responder changes the file name.  It can also lead to false positives if you name your surveys something as generic as Book1.xls.  Code to search for a specific attachment might look like this:

For i =  Fldr.Items.Count to 1 Step -1
    Set olMi = Fldr.Items(i)
    If olMi.Attachments.Count > 0 Then
        For Each olAtt In olMi.Attachments
            If olAtt.Filename = "Book1.xls" Then
                olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls"
            End If
        Next olAtt
        olMi.Save
        olMi.Move MoveToFldr
    End If
Next i

This simply ensures that there is at least one attachment before looping through the Attachments collection.  Combining the subject and attachments searching may even lead to better results.

The .SenderName property is used to name the newly saved file.  Because the .SaveAsFile method will overwrite any existing file with the same name, you need to make sure your file name is unique.  You can use the Dir function to see if the file exists before you save as in the example below.

If olAtt.Filename = "Book1.xls" Then
    Resp = vbYes
    If Len(Dir(MyPath & olMi.SenderName & ".xls")) > 0 Then
        Resp = MsgBox("Overwrite file?", vbYesNo)
    End If
    If Resp = vbYes Then
        olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls"
    End If
End If

Note that by inserting this code segment into the procedure above, the MailItem will still be moved to the CompSurv folder.  Also, you will need to dimension the Resp variable as a Long Integer data type.

The .Move method is used to move the MailItem to a subfolder of the Inbox.  The subfolder is identified where the variables are initialized using the .Folders property of the Fldr object variable - Fldr having already been set to the Inbox.  When you save an attachment, the MailItem becomes "dirty," meaning that Outlook believes that there are unsaved changes.  Consequently, it will not let you move a dirty item, so the .Save method is used first.

Prev Page: Sending E-Mail        Top of This Page        Next Page: Calendar

 

 Home                

 

                    Contact Me            Visit Daily Dose of Excel - an Excel weblog