Sending Mail Automatically via Excel VBA

Hi one and all,

This time I have come up with a code to send emails automatically.

This code may have plenty of uses to many of you. Actually it uses Outlook email client to send email.

The example use can be if you run a procedure, you may want to intimate yourself or other via email, at the end of the Procedure, that task as been accomplished.

Make sure OUTLOOK Client application is open before running this macro.

Here is the code ( with Late Binding )

       
Sub SendMailAuto()
    Set OL_Obj = CreateObject("Outlook.Application")
    Set New_Mail = OL_Obj.CreateItem(olMailItem)
        
    With New_Mail
            .To = "Add Second Recipient Here"
            .BCC = "Add First Recipient Here"
            .CC = "Add Third Recipient Here"
            .Attachments.Add "Path of the file or Document"
            .Subject = "Enter your Subject here."
            .Body = "Write the Content of the Mail"
            .Display
    End With
    For i = 1 To 1000: DoEvents: Next   ' Putting a halt to allow Outlook window to show up.
    SendKeys "%s", True
End Sub

If you want to try Early binding, you have to first refer to Microsoft Outlook xx.x Object library form Tools menu-------> References. Here is the Code:-

       
Sub SendMailAuto()
    Dim OL_Obj As Outlook.Application
    Dim New_Mail As Outlook.MailItem
    Set OL_Obj = New Outlook.Application
    Set New_Mail = OL_Obj.CreateItem(olMailItem)
    
    With New_Mail
        .To = "Add Second Recipient Here"
        .BCC = "Add First Recipient Here"
        .CC = "Add Third Recipient Here"
        .Attachments.Add "Path of the file or Document"
        .Subject = "Enter your Subject here."
        .Body = "Write the Content of the Mail"
        .Display
    End With
    
    For i = 1 To 1000: DoEvents: Next  ' Putting a halt to allow Outlook window to show up.
    SendKeys "%s", True
End Sub

Change the To, BCC, CC, Subject, Body. Attachment properties accordingly before running the macro. You can remove the unwanted properties as well.

Suggestions and doubts are always invited.

Other related post:- Excel VBA - E-Mailing a Selection, Range or whole worksheet (as Email Body)

Excel Forum Post:- http://www.excelforum.com/excel-general/1068474-excel-vba-sending-email-validation-list.html

Regards,