Catégorie : Excel VBA Course

When creating a recurring appointment, an additional object is involved: a RecurrencePattern object that defines the pattern for the series of appointments.

Here is an example:

Sub CreateRecurringAppointment()
    Dim appOutlook As Outlook.Application
    Dim appointment As Outlook.AppointmentItem
    Dim pattern As Outlook.RecurrencePattern
    Set appOutlook = CreateObject("Outlook.Application")
    ' Create a new appointment
    Set appointment = appOutlook.CreateItem(olAppointmentItem)
    ' Assign properties
    appointment.Start = "10/06/2025 07:45"
    appointment.Duration = 45 ' Duration in minutes
    appointment.Subject = "Test"
    ' Get the recurrence pattern object
    Set pattern = appointment.GetRecurrencePattern
    ' Set recurrence properties
    pattern.RecurrenceType = olRecursWeekly
    pattern.DayOfWeekMask = olWednesday Or olSaturday
    pattern.PatternStartDate = "18.03.2020"
    pattern.PatternEndDate = "04.04.2020"
    ' Save the recurring appointment
    appointment.Save
    ' Clean up
    appOutlook.Quit
    Set pattern = Nothing
    Set appointment = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:

• The CreateItem() method creates an object of type AppointmentItem.
• The appointment’s properties such as Start, Duration, and Subject are assigned.
• The GetRecurrencePattern() method returns an object of type RecurrencePattern.
• Some important properties of the RecurrencePattern object are then set:
  • RecurrenceType: Defines the recurrence frequency and must be set first. Valid values include:
    • olRecursDaily (daily),
    • olRecursWeekly (weekly),
    • olRecursMonthly (monthly).
  • DayOfWeekMask: Specifies the days of the week on which the appointment occurs. This is a bitmask composed by bitwise OR of day constants:
    • olSunday (1),
    • olMonday (2),
    • olTuesday (4),
    • olWednesday (8),
    • olThursday (16),
    • olFriday (32),
    • olSaturday (64).

You can combine any combination of these constants using the Or operator.

• • PatternStartDate and PatternEndDate: Define the date range during which the recurring appointments occur.
• Finally, the recurring appointment is saved using the Save() method.

Items in the Calendar folder are objects of the type AppointmentItem. These objects have different properties compared to MailItem objects. The following example shows how to add a new appointment to the personal calendar using VBA:

Sub CreateAppointment()
    Dim appOutlook As Outlook.Application
    Dim appointment As Outlook.AppointmentItem
    ' Start Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Create a new appointment item
    Set appointment = appOutlook.CreateItem(olAppointmentItem)
    ' Assign properties to the appointment
    appointment.Start = "10/06/2025 09:45"
    appointment.Duration = 60        ' Duration in minutes
    appointment.Subject = "Test"
    appointment.Location = "here"
    ' Save the appointment
    appointment.Save
    ' Quit Outlook and clean up
    appOutlook.Quit
    Set appointment = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:
Using the method CreateItem() along with the constant olAppointmentItem, a new Outlook item of the type AppointmentItem is created.

Several key properties of the appointment are then set:

• Start: Defines the start date and time of the appointment (format: dd.mm.yyyy hh:mm).
• Duration: Specifies the length of the appointment in minutes.
• Subject: The subject or title of the appointment.
• Location: The place where the appointment takes place.

The new appointment is saved in Outlook’s calendar with the Save() method.

The following example lists all contacts whose last name begins with the letter « M »:

Sub AccessContacts()
    Dim appOutlook As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim contactsFolder As Outlook.Folder
    Dim contact As Outlook.ContactItem
    Dim output As String
    ' Start Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Get the MAPI namespace
    Set ns = appOutlook.GetNamespace("MAPI")
    ' Access the default Contacts folder
    Set contactsFolder = ns.GetDefaultFolder(olFolderContacts)
    ' Attempt to retrieve matching contacts
    On Error GoTo ErrorHandler
    For Each contact In contactsFolder.Items
        If Left(contact.LastName, 1) = "M" Then
            output = output & contact.LastName & ", " & contact.FirstName & _
                     " (" & contact.Email1Address & ")" & vbCrLf
        End If
    Next contact
    ' Display the collected contacts
    MsgBox output
    ' Clean up
    appOutlook.Quit
    Set contact = Nothing
    Set contactsFolder = Nothing
    Set ns = Nothing
    Set appOutlook = Nothing
    Exit Sub
