Sending email from Excel
March 7th, 2007Ever needed or wanted to send an email from Excel? This sample workbook demonstrates how to send emails directly from Excel.
There's a screenshot and code snip below. You can download the sample workbook here .
Private Const NAMESPACE = "http://schemas.microsoft.com/cdo/configuration/"
Private Const cdoBasic = 1
Private Const cdoSendUsingPort = 2
Sub Button1_Click()
Dim objCDOMessage As Object
Dim objCDOConfig As Object
Set objCDOMessage = CreateObject("CDO.Message")
Set objCDOConfig = CreateObject("CDO.Configuration")
objCDOConfig.Fields(NAMESPACE & "sendusing") = cdoSendUsingPort
objCDOConfig.Fields(NAMESPACE & "smtpserver") = "your.smtp.server"
objCDOConfig.Fields(NAMESPACE & "smtpserverport") = 587
'You need these lines if your smtp server requires authentication
objCDOConfig.Fields(NAMESPACE & "sendusername") = "your_username"
objCDOConfig.Fields(NAMESPACE & "sendpassword") = "your_password"
objCDOConfig.Fields(NAMESPACE & "smtpauthenticate") = cdoBasic
objCDOConfig.Fields.Update
Set objCDOMessage.Configuration = objCDOConfig
objCDOMessage.TextBody = "Dear " & Sheet1.Range("B2").Text & "," & vbNewLine & vbNewLine
objCDOMessage.TextBody = objCDOMessage.TextBody & "Thank you for your order. Your order number is " & Sheet1.Range("D2").Text
objCDOMessage.From = "your_email@somewhere.com"
objCDOMessage.Subject = "Test email"
objCDOMessage.To = Sheet1.Range("C2").Text
objCDOMessage.Send
End Sub
2 Comments »
RSS feed for comments on this post. TrackBack URI
Leave a comment
(C) by Virgilio Adriano. All rights reserved. Powered by WordPress.
Entries and comments feeds.
It took 0.424 seconds to load this page.
Absolutely love the potential of this tool…I downloaded your sample workbook successfully. But when I clicked the “Send Email” Button, got error message:
“the transport failed to connect to the server”
Any idea what I’m doing wrong?
Comment by Claude Brogunier — August 29, 2007 #
Hi Claude. In the VBA code, you need to provide your smtp server, port and also the user name and password. Those are settings you would typically provide to your email client when setting up your email. Look in the code for any text that starts with “your” and replace those values ith appropriate values. Thanks!
Comment by Vergel — August 29, 2007 #