Editor's Note: The following is a guest post by MVP Ron de Bruin as part of the MVP Award Program Blog's "MVPs for Office and SharePoint 2010" series. Ron de Bruin is working for a company that is growing chrysanthemums (nothing to do with Excel) and has been an Excel MVP since 2002. He spends his free time updating his web site - Ron's Excel Tips, writing articles for MSDN, and helping people in the online communities.

Excel and Windows Live Mail

Important: if you set a Windows Live mail version before Windows Live mail 2011 as your default mail program it is possible that if you want to mail from Excel you get a General mail failure because it isn’t fully MAPI-qualified like Windows Mail or Outlook. It makes no different if you try to create a mail with the Excel menu options or if you create a mail with a macro (VBA code).

Note: Microsoft fixed this problem in Windows Live mail 2011.

When I was testing my VBA code examples I notice that when I try to run the test macro the third time it was working and after that it was working the first time every time I try to run it until you close/reopen Excel. This is the same if you try to mail with the Excel menu options.

Solutions that work with every Windows Live Mail version


RDBMail add-in for Excel 2007-2010

 

I created an add-in named RDBMail for Excel 2007-2010 that create a new tab on the Ribbon with a lot of mail options. This is a very easy way to mail from Excel with Windows Live Mail. RDBMail Add-in for Excel 2007-2010 with Outlook Express, Windows Mail and Windows Live Mail

http://www.rondebruin.nl/mail/add-in.htm

Note: There is also an Add-in only for Outlook on that page, so pick the correct one.

Use VBA code to mail from Excel

To avoid the General mail failure error I use a loop now in my SendMail code examples that try to create the mail three times and will exit the loop if there is no error.

For I = 1 To 3

            .SendMail "ron@debruin.nl", _

                      "This is the Subject line"

            If Err.Number = 0 Then Exit For

        Next I

The following subroutine sends a newly created workbook with just the ActiveSheet.

It is saving the workbook before mailing it with a date/time stamp.

After the file is sent the workbook will be deleted from your hard disk.

Change the mail address and subject in the macro before you run it.

Sub Mail_ActiveSheet()

'Working in 97-2010

    Dim FileExtStr As String

    Dim FileFormatNum As Long

    Dim Sourcewb As Workbook

    Dim Destwb As Workbook

    Dim TempFilePath As String

    Dim TempFileName As String

    Dim I As Long

    With Application

        .ScreenUpdating = False

        .EnableEvents = False

    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook

    ActiveSheet.Copy

    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format

    With Destwb

        If Val(Application.Version) < 12 Then

            'You use Excel 97-2003

            FileExtStr = ".xls": FileFormatNum = -4143

        Else

            'You use Excel 2007-2010, we exit the sub when your answer is

            'NO in the security dialog that you only see when you copy

            'an sheet from a xlsm file with macro's disabled.

            If Sourcewb.Name = .Name Then

                With Application

                    .ScreenUpdating = True

                    .EnableEvents = True

                End With

                MsgBox "Your answer is NO in the security dialog"

                Exit Sub

            Else

                Select Case Sourcewb.FileFormat

                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51

                Case 52:

                    If .HasVBProject Then

                        FileExtStr = ".xlsm": FileFormatNum = 52

                    Else

                        FileExtStr = ".xlsx": FileFormatNum = 51

                    End If

                Case 56: FileExtStr = ".xls": FileFormatNum = 56

                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50

                End Select

            End If

        End If

    End With

    '    'Change all cells in the worksheet to values if you want

    '    With Destwb.Sheets(1).UsedRange

    '        .Cells.Copy

    '        .Cells.PasteSpecial xlPasteValues

    '        .Cells(1).Select

    '    End With

    '    Application.CutCopyMode = False

 

    'Save the new workbook/Mail it/Delete it

    TempFilePath = Environ$("temp") & "\"

    TempFileName = "Part of " & Sourcewb.Name & " " _

                 & Format(Now, "dd-mmm-yy h-mm-ss")

    With Destwb

        .SaveAs TempFilePath & TempFileName & FileExtStr, _

                FileFormat:=FileFormatNum

        On Error Resume Next

        For I = 1 To 3

            .SendMail "ron@debruin.nl", _

                      "This is the Subject line"

            If Err.Number = 0 Then Exit For

        Next I

        On Error GoTo 0

        .Close SaveChanges:=False

    End With

    'Delete the file you have sent

    Kill TempFilePath & TempFileName & FileExtStr

    With Application

        .ScreenUpdating = True

        .EnableEvents = True

    End With

End Sub

You can also use the following line if you know the sheet you want to mail: 

Sheets("Sheet5").Copy 

It doesn't have to be the active sheet used at that time.

If you want to Display the mail instead of sending it directly you must remove the mail address like this:

.SendMail "", _

                      "This is the Subject line"

Check out the webpage of the macro example above for more information and an example workbook:
More Information

You can find a lot more code examples for Windows Live Mail in the SendMail section on this page.
Note: The SendMail code will also work with Outlook Express, Windows Mail and Outlook.
Example Code for sending mail from Excel
Note: If you use Outlook see the Outlook object model examples on the same page and see also these articles on MSDN. You have much more options if you use Outlook.
Using VBA in Excel to Send Workbooks and Ranges Through E-Mail with Outlook (Part 1 of 2)
 
Using VBA in Excel to Send Workbooks and Ranges Through E-Mail with Outlook (Part 2 of 2)
 
Creating and Sending PDF Files from Excel 2007 and Excel 2010