Send Email from Excel VBA without Outlook: 4 Suitable Examples

Method 1 – Sending Single Email from Gmail Account Using Excel VBA 

Steps:

• Go to Developer >> Visual Basic 

• The VBA window will open. Select Insert >> Module

• Select Tools >> References

• Check Microsoft CDO for Windows 2000 Library. 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

After running this code, the message “Heads up, enemy UAV spotted” will be sent to the recipient’s email address.

That’s how we create the outlet of this VBA code to send mail without Outlook.

• Run the Macro by clicking 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.

Method 2 – Send Multiple Emails from Excel VBA without Outlook

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

• Go back to your sheet and run the Macro named SendMail02 as it is your current Macro.

• You will see a message box saying the number of emails, running the Macro will send email to 2 addresses. Click OK.

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.

You will see the proof that [email protected] account receives the above email.

Also, [email protected] receives this email.

By following the above steps, you can send an email to multiple addresses without using Outlook.

Method 3 – Using FilePicker to Send Files Via Email without Outlook

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

• Click the Run icon.
• Run the Macro named Module2.SendMail because it is your current Macro.

• 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.

• The receiver will find this email in his/her Inbox. 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.

Method 4 – Sending Email with Attachments without Outlook

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

• 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. Copy it and paste it in the VBA

• Run the code.

• 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. See the file that I selected after running the code.

Thus you can send a file via email without using VBA functions.

Download Practice Workbook

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