Solved: Email active worksheet using Outlook

[wotme]

Hi just joined your forum and need some help with an existing post. Its ideal for my use just need to tweak it slightly

vbaexpress.com/kb/getarticle.php?kb_id=326

The post says "If you want to send the workbook with all formulas removed, you will need to Copy/Paste values before saving the temporary file. Please ask for help in the Excel forum if you would like to do this. "

I do need to know this, anybody help?

[Leith Ross]

Hello wotme,

The needed change is in bold in the code below...


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
'Replace all Formulas wiith their values
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Value
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

[wotme]

Perfect,thank you:clap: