Microsoft Office Tutorials and References
In Depth Information
E-mailing from Excel with Outlook
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub EmailWithOutlook()¶
'Variable declaration¶
'Outlook application¶
Dim oApp As Object¶
'Outlook MailItem¶
Dim oMail As Object¶
Dim WB As Workbook¶
Dim WS As Worksheet¶
Dim FileName As String¶
Dim SendSheetOnly As Boolean¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
'Ask the user what to send¶
Select Case MsgBox( _¶
Prompt:="Do you want to send the active sheet only ?", _¶
Case vbYes¶
'Yes, make a copy of the active sheet.¶
SendSheetOnly = True¶
Set WB = ActiveWorkbook¶
'Save without formulas ?¶
Cells.PasteSpecial Paste:=xlPasteValues¶
'Clear the clipboard¶
Application.CutCopyMode = False¶
FileName = "Temp.xls"¶
'Continue when error occurs¶
On Error Resume Next¶
Kill "C:\" & FileName¶
On Error GoTo 0¶
WB.SaveAs FileName:="C:\" & FileName¶
Case vbNo¶
If Len(ActiveWorkbook.Path) = 0 Then¶
'File hasn't been saved before¶
MsgBox "Can't send an unsaved workbook", vbCritical¶
GoTo exiting¶
End If¶
SendSheetOnly = False¶
Set WB = ActiveWorkbook¶
Case vbCancel¶
'olDiscard = 1¶
oMail.Close SaveMode:=1¶
GoTo exiting¶
End Select¶
Search JabSto ::

Custom Search