Question How to automate Outlook from Excel to send emails with optional attachment

Start by creating a new Excel VBA project, to do this from Excel hit ALT+F11 to open the VBA editor. Then In the properties window right click on the VBA project and add a new module. Go to preferences in the tools menu and add a reference to the Microsoft Outlook object library.

Copy and paste the code below into this module, the code been commented with explanations on what it does:

Sub runMail() 'this is the main macro that will call the OutlookMailSender sub
'you can call OutlookMailSender without an attachment
Call OutlookMailSender
'or specify an optional attachment to the email
'Call OutlookMailSender("c:\pathtoyourattachment.txt")
End Sub

Sub OutlookMailSender(Optional attachment)
'this is where the real work is done

'first declare the objects and variables we will use later
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.recipient
Dim objOutlookAttach As Outlook.attachment
Dim bodytext As String
Dim recipient As String

'this is the text inside the email
bodytext = "Hi " & vbNewLine & vbNewLine & vbNewLine & _
"This email was sent by Excel automation"

recipient = "recipient@somewhere.com"

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(recipient)
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message. You don't have to have this if you don't need it
Set objOutlookRecip = .Recipients.Add("edgarbadawy@there.com.au")
objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message. You don't have to have this if you don't need it
Set objOutlookRecip = .Recipients.Add("edgarbadawyboss@there.com.au")
objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = "your email subject"
.Body = bodytext
.Importance = olImportanceHigh 'High importance, if you need it

' Add attachments to the message, if one was specified from the calling function in runMail()
If Not IsMissing(attachment) Then
Set objOutlookAttach = .Attachments.Add(attachment)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
'this returns false if Outlook could not resolve the email address
If Not objOutlookRecip.Resolve Then MsgBox "Could not resolve the email for " & objOutlookRecip
Next

' Should we display the message before sending? You can use a Boolean here instead of true. As is the email will be displayed all the time.
If True Then
.Display
Else
'if false above then the email is saved in the sent items folder and sent
.Save
.Send
End If

End With

Set objOutlook = Nothing


End Sub

For those of you who don't know much about VBA or would like to receive a working Excel file with all the coding done I have an option where you can download just that for AU$3.50.

Obviously paying $3.50 dollars isn't what many would like to do but it's only a small amount, you can think of it as a donation.

Some might want to save time doing the coding or might like to improve their VBA skills then it is worth the money. Payment is via Paypal...

The link to download the file is here

Or if you are looking for an advanced version of this macro that allows you to send emails from Outlook with text formatting, reports, graphs and images in the message body from an excel list check out this product .