How to Send Mail from Excel VBA Script?
We found many people searching for VBA script to send SMTP email without using outlook or any other application. We have written a Macro and also shared the code below to send email from your itself.
Although most of the purposes of sending emails through Excel are solved with Mail Merge but it could be useful for sending plain emails in bulk with less efforts or could be used as a module in Excel VBA application for sending email. Download the Excel Mail sending Macro from here or use the written code in this page.
You may also use below written code. All you need to do is open VBA Editor using ALT + F11 (Press Fn key also if your keyboard has.) shortcut key, Click on insert and insert a module. Copy and paste the below code and change following values:
- Replace smtp.example.com with your server SMTP server address (E.g. smtp.gmail.com for Gmail Accounts, smtp.mail.yahoo.com for Yahoo Accounts)
- Replace 465 with your SMTP Server Port (E.g. 465 / 587 / 25). These ports could vary according to Encryption Type. Try using anyone of three.
- User Email ID should be your email id (E.g. email@example.com)
- Replace sender_password with your password
- Replace firstname.lastname@example.org with email ID to whom you want to send email and email@example.com should be same as we used earlier.
Note: Please make sure your account does not have Two Factor Authentication Enabled. Also if you are using Gmail account, "Allow less secure apps" in Sign in and secutiry settings should be ON.
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.example.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/sendusername") = "firstname.lastname@example.org"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "sender_password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
strbody = "Hey There," & vbNewLine & vbNewLine & _ "This is a test message from VBA Excel Mail sending Program. Thank you for using me." & vbNewLine & _
"Thank you" & vbNewLine & _
Set.Configuration = iConf
.To = "email@example.com"
.From = "firstname.lastname@example.org"
.Subject = "Mail Subject"
.TextBody = strbody
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
The above code could be used for sending bulk email by just listing a list of email accounts in excel and looping through it.