You may need to automate email sending using VBA scripts sometimes. Yes, it’s possible and below we are sharing the code as well as a downloadable Excel Macro to send an email.
Although most of the purposes of sending emails through Excel could be solved with Mail Merge, but it could be useful for sending plain emails in bulk with less effort. It could be used as a module in the Excel VBA application for sending an email. Download the Excel Mail sending Macro from here or use the written code on this page below.
You may also use the 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. E.g. Use ‘smtpusetls’ in case of TLS encryption and port no. 587. Set smtpusessl = False and port no. 25 in case of no encryption.
- The user Email ID should be your email id (E.g. [email protected])
- Replace sender_password with your password
- Replace [email protected] with the email ID to whom you want to send the email and [email protected] should be the same as we used earlier.
Note: Please make sure your account does not have Two Factor Authentication Enabled. In case Two Factor Authentication is enabled, you can create application-specific password.
Sub CDO_Mail_Small_Text()
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
With Flds
.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") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "sender_password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Update
End With
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 & _
"Regards"
With iMsg
Set.Configuration = iConf
.To = "[email protected]"
.From = "[email protected]"
.Subject = "Mail Subject"
.TextBody = strbody
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
End Sub
The above code could be used for sending bulk emails by just listing a list of email accounts in excel and looping through it.
Priya Bansal is a seasoned education writer with a passion for creating insightful and engaging content. With a background in education and a knack for research, she excels at producing articles, blogs, and resources that inform and inspire. Priya’s work contributes to the enrichment of educational discourse and empowers learners of all ages.
