Simple Email

Basic Exercise

Statement

Send a simple email (unformatted text) that is readable by all MS Outlook versions. Include in the body of the text the net profit of the income statement.

Note: MS Outlook needs to be configured for this macro to function correctly.

Suggested Concepts:

Dim - Declares a variable.
Outlook Library - Allows VBA to interact with Outlook.
Set - Assigns an object reference to a variable or property.
With - Executes a series of statements that repeatedly refer to a single object or structure.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Use Outlook object library to create the code then remove the library and alter the code to the variable values as they were assigned by Outlook (late binding)

Place the emails .Display right after the .BodyFormat or the email signature might be omitted. Also, do not forget to place & .body at the end of the emails text or the signature might by omitted as well.

Solved Exercise

Exercise Solution

We declare the variables

Dim OlOut As Object
Dim OlMail As Object
Dim Result As String

We assign to a string variable the worksheet data that we want to send
```
Result = Range("A12") & vbTab & Range("D12")
```
We define the variable OlOut as the Outlook and the variable OlMail as the creation of an email in Outlook
```
Set OlOut = CreateObject("OutLook.Application")
Set OlMail = OlOut.CreateItem(0)
```

We define the type of email, body text, title and the recipient and send the email

With OlMail
    .Bodyformat = 1
    .Display
    .body = "Caros," & vbNewLine & "Segue o resultado do exercício de 2016." & vbNewLine & vbNewLine & Result & .body
    .Subject = "Email Simples"
    '.To = "fulano@gmail.com"
    '.Send
End With

Be careful: It is very common to send an email containing errors. Check your code very carefully before adding .Send.

Consolidated Answer

    Sub Solution()

    Dim OlOut As Object
    Dim OlMail As Object
    Dim Result As String

    Result = Range("A12") & vbTab & Range("D12")

    Set OlOut = CreateObject("OutLook.Application")
    Set OlMail = OlOut.CreateItem(0)

    With OlMail
        .Bodyformat = 1
        .Display
        .body = "Dear all," & vbNewLine & "The result of the fiscal year follows." & vbNewLine & vbNewLine & Result & .body
        .Subject = "Simple Email"
        '.To = "someone@gmail.com"
        '.Send
    End With

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED