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.