ErrorHandler:
    ' Ignore errors and continue with next contact
    Resume Next
End Sub

Explanation:
This code accesses the default Contacts folder in Outlook, identified by the constant olFolderContacts.

All items in this folder are of the type ContactItem and possess properties such as last name (LastName), first name (FirstName), and primary email address (Email1Address).

The script examines the last name of each contact using the Left() function to check if the first character is « M ». For all contacts matching this criterion, it concatenates their last name, first name, and email address into a string.

If an error occurs while accessing any contact (for example, due to an unexpected item type), the error handler ensures the loop continues with the next contact, effectively ignoring problematic entries.

Finally, the compiled list of matching contacts is displayed in a message box.

New contacts can be created in a manner similar to composing new emails. The following example demonstrates this process:

Sub CreateContact()
    Dim appOutlook As Outlook.Application
    Dim contactItem As Outlook.ContactItem
    ' Start Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Create a new contact item
    Set contactItem = appOutlook.CreateItem(olContactItem)
    ' Assign properties to the contact
    contactItem.LastName = "Muster"
    contactItem.FirstName = "Max"
    contactItem.Email1Address = "max.muster@mailziel.de"
    ' Save the new contact
    contactItem.Save
    ' Quit Outlook and clean up
    appOutlook.Quit
    Set contactItem = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:
The CreateItem() method of the Outlook application object is used here with the constant olContactItem to create a new item of type ContactItem. This generates a blank contact form.

Next, the script assigns values to key properties of the contact, specifically the last name (LastName), first name (FirstName), and primary email address (Email1Address).

Finally, the new contact is saved to Outlook’s contacts folder using the Save() method.

The Outlook interface will then display this newly created contact, as illustrated in the referenced figure.

The following VBA example demonstrates how to analyze emails within the « Sent Items » folder in Outlook to answer questions such as:

• What percentage of all emails contain at least one attachment?
• On average, how many attachments does an email with attachments have?
• When and to whom was a specific file sent as an email attachment?

Sub AccessMailAttachments()
    Dim appOutlook As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim folder As Outlook.Folder
    Dim mailItem As Outlook.MailItem
    Dim attachment As Outlook.Attachment
    Dim countWithAttachments As Integer
    Dim percentWithAttachments As Single
    Dim totalAttachments As Integer
    Dim attachmentsPerMail As Single
    Dim output As String
    Const maxItems As Integer = 100
    Dim processedItems As Integer
    ' Start Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Get MAPI namespace
    Set ns = appOutlook.GetNamespace("MAPI")
    ' Access the Sent Items folder
    Set folder = ns.GetDefaultFolder(olFolderSentMail)
    ' Initialize counters
    processedItems = 0
    countWithAttachments = 0
    totalAttachments = 0
    ' Loop through emails in the folder (limit to maxItems)
    For Each mailItem In folder.Items
        processedItems = processedItems + 1
        ' Check if email has attachments
        If mailItem.Attachments.Count > 0 Then
            countWithAttachments = countWithAttachments + 1
            totalAttachments = totalAttachments + mailItem.Attachments.Count
        End If
        ' Stop if maxItems processed
        If processedItems > maxItems Then Exit For
    Next mailItem
    ' Calculate percentage of emails with attachments
    percentWithAttachments = countWithAttachments / folder.Items.Count
    MsgBox Format(percentWithAttachments, "0.00 %") & _
           " of emails in the 'Sent Items' folder have attachments."
    ' Calculate average number of attachments per email with attachments
    If countWithAttachments > 0 Then
        attachmentsPerMail = totalAttachments / countWithAttachments
    Else
        attachmentsPerMail = 0
    End If
    MsgBox Format(attachmentsPerMail, "0.00") & _
           " attachments per email that contains attachments."
    ' Search for a specific attachment by filename
    processedItems = 0
    output = ""
    For Each mailItem In folder.Items
        processedItems = processedItems + 1
        For Each attachment In mailItem.Attachments
            If attachment.FileName = "Mappe9.xlsm" Then
                output = output & "Sent to " & mailItem.To & _
                         " on " & mailItem.CreationTime & vbCrLf
            End If
        Next attachment
        If processedItems > maxItems Then Exit For
    Next mailItem
    ' Display results if the file was found
    If output <> "" Then MsgBox output
    ' Clean up
    appOutlook.Quit
    Set attachment = Nothing
    Set mailItem = Nothing
    Set folder = Nothing
    Set ns = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:
