Send emails from Excel

in Excel

How to send emails from Excel (VBA code included)


Radu, is there a simple way to send emails from Excel? I have a file with approvals and I want the people involved in the process to be nitified when we someone hits the “approve” button.

This was a question I received from one of the participants in one of my advanced Excel courses. My answer was (like Obama said) – yes we can.

Macro / VBA

As we do not have a send email option in Excel, we will need to use a macro and some VBA code for this. I know this sounds complicated, but don’t worry, no VBA programming skills is needed for this, only copy paste skills. 🙂

Outlook required

For this to work you will have to have Outlook installed and configured with an email address. Also, it’s best to have Outlook open while running the code.

Send emails from Excel – Video

In the video bellow I will guide you through using the code bellow.

Code – static email

Let’s start with a simple example, a static email. Running the code bellow will send the email bellow to the recipients I defined in the code.

Sub PlainEmail()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)
strbody = “Hey” & vbNewLine & vbNewLine & _
“This is some dummy text” & vbNewLine & _
“Via: radupopa.co” & vbNewLine

On Error Resume Next

With OutMail
.To = “changethis@email.com”
.CC = “”
.BCC = “”
.Subject = “Sample subject”
.Body = strbody
.Send



End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Code – custom subject

In the course I had, my students had to send this automated email with a custom subject.

Sub StaticEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)

strbody = “Hey,” & vbNewLine & vbNewLine & _
“This is a test email.” & vbNewLine & _
“via: radupopa.co” & vbNewLine

On Error Resume Next

With OutMail
.TO = “changethis@email.com;changethis@emailtoo.com”
.CC = “”
.BCC = “”
.Subject = “Add your subject here ” & Range(“A1”)
.Body = strbody
.Send

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Code errors / download

By copying the code from the site, you might experience some errors. We blame the copy past for that. To make things easier for you, you can grab a file with the macros included here.

Refferences

If you want to read more about this subject, this is the place to go, lots of examples and implementations.


Write a Comment

Comment