Étiquette : vba_basics

The following VBA program determines the number of items contained within the « Sent Items » folder in Outlook. Additionally, it retrieves and displays certain properties of an email item from this folder:

Sub AccessFolder()
    Dim appOutlook As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim folder As Outlook.Folder
    Dim mailItem As Outlook.MailItem
    ' Start the Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Get the MAPI namespace
    Set ns = appOutlook.GetNamespace("MAPI")
    ' Access the default Sent Items folder
    Set folder = ns.GetDefaultFolder(olFolderSentMail)
    ' Count the number of items in the folder
    MsgBox folder.Items.Count & " items in the 'Sent Items' folder"
    ' Attempt to retrieve properties of the first item
    On Error GoTo ErrorHandler
    Set mailItem = folder.Items(1)
    MsgBox "Properties of the first item:" & vbCrLf & _
           "Subject: " & mailItem.Subject & vbCrLf & _
           "Recipient(s): " & mailItem.To & vbCrLf & _
           "Body (first 50 characters): " & Left(mailItem.Body, 50) & " ..."   
    ' Clean up and quit Outlook
    appOutlook.Quit
    Set mailItem = Nothing
    Set folder = Nothing
    Set ns = Nothing
    Set appOutlook = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Unable to retrieve properties from the item."
    appOutlook.Quit
    Set mailItem = Nothing
    Set folder = Nothing
    Set ns = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:
The method GetNamespace() of the Application object returns a namespace object, which is necessary to access Outlook folders. In this context, only the « MAPI » namespace type is supported.

Using the namespace object, the method GetDefaultFolder() retrieves a Folder object representing the default folder of the specified type. Here, it targets the « Sent Items » folder, referenced by the constant olFolderSentMail.

The folder’s Items property is a collection representing all the items (emails, calendar entries, etc.) within that folder. The total number of items can be obtained using the Count property, just as with any typical collection.

Individual items within the Items collection can be accessed using an index. In this example, the code accesses the first item (Items(1)) and displays its main properties: the email’s subject (Subject), recipient list (To), and a snippet of the email body (Body), limited here to the first 50 characters.

If an error occurs while accessing the properties of the item, the program jumps to an error handler that notifies the user that the properties could not be retrieved.

The Outlook application instance and all object references are properly released at the end to avoid resource leaks.

Long lines of code can be made more readable and manageable by splitting them into multiple lines using the underscore character _ (underscore) at the end of a line.

The following example demonstrates this using the previously known procedure for copying a worksheet  — admittedly in a somewhat exaggerated way:

Sub CopySheet()
    ThisWorkbook.Activate
    Worksheets _
        ("Sheet1"). _
        Copy _
        After:=Worksheets("Sheet1")
    ActiveSheet.Name = "Sheet1Copy"
End Sub

Explanation:

• A line ending with an underscore _ indicates that the statement continues on the following line. This allows a single statement to span two or more lines for improved clarity.
• Common break points include spaces between keywords like Copy and After, or between function parameters. This helps maintain readability.
• In the example, additional break points are used after the word Worksheets and before the dot . preceding Copy. While this is somewhat excessive stylistically, it illustrates how very long object references — common in VBA when dealing with objects — can be split across multiple lines.
• It is important to avoid placing the line break inside a word or within the double quotation marks that define string literals.

You can comment out entire blocks of code to temporarily prevent their execution without deleting them. This is especially useful for lines such as debugging outputs (e.g., MsgBox, Debug.Print) that are no longer needed in the final version of the program but that you might want to keep for reference or later use.

In the VBA development environment, you can easily comment a block of code by using the “Block Comment” button located in the Edit toolbar. This action automatically converts all selected lines into comments. Right next to this button is the “Unblock Comment” icon, which allows you to quickly reactivate a previously commented-out block of code. If you don’t see this toolbar, you can enable it via the menu View > Toolbars.