This script uses a For Each loop to iterate over all items in the « Sent Items » folder of Outlook. Since processing a very large number of emails can take a long time, the script limits the examination to the first 100 items by using a constant (maxItems) and a counter (processedItems). You can adjust maxItems to analyze more or fewer emails.

For each email (mailItem), the code checks if it contains any attachments by inspecting the Attachments.Count property. If the email has attachments, it increments the counter for emails with attachments and adds the number of attachments to a total count.

After processing, it calculates and displays:

• The percentage of emails that contain at least one attachment relative to all emails in the folder.
• The average number of attachments per email that has attachments.

Next, using nested loops, the code examines every attachment in each email to find any attachment matching a specific filename — in this case, « Mappe9.xlsm ». For every match found, the script collects and outputs the recipient (mailItem.To) and the creation date/time of the email (mailItem.CreationTime).

Finally, the Outlook application and all object references are properly released to free resources.

If you want to send only a specific range from a workbook, you can first let the user select the range, copy it into a new workbook, and then email that new workbook:

Sub SendSelectedRange()
    Dim selectedRange As Range
    Dim appOutlook As Outlook.Application
    Dim MailItem As Outlook.MailItem
    ' Let user select the range to send
    Set selectedRange = Application.InputBox( _
        Prompt:="Select the range to email", Type:=8)
    ' Copy the selected range
    selectedRange.Copy
    ' Create a new workbook and paste the copied range
    Workbooks.Add
    ActiveSheet.Paste
    ' Save and close the new workbook
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\C:\Users\POPOLY\Desktop\Doc.xlsx"
    ActiveWorkbook.Close
    ' Start Outlook and create a new email
    Set appOutlook = CreateObject("Outlook.Application")
    Set MailItem = appOutlook.CreateItem(olMailItem)
    ' Set email properties
    MailItem.To = "max.muster@mailziel.de"
    MailItem.Subject = "Test"
    ' Attach the newly saved workbook
    MailItem.Attachments.Add ThisWorkbook.Path & "\AttachmentWorkbook.xlsx"
    ' Send the email
    MailItem.Send
    ' Quit Outlook and clean up
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
    Set selectedRange = Nothing
End Sub

Explanation:

• The method Application.InputBox() allows the user to select a range with the mouse.
• The Copy() method copies this selected range. Since no destination is specified, the copied data is stored in the clipboard.
• A new workbook is created with Workbooks.Add(), which becomes the active workbook.
• The clipboard contents are pasted into the new workbook using Paste().
• The new workbook is saved (here named AttachmentWorkbook.xlsx) and then closed.
• An email is created using CreateItem(). The To, Subject, and Attachments properties are assigned. The attachment is the newly saved workbook.
• The email is placed into the Outlook Outbox, ready for sending.

The previous example can be easily adapted to send a mail merge email to multiple recipients whose addresses are stored in an Excel worksheet.

