Learn to Compose and send emails from Excel using VBA

Learn to Compose and send emails from Excel using VBA

Below is the Macro (VBA Code) to do it with ease (can be automated as needed)

1) Press Alt+F11 (this will open VB Editor)
2) Insert a Module from Project Window (Left side)
3) Copy and paste the below code to the module
4) Edit the code as required
5) Press F5
Tada..!! your email is ready to send.

Bonus Tip: Save the file as Excel 97-2003 (XLS) or Excel Macro Enabled (XLSM) Workboook, otherwise you will lose the macro.

Learn to Compose and send emails from Excel using VBA

Download example file with code @ bottom

Sub SendEmails()

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
'Input email ids here and use ";" is case email need to send multiple users
.To = "RaghuRam.Star@gmail.com"
.CC = "Email2@gmail.com"
.BCC = "Email3@gmail.com"
'Subject Line comes here
.Subject = "Email Subject Line"

'email body comes here use "vblf" for multi-line text
.Body = "Body Text Line 1" & _
vbLf & "Text Line 2" & _
vbLf & "Text Line 3"

'you can add attachments here
.Attachments.Add ("C:FilePathAttachmentFile.txt")

'I prefer to use "Display" to check my email before i send. However you can directly send email using "Send" command
.Display '.Send

End With
On Error GoTo 0

'Free up the memory
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Download Example File Send_Email_From_Excel_ExampleFile.xls

Also check below VBA Resources

Comments

    1. admin

      Its helpful, if someone wants to send bunch of emails at once.
      Some times I got to send 50 – 60 emails a day, where I can not put all the email ids in ‘TO’ section nor I can ‘BCC’ it. Simply I have to send all the emails individually. In this case, I had all the email ids in a sheet and used this code in loop…. thats all… 1hr of manual efforts reduced to a 1 min.

      Soon I will update it to work for gmail/yahoo… you might like it…

  1. vikas gupta

    It is the best code I ever found.
    Sir, what be the change if I want mail using yahoo.in

    Thanks & Regards,
    Vikas.

Leave a Reply

Your email address will not be published. Required fields are marked *

four × 4 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.