How to Use an Excel Macro to Email Without Using Outlook

There are lots of reasons to send mail from an Excel macro. For example, when a sales sum reaches a certain value, a manager may want to know right away. While there are a few ways to send email from an Excel macro, the most commonly used method is to harness Outlook. That is a problem if you don't have Outlook installed on the computer you're using to open the spreadsheet. In that case, you can use CDO, which stands for Collaboration Data Object, instead.

It's possible to send email from Excel without calling up Outlook.

Step 1

Press "Alt" and "F11" at the same time from the Excel spreadsheet to open the built-in Visual Basic editor.

Step 2

Copy and paste the following code into the blank code window: Sub CDO_Mail_Small_Text() Dim iMsg As Object Dim iConf As Object Dim strbody As String ' Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

.Item("http://schemas.Microsoft.com/cdo/configuration/sendusing") = 2 ' .Item("http://schemas.Microsoft.com/cdo/configuration/smtpserver") _ ' = "Fill in your SMTP server here" ' .Item("http://schemas.Microsoft.com/cdo/configuration/smtpserverport") = 25 ' .Update ' End With

strbody = "Hi there" & vbNewLine & vbNewLine & _
          "This is line 1" & vbNewLine & _
          "This is line 2" & vbNewLine & _
          "This is line 3" & vbNewLine & _
          "This is line 4"

With iMsg
    Set .Configuration = iConf
    .To = "person@email.com"
    .CC = ""
    .BCC = ""
    .From = """Person"" < person@email.com >"
    .Subject = "Subject Line"
    .TextBody = strbody
    .Send
End With

End Sub

Step 3

Replace "Fill in your SMTP server here" with the address of the SMTP server you wish to use.

Step 4

Press "F5" to run the macro and send your email message.

Tip

If you don’t have your own, Google provides an SMTP server (http://gmail.google.com) that you can use for free.

references