CODEWITHSUNDEEP
vbaexcel
Wazz
Wazz

Reputation: 3

Running code on a click within an Excel spreadsheet, preferably via a button

I have never used macro's before or used Visual Basic for Applications. I need this as part of a checklist in Microsoft Excel 2013.

My Aim: Once the checklist has been filled, I want the active worksheet to be attached to an email whilst auto-filling the email addresses and the subject which will be "Checklist_XXX" the XXX part for example will be amended depending on who is using the checklist, so it could be Checklist_12345.

Steps I have taken: The checklist is complete, and I have created a ActiveX button which by default has no code.

I found the following code online which seems to be what I need:

Sub Mail_workbook_Outlook_1()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "[email protected],[email protected]"
        .Subject = "Checklist_"
        .Body = "I have attached my checklist related to change"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

What I think this does: I believe it will create an Outlook email with the attached message and will have [email protected] and [email protected] as recipients with the subject and body amended. I changed it from .send to .display so I can review before I send.

The short question: How do I run this code on a click within my Excel spreadsheet, preferably via a button?

Upvotes: 0

Views: 106

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

To attach the code to the click of an ActiveX command button:

Click the button and select View Code.
You'll be taken to a screen with some code in it:

Private Sub CommandButton1_Click()

End Sub

Simply add your procedure name in there:

Private Sub CommandButton1_Click()
    Mail_workbook_Outlook_1
End Sub

NB:
In may be worth your while to update the button name to something more meaningful than CommandButtonx.
Right-click, select properties and update the (name) field.

Upvotes: 1

Related Questions