Well I spent a while looking for something that would email a worksheet to a specified person when a user clicked a button on a form. I never found anything in this forum that specifically met my requirements, so I was able to piece together a macro that works for me and I wanted to share it.
Application.Run ("Value_All") simply values out the sheet.
This macro will copy the worksheet to a new workbook, save the workbook to C:\Temp with the name weeklyupdate.xls. Then, opens an email, attaches the file and sends it to a group. It doesnt actually send it on your behalf because outlook won't let it. Everytime I tried, outlook pops up a warning and makes the user click yes to verify they want the email to send. Once the email is sent, the macro finishes by deleting the file in the temp folder.
I'm not sure if this is customary to post success, but I saw similar questions to this and wanted to share.
Code:
Private Sub WeeklyUpdate_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Application.Run ("Value_All")
Sheets("Weekly Update").Copy
ChDir "C:\Temp"
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp\WeeklyUpdate.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "someone@somewhere.com"
.Subject = "2009 Master Site Sheet - Weekly Update"
.Attachments.Add ("C:\Temp\WeeklyUpdate.xls")
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill "C:\Temp\WeeklyUpdate.xls"
Application.ScreenUpdating = True
End Sub
Application.Run ("Value_All") simply values out the sheet.
This macro will copy the worksheet to a new workbook, save the workbook to C:\Temp with the name weeklyupdate.xls. Then, opens an email, attaches the file and sends it to a group. It doesnt actually send it on your behalf because outlook won't let it. Everytime I tried, outlook pops up a warning and makes the user click yes to verify they want the email to send. Once the email is sent, the macro finishes by deleting the file in the temp folder.
I'm not sure if this is customary to post success, but I saw similar questions to this and wanted to share.