​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking

Send an email via SMTP using excel/VBA


Below is some sample code to enable you to send an email via SMTP. You need to set up a reference to Microsoft CDO first.

Sub smtp_send()

ConstcdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.

ConstcdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).


ConstcdoAnonymous = 0 'Do not authenticate

ConstcdoBasic = 1 'basic (clear-text) authentication

ConstcdoNTLM = 2 'NTLM


Set objMessage = CreateObject("CDO.Message")

objMessage.Subject = "CDS Sheet Attached"

objMessage.From = "from@email.com"

objMessage.To = "to@email.com"

objMessage.TextBody = "Updated CDS Sheet Attached"


'==This section provides the configuration information for the remote SMTP server.


objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2


'Name or IP of Remote SMTP Server

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp_ip_address_goes_here"


'Type of authentication, NONE, Basic (Base64 encoded), NTLM

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic


'YourUserID on the SMTP server

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusername") = "from@email.com"


'Your password on the SMTP server

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your_email_password_goes_here"


'Server port (typically 25)

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25


'Use SSL for the connection (False or True)

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False


'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10




'==End remote SMTP server configuration section==



End Sub