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


elmama
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

 

objMessage.Configuration.Fields.Update

 

'==End remote SMTP server configuration section==

 

objMessage.Send

End Sub