Solved: Excel Macro for Button

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
I need some assistance creating a macro to automate a process by clicking on a button. I would like to have multiple functions performed when a single button is clicked. They are:

1. Save the active file
2. Open a new mail message (and attach the active file if possible)
3. Fill in the To: with "[email protected]"
4. Fill in the CC: with "[email protected]"
5. Fill in the subject with "Performance Tracker"

Is this possible? I have no idea where to start other than I know it will require VBA. Can someone help?
 
Joined
Jul 25, 2004
Messages
5,458
Sure it is. You can use something like this. It assumes you are using Outlook and uses late binding ...


Code:
Option Explicit

Sub sendBook()
    If ActiveWorkbook Is Nothing Then Exit Sub
    
    Dim fName As Workbook
    Dim OLApp As Object, OLMsg As Object
    
    If MsgBox("Are you sure you want to send the ActiveWorkBook?", _
    vbYesNo, "Send ActiveWorkbook") = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    
    Set fName = ActiveWorkbook
    fName.Save
    Set OLApp = CreateObject("Outlook.Application")
    Set OLMsg = OLApp.CreateItem(0)
    With OLMsg
        .To = "[email protected]"
        .Cc = "[email protected]"
        .Bcc = ""
        .Subject = "Performance Tracker"
        .Attachments.Add fName.FullName
        .Display
    End With
    
    Application.ScreenUpdating = True
    Set OLApp = Nothing
    Set OLMsg = Nothing
    Set fName = Nothing
    
End Sub

This routine goes into a Standard Module. Note that the Save will save the workbook to the current active (windows) directory, wherever that is. You can specify the location if you'd like.

HTH
 
Joined
Sep 4, 2003
Messages
4,916
Just be aware that the automation of the email will also result in a security prompt that will be automatically generated by Outlook. This prompt which was designed to combat the spread of viruses will require that you click "YES" when prompted whether or not to allow Outlook to automatically send the email. Since you are already clicking a button to start the macro it shouldn't be a big problem but if you plan on completley automating the entire process in the future you will need to take this into consideration.

Rollin
 
Joined
Jul 25, 2004
Messages
5,458
Rollin, this particular code, will not. What you are talking about is if you changed the ".Display" to ".Send", then you would get a prompt asking you to grant access to Outlook. If this is wished to be bypassed, Outlook Redemption is needed.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
If couchdan wants the email to be sent, it also depend on the Outlook level, older versions of Outlook did not respond with the confirmation request.
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
I just want the email prepared to be sent, they can click the send button. I will give this a try and see if it gives the result I am looking for. Thanks a bunch! I will post back my results.
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
firefytr,
It appears to work beautifully. I will leave this thread open for a couple of days in case I encounter problems, but my first test worked just like I wanted to.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top