Skip to main content

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...
Labels:

Comments

Post a Comment

Popular posts from this blog

Dynamically generate Text Box and update its value in database

Hi Guys, This Post is in reference to the time I spent in order to write just a piece of code in PHP, though it would be very easy for most of the guys but if you are still learning then its a good thing to be proud of. Ok let me explain a little about what I was trying to do and how I did it. Requirement : I need to list down all the names of guys who falls under a certain category and display them on another page. I can do this by creating static text box in the landing page and fetch the value from the database OR I can dynamically generate the text box and display all the data there. For that first call the sql query of the condition like this. $sql = mysql_query("SELECT * FROM `list1` WHERE BLAH BLAH"); Now use the While query in order to get all the data required  <?php       while ($row = mysql_fetch_array($sql))       {           ?>       <tr>       <td  align="center" valign="middle" scope="row">
Post a Comment
Read more

Jquery Carousels

Jquery Carousels we all love carousels they are a fantastic way to give the effects we want our visitors to have, more than that we have multiple components to show like images, links, text etc. There are lot many ways to achieve it, Jquery is ofcourse the best possible option available outside. We all search for lot of ems it can be Jquery Flexslider  or Jcarousel Lite  whatever you choose, customization is required, in this tutorial I am not going to focus on how to install these libraries rather one step ahead, to let you know how these carousels can be called multiple times in the same page having their controls working respectively for each carousel instance. So, lets get started with the HTML <div id="sideRight"> <div id="first">       <!-- Do not change the class and tag type, as this will remain as it is for all the following divisions-->       <p class="containheader">Plans for you <a href=&quo
Post a Comment
Read more

IE 10 in IE7 compatibility mode returns error: 'SCRIPT3: Member not found'

Lately I upgraded my Internet Explorer to newer version which is 10, and was quite happy about it, but that doesn't seem to last long, when I tested my site changing the browser mode IE7, specifically using IE10, then jquery have thrown an error saying "member not found" , I was not surprised as such issues I have seen before as well, and was sure that there was JS code messed up from my side, after scanning through all the methods etc. I was not able to figure out anything, even was not sure what to search on Internet. After introspecting it was clear that this bug was not coming while I was using IE 7 from IE 9 browser installed, it came only after upgrading. At last, I found the root cause, it was not the jquery, not my JS code, not the custom dropdowns, even not all the pages were throwing this error, this was due to the jquery validate plugin, and thanks to  jester12345  who acknowledged the bug at  https://github.com/jzaefferer/jquery-validation/issues/845  an
Post a Comment
Read more