In this article, we’ll show you 5 Macro to send email from Excel. To demonstrate our methods, we’ve selected a dataset with 3 columns: “Name”, “Email”, and “City”.
Download Practice Workbook
5 Ways to Use Macro to Send Email from Excel
1. Use of Outlook Object Library to Send Email
For the first Macro, we’re going to enable “Microsoft Outlook 16.0 Object Library” to send an email from Excel. Moreover, we need to log in to our Outlook account in Excel.
In the beginning, we’re going to bring up the Visual Basic window.
- Firstly, from the Developer tab >>> select Visual Basic.
Alternatively, you can press ALT + F11 to display the VBA window.
- Secondly, from Tools >>> select “References…”.
A new dialog box will appear.
- Thirdly, select “Microsoft Outlook 16.0 Object Library”, and press OK.
Thus, we’ll enable Outlook Object Library.
- Them from Insert >>> select Module.
We’ll type our code here.
- After that, type the following code.
Sub Macro_Send_Email()
Dim eApp As Outlook.Application
Dim eSource As String
Set eApp = New Outlook.Application
Dim eItem As Outlook.MailItem
Set eItem = eApp.CreateItem(olMailItem)
eItem.To = Range("C5").Value
'These items are optional
'eItem.CC = "[email protected]"
'etem.BCC = "[email protected]"
eItem.Subject = "Sending Email using VBA from Excel"
eItem.Body = "Hello," & vbNewLine & "Hope this email finds you well." & _
vbNewLine & vbNewLine & _
"Sincerely," & vbNewLine & "Exceldemy"
'If you want to attach this workbook, then uncomment these two lines from below
'Source = ThisWorkbook.FullName
'eItem.Attachments.Add Source
eItem.Display 'can use .Send
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure Macro_Send_Email.
- Secondly, we’re declaring the variable types.
- Thirdly, we’re selecting Outlook as our Mail Application.
- Then, we’re selecting our email sending address from cell C5.
- After that, the email content is set in our code.
- Finally, “VBA Display Property” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “Send Property” to send emails without displaying.
- After that, Save and close the Module.
Now, we’ll Run the code.
- Firstly, from the Developer tab >>> select Macros.
The Macro dialog box will appear.
- Secondly, select our Sub Procedure “Macro_Send_Email”.
- Finally, press Run.
After executing the code, we’ll see the email window. We can click on Send. Thus, we’ve shown you the first method of sending an email from excel using VBA.
Read More: Send Email from Excel VBA without Outlook (4 Suitable Examples)
2. Macro for Sending Email from Gmail Account in Excel
For this method, we need less secure app access from the Gmail account. Additionally, we’ll need to enable Microsoft CDO from the References menu.
Steps:
- Firstly, as shown in the first method, bring up the References dialog box.
- Secondly, select “Microsoft CDO for Windows 2000 Library” and press OK.
- Thirdly, go to Security from your Google Account settings.
- Finally, turn on Less secure app access.
Now, we’ll input our Macro code.
- Firstly, as shown in method 1, bring up the Module window and type this code.
Option Explicit
Sub Send_Gmail_Macro()
Dim cMail As Object
Dim cConfig As Object
Dim sConfig As Variant
Dim cSubject As String
Dim cFrom As String
Dim cTo As String
Dim cCC As String
Dim cBcc As String
Dim cBody As String
cSubject = "Macro to Send Gmail"
cFrom = "[email protected]"
cTo = "[email protected]"
cBody = "Hello. This is an automated message. Please don't reply"
Set cMail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set cConfig = CreateObject("CDO.Configuration")
cConfig.Load -1
Set sConfig = cConfig.Fields
With sConfig
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "SenderGmailPassword"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With cMail
Set .Configuration = cConfig
End With
cMail.Subject = cSubject
cMail.From = cFrom
cMail.To = cTo
cMail.TextBody = cBody
cMail.CC = cCC
cMail.BCC = cBcc
cMail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure Send_Gmail_Macro.
- Secondly, we’re declaring the variable types.
- Thirdly, we’re setting the email content in our code.
- Then, we’re providing our login credentials. You need to type your own ID and Password here.
- After that, we’ve set out port to 465.
- Finally, we’re sending our email.
- Then, Save and Run this code.
We’ve successfully sent an email to our address.
Read More: Macro to Send Email from Excel with Body (3 Useful Cases)
3. Send Email to a List of Recipients from a Column
For the third method, we’re going to send emails to 7 people using Macro from Excel. We’ll find the last row of our dataset, hence our code will work for a longer list. We’ll send emails from the cell C5:C10 range.
Steps:
- Firstly, as shown in method 1, bring up the Module window and type this code.
Sub Macro_Send_Email_From_A_List()
Dim pApp As Object
Dim pMail As Object
Dim z As Integer
Dim eList As String
Dim eRow As Long
Set pApp = CreateObject("Outlook.Application")
Set pMail = pApp.CreateItem(0)
eRow = Range("C:C").SpecialCells(xlCellTypeLastCell).Row - 1
With pMail
eList = ""
For z = 5 To eRow
If eList = "" Then
eList = Cells(z, 3).Value
Else
eList = eList & ";" & Cells(z, 3).Value
End If
Next z
.BCC = eList
.Subject = "Hello There"
.Body = "This Message is brought to you by Exceldemy."
.Display 'You can use .Send here
End With
Set pMail = Nothing
Set pApp = Nothing
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure Macro_Send_Email_From_A_List.
- Secondly, we’re declaring the variable types.
- Thirdly, we’re selecting Outlook as our Mail Application.
- Then, we’re finding the last row, which is 10 for our dataset.
- After that, as our email starts from row 5 we’ve input 5 as the starting value for “variable z”. Moreover, our emails are on the C column, hence we’ve input 3 inside the Cells property.
- Then, we’re setting the email content in our code.
- Finally, “.Display” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.
- Then, Save and Run the Module.
We can see that all our emails are displayed in BCC. In conclusion, we can simply press Send to complete our task.
Read More: How to Send Email from Excel List (2 Effective Ways)
Similar Readings
- How to Send Email Automatically When Condition Met in Excel
- How to Share Excel File Online (2 Easy Methods)
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to Send Email If Conditions Met in Excel (3 Easy Methods)
- How to Enable Share Workbook in Excel
4. Macro to Send Single Sheet Using Email
In this section, we’ll send the Active Worksheet to our target person. Here, we’ll need to pick the location of our Excel file.
Steps:
- Firstly, as shown in method 1, bring up the Module window and type this code.
Sub Macro_Email_Single_Sheet()
Dim pApp As Object
Dim pMail As Object
Dim zBook As Workbook
Dim fxName As String
'Dim zSheet As Worksheet
'Dim shtName As String
Application.ScreenUpdating = False
ActiveSheet.Copy
Set zBook = ActiveWorkbook
fxName = zBook.Worksheets(1).Name
On Error Resume Next
Kill "C:\Users\Rafi\OneDrive\Desktop\Softeko\47\" & fxName
On Error GoTo 0
zBook.SaveAs FileName:="C:\Users\Rafi\OneDrive\Desktop\Softeko\47\" & fxName
Set pApp = CreateObject("Outlook.Application")
Set pMail = pApp.CreateItem(0)
With pMail
.To = "[email protected]"
.Subject = "Macro to Send Single Sheet via Email"
.Body = "Dear RecieverName," & vbCrLf & vbCrLf & _
"Your requested file is attached"
.Attachments.Add zBook.FullName
.Display
End With
zBook.ChangeFileAccess Mode:=xlReadOnly
Kill zBook.FullName
zBook.Close SaveChanges:=False
Application.ScreenUpdating = True
Set pMail = Nothing
Set pApp = Nothing
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure Macro_Email_Single_Sheet.
- Secondly, we’re declaring the variable types.
- Thirdly, we’re copying the Active Sheet and saving it as a separate Workbook.
- After that, we’re selecting Outlook as our Mail Application.
- Then, we’re setting the email content in our code.
- After that, we’ve attached the Sheet to the email.
- Finally, use “.Display” to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.
- Then, Save and Run the Module.
We’ll see the Sheet name in the window. Press Send to complete the task.
We can open the file and verify our code is working.
Read More: How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
5. Macro to Send Email Based on Cell Value
For the last method, we’ve changed our dataset a little bit. We’ve added the “Payment Due” column to the dataset. Here, we’ll send an email that contains the city “Obama”. We can clearly see that row 5 contains it, hence we’re gonna send an email to that person only.
Steps:
- Firstly, as shown in method 1, bring up the Module window and type this code.
Option Explicit
Sub Send_Email_Condition()
Dim xSheet As Worksheet
Dim mAddress As String, mSubject As String, eName As String
Dim eRow As Long, x As Long
Set xSheet = ThisWorkbook.Sheets("Conditions")
With xSheet
eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
For x = 5 To eRow
If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Obama" Then
mAddress = .Cells(x, 3)
mSubject = "Request For Payment"
eName = .Cells(x, 2)
Call Send_Email_With_Multiple_Condition(mAddress, mSubject, eName)
End If
Next x
End With
End Sub
Sub Send_Email_With_Multiple_Condition(mAddress As String, mSubject As String, eName As String)
Dim pApp As Object
Dim pMail As Object
Set pApp = CreateObject("Outlook.Application")
Set pMail = pApp.CreateItem(0)
With pMail
.To = mAddress
.CC = ""
.BCC = ""
.Subject = mSubject
.Body = "Mr./Mrs. " & eName & ", Please pay the due amount within the next week." _
& vbNewLine & "The exact amount is attached with this email."
.Attachments.Add ActiveWorkbook.FullName 'Send The File via Email
.Display 'We can use .Send here too
End With
Set pMail = Nothing
Set pApp = Nothing
End Sub
VBA Code Breakdown
- Firstly, we’re calling our first Sub Procedure Send_Email_Condition.
- Secondly, we’re declaring the Variable types and setting “Conditions” as our Sheet.
- Thirdly, the last row number is found. Moreover, our value starts from row 5, hence we’ve put row 5 to the last row in our code.
- Then, call our second Sub Procedure Send_Email_With_Multiple_Condition.
- After that, we’re selecting Outlook as our Mail Application.
- Then, the email content is set in our code.
- Here, we’re attaching the Excel file with the email using the Attachment method.
- After that, “.Display” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.
- Then, Save and Run the Module.
In conclusion, we’ve shown you yet another method of sending an email using VBA Macro from Excel.
Read More: Automatically Send Emails from Excel Based on Cell Content (2 Methods)
Practice Section
We’ve added practice datasets for each method in the Excel file.
Conclusion
We’ve shown you 5 methods to use Macro to send an email from Excel. Thanks for reading, keep excelling!
Related Articles
- How to Send Excel File to Email Automatically (3 Suitable Methods)
- How to Send Automatic Email from Excel to Outlook (4 Methods)
- Send Email from Excel VBA without Outlook (4 Suitable Examples)
- How to Automatically Send Email from Excel Based on Date
- Excel Macro: Send Email to an Address in Cell (2 Easy Ways)
- [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)