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 dump@ozgrid 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