Send email from excel

Hi All,

As we all play around a lot with Excel in our Day to Day tasks, so automating that is a must thing.

Below is a code to send the whole excel sheet via email on the click of a button, I know you can find this on various other sites as well, but I am also sharing the same as well.

Below is the Macro to send a excel file as a attachment.


Sub Send_email()


Dim wb As Workbook
Dim I As Long
Set wb = ActiveWorkbook


    If Val(Application.Version) >= 12 Then
        If wb.FileFormat = 51 And wb.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                   "Save the file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If


    On Error Resume Next   
    
   wb.sendmail "xxx@abc.com", _
                    "Please find the attached email"
                    MsgBox "Mail Sent Successfully "                   
                  


    On Error GoTo 0
End Sub

The Code is really small but the utility is Big.

Also if you want to send a email on click of a button but also needs to send a copy to another person which is optional, so you can make a new userform in excel and place the checkbox and button and can then place the event over there like

Private Sub CommandButton1_Click()

Dim wb As Workbook

Dim I As Long

Set wb = ActiveWorkbook

    If Val(Application.Version) >= 12 Then

        If wb.FileFormat = 51 And wb.HasVBProject = True Then

            MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _

                   "Save the file first as xlsm and then try the macro again.", vbInformation

            Exit Sub

        End If

    End If

    On Error Resume Next    

    If CheckBox1.Value = True Then

    

   wb.sendmail "xxx@xx.com", _

                    "Please find the attachedsheet"

                    MsgBox "Mail Sent Successfully!!"

                    

                    wb.sendmail " xxx@abc.com", _

                    " Please find the attached sheet "

                    MsgBox " A copy has been sent successfully!! "

                    

           Me.Hide

           Else

           wb.sendmail "xxx@xx.com", _

                    "Please find the attached sheet"

                    MsgBox "Mail Sent Successfully!!"

                    Me.Hide

                    

                    End If    

    On Error GoTo 0

End Sub

Place the above code in the userform1 commandbutton click event and simply call this form via macro in Modules like given below...

Module 1

Sub Send_mail_Userform()

Userform1.show

End Sub

And assign the macro Send_mail_Userform() to a object, button or whatever you like and you are DONE !!!

Let me know if any one has requirement for send a email as single worksheet, selected area or multiple tabs from excel.

Cheers!!

Happy Blogging...