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


1

5 Responses

Show all responses
  1. rajesh
    November 17, 2014
  2. Yanko Yemi Yamuhura
    December 28, 2014
    • admin
      December 29, 2014
  3. vikas gupta
    August 10, 2016
  4. Srinivas
    August 10, 2016

Leave a Reply