Emailing from Excel VBA

Hi Raxit, thanks for your query. The subroutine below should do the trick. Note I've commented out some expressions at the end so the email remains on the screen, and you might want to uncomment various other expressions to hard code recipient names etc. Try it out and see how far you get.

__________________

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
'.Subject = "Look at my workbook!"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
'WB.ChangeFileAccess Mode:=xlReadOnly
'Kill WB.FullName
'WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
'Set oMail = Nothing
'Set oApp = Nothing
End Sub

_________________

Hope this helps,

Anthony