Using excel VBA code How to send email and attachment

Using excel VBA code How to send email and attachment.

Requirements:

1. Microsoft office Excel & Excel VBA

2. Microsoft Outlook

Description

I am going to draft an email with attachment using excel. first we need to mention To email ID, CC(if required), email Subject and attachment path in excel sheet and then copy below coding in VBA coding area then execute the code it will generate the email draft

Coding area

Step 1: open excel and press Alt+F11 for VBA coding window

Step 2: insert module and past below code

Excel screen shot

Step 3: Before running macro. 

go to Tools--> Reference--> and select the library according version your using

Microsoft Outlook 12.0 Object Library (Excel 2003 and low ver)

for 2007 & 2010 Microsoft Outlook 14.0 Object Library

Code:

Sub mailsending( )

Dim objol As New Outlook.Application

Dim objmail As MailItem

Set objol = New Outlook.Application

Set objmail = objol.CreateItem(olMailItem)

Toname = Range("a1").Value

ccname = Range("b2").Value

Subject = Range("c2").Value

file = Range("d2" ).Value

   With objmail

       .To = Toname

       .CC = ccname

       .Subject = Subject

       .Attachments.Add file 'adds attachment to email

.Display

    End With

    Application.EnableEvents = True

    End Sub

Run the above code in VBA it will draft email automaticlly and display like below screen shot

If your going to send 10 or more email at a time us below coding

Sub OPSMailer()

Dim objol As New Outlook.Application

Dim objmail As MailItem

For p = 2 To Range("a65536").End(xlUp).Row

Set objol = New Outlook.Application

Set objmail = objol.CreateItem(olMailItem)

Toname = Range("a" & p).Value

ccname = Range("b" & p).Value

Subject = Range("c" & p).Value

file = Range("d" & p).Value

   With objmail

       .To = Toname

       .CC = ccname

       .Importance = 2

       .Subject = Subject

       .Attachments.Add file 'adds attachment to email

.Display

    End With

Next p

    Application.EnableEvents = True

End Sub

Regards,

Suryakiran G

how to send email using excel vba, attached a file using excel vba to email