Copying Worksheet to new workbook, naming, saving, emailng, deleting

JSA715

Active Member
Joined
Aug 3, 2008
Messages
254
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.

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.:biggrin:
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top