How to Send Email Using Excel 2007 VBA

Send email from Excel using VBA.

Visual Basic for Applications (VBA) is commonly used to automate routine tasks in Microsoft Office applications. Sending an email from Microsoft Excel 2007 using VBA is useful in cases when you need to continuously email the latest data in an Excel workbook. In a few steps you can write code to easily automate the task of sending the email through Microsoft Outlook.

Instructions

1. Start Microsoft Office 2007 and select the "Developer" tab. Click on "Visual Basic" and select "Tools." Click on "References" check "Microsoft Outlook 12.0 Object Library" and select "OK."

2. Type the following to create a new sub:

   Sub sendEmail()

   Press "Enter."

3. Type the following to create your variables:

   Dim oLookApp As Outlook.Application

   Dim oLookMail As Outlook.MailItem

4. Type the following to assign the outlook object and create a new email:

   Set oLookApp = New Outlook.Application

   Set oLookMail = oLookApp.CreateItem(0)

5. Type the following to define the variables for your email:

   With oLookMail

   .To = "[e-mail]"

   .Subject = "My Workbook"

   .Body = "I'm e-mailing this workbook.."

   .Attachments.Add ActiveWorkbook.FullName

   .send

   End With

6. Execute your sub to email the current Excel workbook.

References

• Microsoft: Using VBA in Excel to Send Workbooks