OzGrid

Send Emails From Excel

< Back to Search results

 Category: [Excel]  Demo Available 

Send Emails From Excel

 

Send Email From Excel. VBA Code to Send an Email from Excel

 

Got any Excel Questions? Free Excel Help

 

Excel VBA allows us to send emails from within Excel and even send the Workbook as an attachment.

SendMail Method

The SendMail Method is very easy to use and will send any specified Excel Workbook as an attachment to specified recipients. As the code below specifies the Active Workbook the code is best stored in your Personal.xls

Sub SendActiveWorkbook()

    ActiveWorkbook.SendMail _

    Recipients:="[email protected]", _

    Subject:="Try Me " & Format(Date, "dd/mmm/yy")

End Sub

If you only wish to send one Worksheet from a Workbook we can use the method shown below. It creates a new Workbook housing ONLY the sheet we copy. It then sends the 1 sheet Workbook as an attachment, then closes the new Workbook without saving.

Sub Send1Sheet_ActiveWorkbook()

'Create a new Workbook Containing 1 Sheet (left most) _

 and sends as attachment.

 

    ThisWorkbook.Sheets(1).Copy

    

    

    With ActiveWorkbook

         .SendMail Recipients:="[email protected]", _

          Subject:="Try Me " & Format(Date, "dd/mmm/yy")

         .Close SaveChanges:=False

    End With

    

End Sub

Route Method

Another method we can use is the Route Method, it routes the workbook, using the workbook's current routing slip. This allows us to specify numerous recipients and have the Workbook send to the next in the routing slip. When sent, the text below is automatically added to the body of the email;

The enclosed document has a routing slip.  When you are done reviewing this document, choose Send To from the Microsoft Office Excel File menu.  Then select Next Routing Recipient to continue the routing.

In the case of the code below, the Workbook would first be sent to [email protected] who would take the needed action and then go File>Send to>Next Routing Recipient and the Workbook would then be automatically sent to the next Recipient at [email protected]

Sub RouteActiveWorkbook()

    With ActiveWorkbook

       .HasRoutingSlip = True

           With .RoutingSlip

                .Delivery = xlOneAfterAnother

                .Recipients = Array("[email protected]", _

                    "[email protected]", "[email protected]")

                .Subject = "Check This Out"

                .Message = "Please fill in the Workbook and send it on."

          End With

        .Route

    End With

End Sub

See also:

Index to Excel VBA Code
Return an Excel Worksheet/Sheet Name to a Cell
Excel: Reverse Cell Text/Content
Add Excel Right Click Menu
Delete Rows Meeting Condition/Criteria
Worksheet Change Event: Automatically Run Excel Macros When a Cell Changes

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)