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.