Excel Macro To Send Email & Delete Data

Question

I am testing using macros to accomplish having a single Excel button send the file (as attachment or raw data) to a recipient and then delete all the data entered. The Excel worksheet in question is a template and has all cells locked except for editable cells.

So far I have added 2 buttons - one to email and one to clear the user's inputted data.

Can anyone recommend a simpler, more streamlined solution? Like a single button to email the data then clear the data?

Answer 1

Use Cells.Clear

It will clear the entire worksheet. Do you want to clear only limited cells, then you need to define that range and then clear?

Answer 2

I've tried many variations of what you see below, none have worked.

Answer 3

Just put Clearcells before Endsub of first routine.

Answer 4

I have this so far:

Private Sub CommandButton2_Click()

    Dim xOutApp As Object

    Dim xOutMail As Object

    Dim xMailBody As String

    On Error Resume Next

    Set xOutApp = CreateObject("Outlook.Application")

    Set xOutMail = xOutApp.CreateItem(0)

    xMailBody = "Body content" & vbNewLine & vbNewLine & _

              "This is line 1" & vbNewLine & _

              "This is line 2"

                  On Error Resume Next

    With xOutMail

        .To = "******@domain.com"

        .CC = ""

        .BCC = ""

        .Subject = "Test email send by button clicking"

        .Body = xMailBody

        .Attachments.Add ActiveWorkbook.FullName

        .Display   'or use .Send

    End With

    On Error GoTo 0

    Set xOutMail = Nothing

    Set xOutApp = Nothing

End Sub

Private Sub CommandButton3_Click()

Clearcells

End Sub

How would I combine the scripts into 1 single button?

Answer 5

The code which you have generated for deletion, you should be able to copy and paste this code into Send Mail code at the end.