Sending Email

Sending E-mail

Once you've exposed Outlook's object model with binding, you have all the tools you need to begin automating Outlook. The only step that remains is to explore Outlook's object model and figure out how to do what you want. For my money, examples are king. So I've attempted to provide you with some examples in the next sections to show how you might use your new found knowledge and hopefully provide you with some tips and tricks to speed up the learning process. I'll start with a very common activity, sending e-mail.

Sending an E-mail with Attachments

Sub SendWithAtt()

    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Dim CurrFile As String

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    ActiveWorkbook.Save

    CurrFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

    With olMail
        .To = "name@domain.net"
        .CC = "name2@domain2.com"
        .Subject = "These two files"
        .Body = ActiveSheet.Range("D4").Text & vbCrLf
        .Attachments.Add CurrFile
        .Attachments.Add "c:\My Documents\book.doc"
        .Display '.Send
    End With

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

Comments:

This procedure adds two attachments to the e-mail; the ActiveWorkbook and a Word document. The CreateItem method is used to create the new e-mail. This method can be used to create just about anything in Outlook; Tasks, Appointments, Journal Entries, etc. olMailItem is a built in constant that tells VBA which type of item to create.

Because the ActiveWorkbook will be one of the attachments, it needs to have a path. To ensure that it does, the workbook is saved. This also ensures that the most current copy of the workbook will be attached and not the previously saved copy.

The .To property of the MailItem object is an easy way to define the recipients address. There is also a Recipients collection to which you can add recipients. The next example uses the Recipients collection.

The body of the e-mail will consist of whatever is in D4 of the ActiveSheet. You can concatenate cells to create whatever body you would like. You can also use the .HTMLBody property to create as complicated a body as you want. The .HTMLBody property was introduced in Outlook 2000 and is not available in earlier versions. Creating a body using HTML is the subject of another example.

Adding attachments to the e-mail is accomplished by adding file names to the Attachments collection. The Add method of the Attachments collection accepts strings that are valid file names including the full path.

The .Display property shows the e-mail without sending it. I generally use .Display while I'm testing the procedure and change it to .Send when I'm finished.

Prev Page: Early Binding vs. Late Binding Top of This Page Next Page: Retrieving E-mail

Sending One Sheet as an Attachment

Sub SendOneSheet()

    Dim olApp As Outlook.Application
    Dim olMail As MailItem

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    ThisWorkbook.Sheets(2).Copy

    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & _
        "Sheet2.xls"

    With olMail
        .Recipients.Add "You@me.com"
        .Recipients.Add "Me@You.com"
        .Recipients.Add "Him@Her.com"
        .Subject = "That one sheet"
        .Body = "Here you go" & vbCrLf
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With

    ActiveWorkbook.Close False

    Kill ThisWorkbook.Path & "\" & "Sheet2.xls"

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

Comments:

To send one sheet of the current workbook, the .Copy method of the Worksheet object is used with no arguments. This creates a new workbook with the copied sheet in it and makes it the ActiveWorkbook. Then the workbook is saved so that it has a proper path so it can be attached later.

Note that the Recipients collection is used instead of the .To property. The Recipients collection is useful when you are adding recipients inside of a loop. Otherwise, the .To property works just as well.

The .FullName property used when adding the attachment was introduced in Excel 2000 and was not available in earlier versions. It can be recreated in earlier versions by using ActiveWorkbook.Path & "\" & ActiveWorkbook.Name.

The newly created workbook is closed and the Kill statement is used to delete the file from the disk.

Prev Page: Early Binding vs. Late Binding Top of This Page Next Page: Retrieving E-mail

Using HTML in Message Body

Sub SheetInBody()

    Dim olApp As Outlook.Application
    Dim olMail As MailItem

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    With olMail
        .To = "dickk@thedomain.com"
        .Subject = "Table in body"
        .HTMLBody = SheetToHTML(ThisWorkbook.Sheets(1))
        .Display
    End With

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

Comments:

Nothing too new here. The HTMLBody property is set to a string that represents HTML. You can code the string yourself, but in this example, I used a function that turns a worksheet into an HTML file and reads the contents of that file. You can see the SheetToHTML function, but it's nothing too fancy. There is also a RangeToHTML function for when you just want to use a range instead of the whole sheet.

Remember, the HTMLBody property was introduced in Outlook 2000 and is unavailable in earlier versions.

Prev Page: Early Binding vs. Late Binding Top of This Page Next Page: Retrieving E-mail

Converting Excel to HTML

Below are the two functions mentioned above that covert a sheet and a range to html, respectively.

Public Function SheetToHTML(sh As Worksheet)

    Dim TempFile As String
    Dim fso As Object
    Dim ts As Object

    Randomize

    sh.Copy
    TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm"

    ActiveWorkbook.SaveAs TempFile, xlHtml
    ActiveWorkbook.Close False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

SheetToHTML = ts.ReadAll

    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    Kill TempFile

End Function

Function RangetoHTML(Rng As Range)

    Dim wb As Workbook
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim i As Long
    Dim Rng2 As Range
    Dim DelCol1 As String
    Dim DelCol2 As String

    Randomize

    TempFile = Rng.Parent.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm"

    'Copy the sheet to a new workbook and copy the cells to avoid the
    '255 character limit when copying sheets
    Rng.Parent.Copy
    Rng.Parent.Cells.Copy ActiveSheet.Cells

    Set wb = ActiveWorkbook
    Set Rng2 = wb.Sheets(1).Range(Rng.Address)

    'Convert to values
    Rng2.Copy
    Rng2.PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    'Delete rows below
    Rng2.Parent.Rows(Rng2.Rows(Rng2.Rows.Count).Row + 1 & ":65536").Delete

    'Delete columns to right
    DelCol2 = Chr(64 + Rng2.Parent.Columns(Rng2.Columns _
    (Rng2.Columns.Count).Column + 1).Column)
    Rng2.Parent.Columns(DelCol2 & ":IV").Delete

    'Delete rows above
    If Rng2.Rows(1).Row > 1 Then
        Rng2.Parent.Rows("1:" & Rng2.Rows(1).Row - 1).Delete
    End If

    'Delete columns to left
    If Rng2.Columns(1).Column > 1 Then
        DelCol1 = Chr(64 + Rng2.Parent.Columns(Rng2.Columns(1).Column - 1).Column)
        Rng2.Parent.Columns("A:" & DelCol1).Delete
    End If

    wb.SaveAs TempFile, xlHtml
    wb.Close False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

    RangetoHTML = ts.ReadAll

    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    Kill TempFile

End Function

Prev Page: Early Binding vs. Late Binding Top of This Page Next Page: Retrieving E-mail

Home

Contact Me Visit Daily Dose of Excel - an Excel weblog