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

Question

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?

VB.NET IsNot vba AndAlso IsNot excel-vba AndAlso does not do macros — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Oct 11, 2016 at 16:01
Somewhere out there is a girl called Mye Mail who's getting sooo fed up with all these spam emails..... — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Oct 11, 2016 at 16:27

Darren Bartrup-Cook · Accepted Answer · 2016-10-11 16:23:57Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related