The article will provide you with some methods on how to send email from Excel VBA without Outlook. Generally it’s easy to use your email account to send messages to others. But using Microsoft Visual Basic for Application (VBA) can give you some extra benefit. You can send one or more emails by VBA.
In the dataset, we showed two different criteria to send emails without Outlook. Here is the dataset for sending multiple emails. All of them are dummy addresses except [email protected] and [email protected], which are mine. I’m using my address so that I can show you how the methods work.
Send Email from Excel VBA without Outlook: 4 Ways
1. Sending Single Email from Gmail Account Using Excel VBA
We can send an Email automatically by using the Microsoft Visual Basic for Application (VBA) based on dates. You can make a list of dates when you want to send the Emails to their corresponding recipients. Let’s go through the process below for a better understanding. In this section, we will only work with the dates.
- First, go to Developer >> Visual Basic
- Then the VBA window will open. Select Insert >> Module
- Select Tools >> References
- Check Microsoft CDO for Windows 2000 Library. We select this to send email using Gmail without Outlook.
- Type the following code in the VBA Module.
Option Explicit
Sub SendMail01()
Dim Email_Obj As Object
Dim Email_Configuration As Object
Dim Mail_Configuration As Variant
Dim Email_Sub As String
Dim Message_From As String
Dim Message_To As String
Dim Email_Cc As String
Dim Email_Bcc As String
Dim Message_Body As String
Email_Sub = "Emergency Notice"
Message_From = "[email protected]"
Message_To = "[email protected]"
Message_Body = "Heads up, enemy UAV spotted."
Set Email_Obj = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set Email_Configuration = CreateObject("CDO.Configuration")
Email_Configuration.Load -1
Set Mail_Configuration = Email_Configuration.Fields
With Mail_Configuration
.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") = "ZxcvbN0987"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With Email_Obj
Set .Configuration = Email_Configuration
End With
Email_Obj.Subject = Email_Sub
Email_Obj.From = Message_From
Email_Obj.To = Message_To
Email_Obj.TextBody = Message_Body
Email_Obj.CC = Email_Cc
Email_Obj.BCC = Email_Bcc
Email_Obj.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
Here, after running this code, the message “Heads up, enemy UAV spotted” will be sent to the recipient’s email address.
Code Breakdown
- First, we named our Sub Procedure SendMail01.
- Then we declared some variables Email_Obj and Email_Configuration as Object.
- After that, we again declare Mail_Configuration as Variant.
- We also declared Email_Sub, Message_From, Message_To, Email_Cc, Email_Bcc, Message_Body as String.
- Then we defined the Subject, Sender Email Address, Receiver Email Address and Text of the Email with the variables Email_Sub, Message_From, Message_To and Message_Body
- We will send the email without the help of Outlook. For that reason, we set Email_Obj to Message to create the object of the email.
- After that, we set the configuration of the email with a With Statement. We defined some items for the email.
- Finally used Send property to send Email directly without displaying. If you want you can use the Display property to see the email
That’s how we create the outlet of this VBA code to send mail without Outlook.
- Now just run the Macro by clicking on the Run
- This operation will send the email to the recipient address [email protected].
- Go to the Inbox of your email address and you will see a new email Click to open it.
Thus you can send a single Email without Outlook.
Read More: Excel Macro to Send Email Automatically
2. Send Multiple Emails from Excel VBA without Outlook
In this section, I’ll show you how to send multiple emails using VBA without Outlook. Suppose some guys want a template on how to make a daily activity report in Excel from you. You sent that through email to some of them. Now you will send this email to those who didn’t receive it. The sent emails are marked with dates. When you execute the code in the following part of this section, you will get a confirmation message in your Excel workbook and you will see the updated dates beside the email addresses meaning they are sent on that date.
Steps:
- Follow Section 1 to open a VBA Module.
- Type the following code in the Module.
Sub SendMail02()
Dim Email_Text, Email_Configuration As Object
Dim Email_Subject, Email_Message, Surname, first_name, Email, Link As String
Dim first_row, last_row, Count_Sent_Message As Long
Dim EmailFields As Variant
Set Email_Text = CreateObject("CDO.Message")
Set Email_Configuration = CreateObject("CDO.Configuration")
Email_Configuration.Load -1
Set EmailFields = Email_Configuration.Fields
With EmailFields
.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/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.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") = "ZxcvbN0987"
.Update
End With
With Sheet2
Link = .Range("F11").Value
last_row = .Range("E999").End(xlUp).Row
For first_row = 14 To last_row
Email_Subject = .Range("F3").Value
Email_Message = .Range("F4").Value
If .Range("L" & first_row).Value <> Empty Then GoTo NextRow
Surname = .Range("E" & first_row).Value
first_name = .Range("F" & first_row).Value
Email = .Range("K" & first_row).Value
Email_Subject = Replace(Replace(Email_Subject, "#first_name#", first_name), "#Surname#", Surname)
Email_Message = Replace(Replace(Email_Message, "#first_name#", first_name), "#Surname#", Surname)
With Email_Text
Set .Configuration = Email_Configuration
.To = Email
.CC = ""
.BCC = ""
.From = """YourName"" <Your Email Address>"
.Subject = Email_Subject
If Link <> Empty Then .AddAttachment Link
.TextBody = Email_Message
.Send
End With
Count_Sent_Message = Count_Sent_Message + 1
.Range("L" & first_row).Value = Now
NextRow:
Next first_row
Set Email_Text = Nothing
Set Email_Configuration = Nothing
Set EmailFields = Nothing
End With
MsgBox Count_Sent_Message & " Emails have been sent"
End Sub
Code Breakdown
- First, we named our Sub Procedure
- Then we declared some variables Email_Text and Email_Configuration as Object.
- We also declared Email_Subject, Email_Message, Surname, first_name, Email and Link as String; then first_row, last_row, Count_Sent_Message as Long.
- After that, we again declare EmailFields as Variant.
- We will send the email without the help of Outlook. For that reason, we defined the Email_Text and Email_Configuration as Message and CDO.Configuration respectively to create objects for the email.
- After that, we set the configuration of the email with a With Statement. We defined some items for the email.
- The Count_Sent_Message variable will determine the number of sent emails and at the same time, it also tells us how many emails are unsent.
- Finally used Send property to send Email directly without displaying. If you want you can use the Display property to see the email
- Now, go back to your sheet and run the Macro named SendMail02 as it is your current Macro.
- After that, you will see a message box saying the number of emails In this case, running the Macro will send email to 2 addresses. Click OK.
After that, the receivers will see the email in their Inboxes or Spam Boxes. Let me show you Inboxes of my 2 gmail accounts. You will also see the attached file of the downloadable link.
In the following picture, you will see the proof that [email protected] account receives the above email.
And also, [email protected] receives this email.
By following the above steps, you can send an email to multiple addresses without using Outlook.
Read More: Excel Macro: Send Email to an Address in Cell
3. Using FilePicker to Send Files Via Email without Outlook
If you want to share a file with someone via email, you can create a function in VBA for this purpose. Let’s follow the steps given below.
Steps:
- Follow Section 1 to open a VBA Module.
- Type the following code in the Module.
Function FileShare(Email_To As String, Email_Subject As String, Optional Email_CC As String, Optional Email_Body As String) As Boolean
On Error GoTo eh:
Dim Email_Msg As CDO.Message
Set Email_Msg = New CDO.Message
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
Email_Msg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "ZxcvbN0987"
Email_Msg.Configuration.Fields.Update
Dim Sending_File As String
Dim Dialog_File As FileDialog
Dim File_Item As Variant
Dim Dialog_Result As Long
Set Dialog_File = Application.FileDialog(msoFileDialogFilePicker)
Dialog_File.Filters.Add "Excel Files", "*.csv; *.xls; *.xlsx; *.xlsm"
Dialog_Result = Dialog_File.Show
If Dialog_Result = -1 Then
If Dialog_File.SelectedItems.Count > 0 Then
For Each File_Item In Dialog_File.SelectedItems
Sending_File = File_Item
Next File_Item
End If
End If
With Email_Msg
.Subject = "Necessary Excel File"
.From = "[email protected]"
.To = Email_To
.TextBody = Email_Body
.AddAttachment Sending_File
End With
Email_Msg.Send
FileShare = True
Exit Function
eh:
FileShare = False
End Function
Sub SendMail()
Dim Email_To As String
Dim Email_Subject As String
Dim Email_Body As String
Email_To = "[email protected]"
Email_Subject = "How to Make Daily Activity Report in Excel"
Email_Body = "I've attached the file you required."
If FileShare(Email_To, Email_Subject, , Email_Body) = True Then
MsgBox "Email sent successfully"
Else
MsgBox "Email is not created"
End If
End Sub
Code Breakdown
- First, we defined a function named FileShare. In this function, we declared Email_To, Email_Subject, Optional Email_CC, Optional Email_Body as String
- Then we declared Email_Msg ss Message as we want to send email without Outlook.
- After that, we set the configuration for the email.
- And then, we declared some other variables like Sending_File as String, Dialog_File as FileDialog, File_Item as Variant, Dialog_Result as Long. The Dialog File variable is set to determine which type of file we can share.
- Later, we used an IF Statement and For Loop so that we can manage how we send our email.
- After doing that, we defined necessary email
- We named our Sub Procedure as SendMail.
- Then after declaring the previous variables again, we used the FileShare function so that we can send the desired file through an email.
- Finally used Send property to send Email directly without displaying. If you want you can use the Display property to see the email
- Click on the Run icon.
- Run the Macro named Module2.SendMail because it is your current Macro.
- After that, you will see the Browse window to select the file from the location and click on Open.
- This operation will open a message box showing that “Email sent successfully”.
- Click OK.
- Now the receiver will find this email in his/her Inbox. Here, I’m showing the email from my Inbox so that you can understand how this code works. You can also see the file that I selected after running the code.
Thus you can send file via email using Excel VBA without Outlook.
Read More: Macro to Send Email from Excel
4. Sending Email with Attachments without Outlook
If you want to send a file without creating a function in VBA and also without the help of Outlook, you can use this code effectively. Let’s go through the process for better understanding.
Steps:
- Follow Section 1 to open a VBA Module.
- Type the following code in the Module.
Option Explicit
Sub SendMail03()
Dim Email_Obj As Object
Dim Email_Configuration As Object
Dim Mail_Configuration As Variant
Dim Email_Sub As String
Dim Message_From As String
Dim Message_To As String
Dim Email_Cc As String
Dim Email_Bcc As String
Dim Message_Body As String
Email_Sub = "Get your file"
Message_From = "[email protected]"
Message_To = "[email protected]"
Message_Body = "I've attached your required file here."
Set Email_Obj = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set Email_Configuration = CreateObject("CDO.Configuration")
Email_Configuration.Load -1
Set Mail_Configuration = Email_Configuration.Fields
With Mail_Configuration
.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") = "ZxcvbN0987"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With Email_Obj
Set .Configuration = Email_Configuration
End With
With Email_Obj
.Subject = Email_Sub
.From = Message_From
.To = Message_To
.TextBody = Message_Body
.AddAttachment ("C:\Users\DELL\Desktop\Nahian\Blog 45\how to make daily activity report in excel.xlsx")
.CC = Email_Cc
.BCC = Email_Bcc
.Send
End With
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
Code Breakdown
- First, we named our Sub Procedure SendMail03.
- Then we declared some variables Email_Obj and Email_Configuration as Object.
- After that, we again declare Mail_Configuration as Variant.
- We also declared Email_Sub, Message_From, Message_To, Email_Cc, Email_Bcc, Message_Body as String.
- Then we defined the Subject, Sender Email Address, Receiver Email Address and Text of the Email with the variables Email_Sub, Message_From, Message_To and Message_Body
- We will send the email without the help of Outlook. For that reason, we set Email_Obj to Message to create the object of the email.
- After that, we set the configuration of the email with a With Statement. We defined some items for the email. In those items, we included the location of the file too.
- Finally used Send property to send Email directly without displaying. If you want you can use the Display property to see the email.
- If you don’t know how to get the file location, follow the image below.
- Click on the top which is marked with a rectangle and type the name of your file exactly in the same way of the image. Then copy it and paste it in the VBA
- Run the code.
- Now the receiver will find this email in his/her Inbox. Here, I’m showing the email from my Inbox so that you can understand how this code works. You can also see the file that I selected after running the code.
Thus you can send a file via email without using VBA functions.
Read More: How to Apply Macro to Send Email from Excel with Attachment
Practice Section
Here, I’m giving you the dataset of this article so that you can make a dataset on your own and practice these methods.
Download Practice Workbook
Conclusion
Suffice it to say, that you will learn some effective ways to send email from Excel VBA without Outlook. This topic can save you a lot of time in the case where you need to send a scheduled email. If you have any other ideas or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- VBA to Generate Multiple Lines in Email Body in Excel
- How to Send Email from Excel with Body Using a Macro
- Print to PDF and Email Using VBA in Excel
- How to Use Excel VBA to Paste Range into Email Body
thank you for sharing these codes. As you might know, gmail has changed its security politic. When I try your codes, it sends : message was not sent to smtp server. Transport error code Ox80040217. Server response was not available. Would you have any solution please ? Best regards.
Hello sir, thank you for the feedback. We are working hard on this matter, I hope we will provide you the solution in the upcoming tutorial pretty soon.
Hello,
will this work from November 2022 when using Outlook 365?
I am referring to new authentification:
https://docs.microsoft.com/en-us/exchange/clients-and-mobile-in-exchange-online/deprecation-of-basic-authentication-exchange-online
and
https://techcommunity.microsoft.com/t5/exchange-team-blog/basic-authentication-and-exchange-online-september-2021-update/ba-p/2772210
regards
arno
Hi Cavallino, thanks for reaching out. The good news is, you can send emails by VBA using Outlook. Please check this article “How to Automatically Send Emails Based on Date“. You can also find similar articles on our website. Hope this will help you.
NAHIAN
I am using Excel (2007) and I have tried using the aforementioned code but in each case the code has failed to create an email.
Can you help me sort this out?
Hello sir, the process has been disabled since May 31. But to be sure, please check your Google account if you have the option ‘Less secure app access’ available. You can find this option from Manage Account >> Security >> Less secure app access. If you enable this option, then you can send Mails without Outlook.
How can I communicate with the author of this article. I keep getting “Transport failed to connect to server” errors
There is social media connection link in the introduction section about author. You can send message there.