MVPs for Office and SharePoint 2010: Excel and Windows Live Mail
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
https://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:
https://www.rondebruin.nl/mail/folder1/mail2.htm
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
https://www.rondebruin.nl/sendmail.htm
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)
https://msdn.microsoft.com/en-us/library/ff458119(office.11).aspx
Using VBA in Excel to Send Workbooks and Ranges Through E-Mail with Outlook (Part 2 of 2)
https://msdn.microsoft.com/en-us/library/ff519602(office.11).aspx
Creating and Sending PDF Files from Excel 2007 and Excel 2010
https://msdn.microsoft.com/en-us/library/ee834871(office.11).aspx