This site will look better if you upgrade to a browser that supports web standards.

Sending Mail from MacXL

You can send a workbook as an attachment using the menu command File/Send To/Mail Recipient (As Attachment). This opens your default email program and, typically, attaches the file to a blank message. Unfortunately, there's no way to specify the recipient and subject, much less body text, using the menus.

In Windows version of XL, the VBA SendMail command can be used to send a workbook as an attachment without user input - the recipient and the subject can be provided as arguments to the SendMail method.

But for XL98/01/v.X/04, to quote MVP Bernard Rey, "SendMail simply DOES NOT work." Here are a couple of workarounds:

Open blank message with attachment from VBA

This technique, which I saw first published by MVP Jim Gordon, mimics the menu commands to open a blank message with the active workbook attached.

    Public Sub SendMyMail()
        With Application.CommandBars.Add
            .Visible = False
            .Controls.Add(Type:=msoControlButton, Id:=2188).Execute
            .Delete
        End With
    End Sub

Send a workbook via Entourage using MacScript

Bernard Rey, in a newsgroup article, provided an MacScript (AppleScript) substitute for VBA's SendMail, using Entourage. I've modified it a bit to take the workbook, recipient and subject as arguments

    Public Sub ErageSendMail(theBook As Workbook, _
            recipient As String, subject As String)
        Dim mailStr As String
        mailStr = _
            "Tell application ""Microsoft Entourage""" &vbNewLine & _
            "make new outgoing message with properties" & _
            "{recipient:""" & recipient & """,subject:""" & subject & _
            """,attachment:""" & theBook.FullName & """}" & vbNewLine _
            & "move the result to out box folder" & vbNewLine & _
            "send" & vbNewLine & _
            "end tell"
        MacScript mailStr
    End Sub

You can call it like this:

ErageSendMail ThisWorkbook, "name@address", "This is the subject"

This can be easily modified for Outlook Express, and should be modifiable for other email applications.

Sending a single worksheet

You can send a single worksheet by copying the worksheet to a new workbook first, then sending it. Using Jim Gordon's technique:

    Public Sub MailSingleSheet()
        With Application
            .ScreenUpdating = False
            ActiveSheet.Copy
            With .CommandBars.Add
                .Visible = False
                .Controls.Add(Type:=msoControlButton, Id:=2188).Execute
                .Delete
            End With
            ActiveWorkbook.Close SaveChanges:=False
            .ScreenUpdating = True
        End With
    End Sub

or using the ErageSendMail macro:

    Public Sub MailSingleSheet()
        Dim sPath As String
        With Application
            .ScreenUpdating = False
            ActiveSheet.Copy
            With ActiveWorkbook
                .SaveAs Filename:=.Name
                sPath = .FullName
                ErageSendMail ActiveWorkbook, "name@address", "Attached: " & .Name
                .Close SaveChanges:=False
            End With
            Kill sPath
            .ScreenUpdating = True
        End With
    End Sub

This page last updated Saturday, 22 January 2005

McGimpsey & Associates

Home

MacOffice

Sending Mail from MacXL

Open blank message with attachment from VBA

Send a workbook via Entourage using MacScript

Sending a single worksheet

QuickLinks