Sub MailMergeEmail()
    Dim appOutlook As Outlook.Application
    Dim MailItem As Outlook.MailItem
    Dim i As Integer
    Dim BccList As String
    ' Build list of recipients from the worksheet
    ThisWorkbook.Worksheets("Sheet3").Activate
    i = 1
    Do While Cells(i, 3) <> ""
        BccList = BccList & Cells(i, 3).Value & ";"
        i = i + 1
    Loop
    ' Remove the trailing semicolon
    BccList = Left(BccList, Len(BccList) - 1)
    ' Start Outlook and create an email
    Set appOutlook = CreateObject("Outlook.Application")
    Set MailItem = appOutlook.CreateItem(olMailItem)
    ' Set properties and send
    MailItem.To = "moyofranck@gmail.com"
    MailItem.BCC = BccList
    MailItem.Subject = "Barbecue Party at 5 PM"
    MailItem.Send
    ' Quit Outlook and clean up
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:

• An Excel worksheet contains names, first names, and email addresses of several people.
• A loop constructs a string of all email addresses from column 3 of the worksheet, separating each with a semicolon. The trailing semicolon is removed using the Left() and Len() functions.
• The email is created similarly to the previous example, with the properties To, Bcc, and Subject assigned.
• The Bcc property receives the constructed string of recipients to ensure each recipient’s address remains hidden from others.
• The email is sent using the Send() method, placing it in the Outlook Outbox.

All elements of an email can also be created and composed entirely via VBA code. The following example shows how to create an email with recipient, subject, body content, and an attachment, and then send it:

Sub ComposeEmail()
    Dim appOutlook As Outlook.Application
    Dim MailItem As Outlook.MailItem
    ' Start Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Create a new mail item
    Set MailItem = appOutlook.CreateItem(olMailItem)
    ' Set properties
    MailItem.To = "moyofranck@gmail.com"
    MailItem.Subject = "Test"
    MailItem.Body = "Hello" & vbCrLf & "World"
    ' Add attachment with error handling
    On Error GoTo ErrorHandler
    MailItem.Attachments.Add "C:\Users\POPOLY\Desktop\fleur.jpeg"
    ' Send the email
    MailItem.Send
    ' Quit Outlook and release memory
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:

• The variable MailItem is declared as an object of type Outlook.MailItem.
• Using the CreateObject() function, an object of type Outlook.Application is created and a reference to it is returned. This reference is used to interact with the Outlook application.
• The CreateItem() method of the Application object creates a new item—in this case, a mail item. Different types of items have different available properties and methods.
• The properties To, Subject, and Body assign the email recipient, subject, and message body respectively. Multiple recipients can be specified in a single string, separated by semicolons. Unlike the SendMail() method, recipients here are not provided as an array.
• The Attachments collection holds all attachments of the email. The Add() method adds files to this collection. Since the file might not exist, error handling is used to catch such exceptions.
• The Send() method places the email into the Outlook Outbox for sending.
• Alternatively, you could use the Display() method instead of Send(). This shows the email to the user for review and prompts whether to save it. If the user agrees, the email is saved in the Outlook Drafts folder and can be sent later.
• At the end, the Outlook application is closed using the Quit() method.

If you want to always give the user the opportunity to add additional elements to an email (such as Cc, Bcc recipients, or a message body), you can invoke Excel’s built-in email dialog box:

Sub EmailDialog()
    Dim success As Boolean
    success = Application.Dialogs(xlDialogSendMail).Show("moyofranck@gmail.com", "Test")
    If success Then
        MsgBox "Email was saved"
    Else
        MsgBox "Email saving was canceled"
    End If
End Sub

Explanation:

• The Show() method of the Dialogs collection is called to display one of Excel’s many built-in dialog boxes (see Section 10.1, « Built-in Dialog Boxes »).
• Using the constant xlDialogSendMail, the email dialog box is opened.
• You can specify a single recipient or multiple recipients as an array.
• The automatically generated subject line is known to be incorrect and should be overwritten by the user.
• The active workbook is attached to the email.
• The user can add further elements to the email, such as additional recipients or a message body, within the dialog.
• The method returns a Boolean indicating whether the email was saved. However, note that the return value is sometimes True even if the user cancels the email operation in Outlook.
• If the user saves the email, it is placed in the Outlook Outbox.