- 1). Launch Excel and click the "Developer" tab. Click "Visual Basic" to open the Microsoft Visual Basic Window. Click the "Tools" menu and click "References." Scroll down the list box and check the box next to "Microsoft Outlook <version number> Object Library."
- 2). Click the "Insert" menu and click "Module" to insert a new code module. Type the following to create a new procedure to send email:
Sub sendEmail()
On Error GoTo Err_sendEmail: - 3). Type the following to declare your variables:
Dim oLookApp As Outlook.Application
Dim oLookMail As Outlook.MailItem
Dim dataToSend As String - 4). Type the following to create the Outlook objects:
Set oLookApp = New Outlook.Application
Set oLookMail = oLookApp.CreateItem(0) - 5). Type the following to add two rows of data:
Range("A1").Select
ActiveCell.Value = "Test 1 data"
Range("A2").Select
ActiveCell.Value = "Test 2 data" - 6). Type the following to save the data into a string variable:
Range("A1").Select
dataToSend = ActiveCell.Value
Range("A2").Select
dataToSend = "Row 1:" & dataToSend & "....Row 2:" & ActiveCell.Value - 7). Type the following to define email information and send it:
With oLookMail
.To = "someemailaddress@domain.com"
.Subject = "Email Excel"
.Body = dataToSend
.send
End With
Edit the email address and enter a working email address. - 8). Type the following to end the procedure and capture any errors:
Exit_sendEmail:
Exit Sub
Err_sendEmail:
MsgBox Err.Description
Resume Exit_sendEmail:
End Sub - 9). Press "F5" to run the procedure and send an email from Excel.