UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> SMTP to send email question    
 
   
rth
post Jan 14 2007, 09:18 PM
Post#1



Posts: 248
Joined: 7-February 05



Is it possible within access to send email through use of an SMTP server? The idea is to bypass the outlook security and be able to send bulk emails out to each member of our organization. I've got a piece of code done in VB6 that works wonderfully for this task but I have to use the shell command to run it and I'd rather stay solely within Access if possible.
I'm posting the VB6 code in case someone else can benefit from it, but my real question is, can this code be modified to work directly from within Access?
CODE

Private Sub Form_Activate()
Dim strSQL As String
        Dim strSQLCount As String
        Dim strUpdateSQL As String
        Dim strConn As String
        Dim objDBConn As New ADODB.Connection
        Dim rsEmails As New ADODB.Recordset
        Dim rsEmailCount As New ADODB.Recordset
        strSQL = "SELECT * FROM emails"
        strSQLCount = "SELECT COUNT(email_id) from emails"
        
        'set the path to the database
        strConn = "driver={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\emails_db.mdb"
        objDBConn.Open (strConn)
        
        rsEmails.ActiveConnection = objDBConn
        rsEmails.Source = strSQL
        rsEmails.Open
        
        rsEmailCount.ActiveConnection = objDBConn
        rsEmailCount.Source = strSQLCount
        rsEmailCount.Open
        prgEmail.Max = rsEmailCount(0).Value
        Dim myMessage As New CDO.Message
        Dim i As Integer
        
        'set smtp server settings
        myMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        myMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.charter.net"
        myMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        myMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0
        myMessage.Configuration.Fields.Update
        i = 0
        Do While Not rsEmails.EOF
            myMessage.To = rsEmails("email_addr").Value
            myMessage.HTMLBody = rsEmails("body").Value
            myMessage.Subject = rsEmails("subject").Value
            myMessage.From = "me@myserver.net"
            i = i + 1
            lblStatus.Caption = "Sending Email " & i & " of " & prgEmail.Max
            prgEmail.Value = i
    
            myMessage.Send
            strUpdateSQL = "UPDATE emails SET sent=yes WHERE email_id=" & rsEmails("email_id").Value
            objDBConn.Execute (strUpdateSQL)
            rsEmails.MoveNext
        Loop
        lblStatus.Caption = "Sending Email Completed"
        rsEmails.Close
        objDBConn.Close
    
        Set myMessage = Nothing
        Set rsEmails = Nothing
        Set objDBConn = Nothing
End Sub
Go to the top of the page
 
HiTechCoach
post Jan 14 2007, 09:32 PM
Post#2


UtterAccess VIP
Posts: 18,993
Joined: 29-September 03
From: Oklahoma City, Oklahoma


It should be possible to convert to VBA.
Have you tried it in Access/VBA?
Oalso avoid Outlook by using this: vbSendMail.dll Version 3.65-- Easy E-mail Sending in VB, with Attachments
I use it with VB and Access/VBAand the example was easy to convert to VBA.
Go to the top of the page
 
schroep
post Jan 14 2007, 09:42 PM
Post#3


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


Here's a procedure that should work without requiring you to set any references (works in VBScript, as well):
CODE
' SEND EMAIL VIA SMTP USING CDOEX/CDOSYS
Sub prcSendMail(pstrFrom,pstrTo,pstrCC,pstrBCC,pstrSubject,pstrBody)
  Const cstrSMTPServer = "NAMEOFYOURSMTPSERVER"
  Const cintCDOSendUsingPort = 2
  Dim objConfig, objMsg
nbsp; If (Len(pstrFrom & "") > 0) And (Len(pstrTo & "") > 0) And (Len(pstrSubject & "") > 0) And (Len(pstrBody & "") > 0) Then
    Set objConfig = CreateObject("CDO.Configuration")
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsingPort
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer
    objConfig.Fields.Update
    Set objMsg = CreateObject("CDO.Message")
    Set objMsg.Configuration = objConfig
    objMsg.From = pstrFrom
    objMsg.To = pstrTo
    If Len(pstrCC & "") > 0 Then objMsg.CC = pstrCC
    If Len(pstrBCC & "") > 0 Then objMsg.BCC = pstrBCC
    objMsg.Subject = pstrSubject
    objMsg.HTMLBody = pstrBody
    objMsg.Send
    Set objMsg = Nothing
    Set objConfig = Nothing
  End If
End Sub

You can also set a reference to the MICROSOFT CDO FOR EXCHANGE 2000 LIBRARY and use early binding:
CODE
Public Sub prcSendMail(pstrFrom As String, pstrRecipient As String, pstrSubject As String, pstrBody As String, Optional pstrAttachPath As String = "", Optional pstrCC As String = "", Optional pstrBCC As String = "") ' Send email using CDOEX/CDOSYS
  Const conCDOSendUsingPort As Integer = 2
  Const conCDOSMTPServer As String = "NAMEOFYOURSMTPSERVER"
  Dim objCDOConfig As CDO.Configuration
  Dim objCDOMessage As CDO.Message
  Set objCDOConfig = New CDO.Configuration
  objCDOConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = conCDOSendUsingPort
  objCDOConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = conCDOSMTPServer
  objCDOConfig.Fields.Update
  Set objCDOMessage = New CDO.Message
  Set objCDOMessage.Configuration = objCDOConfig
  objCDOMessage.AutoGenerateTextBody = True
  objCDOMessage.From = pstrFrom       ' The address listed as "From"
  objCDOMessage.To = pstrRecipient    ' The address listed as "To"
  objCDOMessage.Subject = pstrSubject ' Specify the subject header
  objCDOMessage.HTMLBody = pstrBody   ' Specify the body content
  If pstrAttachPath <> "" Then objCDOMessage.AddAttachment pstrAttachPath ' Attach specified file, if any
  If pstrCC <> "" Then objCDOMessage.CC = pstrCC
  If pstrBCC <> "" Then objCDOMessage.BCC = pstrBCC
  objCDOMessage.Send
  Set objCDOMessage = Nothing
  Set objCDOConfig = Nothing
End Sub

You should be able to convert either of these to your purposes. You should also be able to easily convert your VB6 example; you probably need to set the reference as in the above example, and may need to make a few simple code tweaks.
Go to the top of the page
 
cheekybuddha
post Jan 15 2007, 03:48 AM
Post#4


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Hi Peter,
Good to 'see' you!
Please can you explain the following lines for me:
CODE
    Set objConfig = CreateObject("CDO.Configuration")
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsingPort
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer

specifically the url to 'schemas...'
Thanks,
d
Go to the top of the page
 
schroep
post Jan 15 2007, 10:56 AM
Post#5


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


It looks a little weird, but that was how Microsoft chose to do the configuration for this particular ActiveX object. In reality, those aren't URL's, just item names.
See if this Microsoft page helps to explain it better.
Go to the top of the page
 
rth
post Jan 15 2007, 11:48 AM
Post#6



Posts: 248
Joined: 7-February 05



Thanks for the help Peter.
This part of the code does not seem to work though...
Const cstrSMTPServer = "smtp.charter.net"
objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer
It gives an error stating the cstrSMTPServer must be a constant. If I do away with the variable and substitute the "smtp.charter.net" it works fine. Do you happen to see anything that would cause this to fail??
Again thanks for the help
Go to the top of the page
 
cheekybuddha
post Jan 15 2007, 12:06 PM
Post#7


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Hi Peter,
Yes, I looked it up in the meanwhile - as you say, it's just an odd way to name a collection item.
Thanks,
d
Go to the top of the page
 
schroep
post Jan 15 2007, 05:56 PM
Post#8


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


My first sample was from VBScript, which doesn't allow typing of variables/constants. Might be necessary to specify type in VBA, as in my second example:
Const cstrSMTPServer As String = "smtp.charter.net"
Const cintCDOSendUsingPort As Integer = 2
Go to the top of the page
 
rth
post Jan 17 2007, 01:23 PM
Post#9



Posts: 248
Joined: 7-February 05



Thanks Peter
Go to the top of the page
 
schroep
post Jan 17 2007, 09:04 PM
Post#10


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


Hope to have helped.
Go to the top of the page
 
databasedevelope...
post Feb 10 2007, 03:11 PM
Post#11



Posts: 241
Joined: 2-May 06



This code from PaulSadowski.com works very well. It was very easy to setup and I simply changed the cdoBasic to cdoNone since my outgoing SMTP I believe only checks to make sure that the From @domain.com address matches the SMTP domain. A larger article on this code can be reviewed on this page: http://www.paulsadowski.com/WSH/cdo.htm
onst cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).
Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" <me@my.com>"
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication."
'==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") = "mail.your.com"
'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid"
'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"
'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") = 60
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
Go to the top of the page
 
rjAccDB
post Feb 11 2007, 07:39 AM
Post#12



Posts: 909
Joined: 16-December 06
From: Phil


Hi Peter,
Can you please explain in a most basic words how can I use your first procedure to send email within my Access DB?
Say, I have the controls; From, Email_Addr, Subject, BodyText and Attachment.
Is the above procedure should be on the onclick of a button?
Many thanks for your assistance,
rj
Go to the top of the page
 
schroep
post Feb 11 2007, 11:29 AM
Post#13


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


You would copy the code for the procedure into your form's code module; not attached to any event, just copy the code in at the bottom. This now becomes a procedure you can call anywhere in your form.
You would then, in the OnClick event of your SEND button, use the following line of code:
Call prcSendMail(txtFrom,txtEmail_Addr,"","",txtSubject,txtBody)
If you need to handle attachments, the second version of that procedure handled that; copy it in to your form's code module, and then you would need to (from within the code editor window) select TOOLS/REFERENCES, scroll down to find the MICROSOFT CDO FOR EXCHANGE 2000 LIBRARY entry and put a check mark next to it.
Your OnClick code would look like:
Call prcSendMail(txtFrom, txtEmail_Addr, txtSubject, txtBody, txtAttachPath)
Go to the top of the page
 
rjAccDB
post Feb 12 2007, 12:42 AM
Post#14



Posts: 909
Joined: 16-December 06
From: Phil


Peter,
Thank you for your kind response, I will go on trial and error.
rj
Go to the top of the page
 
tormod
post Jul 5 2009, 09:16 PM
Post#15



Posts: 60
Joined: 22-October 03



@Peter (or anyone else wink.gif )
Could you please upload a easy example db (like with one form and one button or something) showing how this code is used?
Ocan't code, and this time my "cut/paste" magic didn't work.. wink.gif
Best regards
Tormod
Go to the top of the page
 
Saba
post Oct 28 2009, 11:53 AM
Post#16



Posts: 10
Joined: 12-April 04



I'm using Access 2000 and VB6. I'm trying to find a simple way to send an email with an attachment using smtp within vb of Access. I was all excited when I read the above however, in my tools/references I don't have MICROSOFT CDO FOR EXCHANGE 2000 LIBRARY. Your help would be very much appreciated.
Thanks!
Saba
Go to the top of the page
 
Pleasure
post Oct 28 2009, 02:50 PM
Post#17



Posts: 257
Joined: 1-July 05
From: Greece


SABA:

Try using the reference:
Microsoft CDO for Windows 2000 Library

This works fine as well ...

PETER ... PETER ... PETER ... PETER

Odesperately need your help

I tried to convert the 2nd version (that can send attachements) to late binding, like the 1st version (without attachements).

So the code is :

CODE
Public Sub prcSendMail(pstrFrom As String, pstrTo As String, pstrSubject As String, pstrBody As String, Optional pstrAttachPath As String = "", Optional pstrCC As String = "", Optional pstrBCC As String = "")
nbsp; Const cstrSMTPServer = "smtp.otenet.gr"
  Const cintCDOSendUsingPort = 2
  Dim objConfig, objMsg
  If (Len(pstrFrom & "") > 0) And (Len(pstrTo & "") > 0) And (Len(pstrSubject & "") > 0) And (Len(pstrBody & "") > 0) Then
    Set objConfig = CreateObject("CDO.Configuration")
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsingPort
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer
    objConfig.Fields.Update
    
    Set objMsg = CreateObject("CDO.Message")
    Set objMsg.Configuration = objConfig
    
    objMsg.From = pstrFrom
    objMsg.To = pstrTo
    
    If Len(pstrCC & "") > 0 Then objMsg.CC = pstrCC
    If Len(pstrBCC & "") > 0 Then objMsg.BCC = pstrBCC
    If pstrAttachPath <> "" Then objMsg.AddAttachment = pstrAttachPath ' Attach specified file, if any
    
    objMsg.Subject = pstrSubject
    objMsg.HTMLBody = pstrBody
    objMsg.Send
    Set objMsg = Nothing
    Set objConfig = Nothing
    
  End If
End Sub


So I get a RunTime Error 438 - Object doesn't support this property or method at the
CODE
objMsg.AddAttachment = pstrAttachPath


Anything in mind ? Is it possible to use attachment without the need to add a reference of the CDO


Edited by: Pleasure on Wed Oct 28 15:51:41 EDT 2009.
Go to the top of the page
 
Pleasure
post Oct 28 2009, 02:57 PM
Post#18



Posts: 257
Joined: 1-July 05
From: Greece


hey ...
ound it.
Instead of
CODE
objMsg.AddAttachment = pstrAttachPath

we have to use
CODE
objMsg.AddAttachment  pstrAttachPath

Now everything works without the need to set any reference ...
Go to the top of the page
 
zekebar
post Dec 18 2009, 09:51 AM
Post#19



Posts: 123
Joined: 25-September 04



Is this code compatable with Access 2.0, Does anyone know. I copied and pasted the code, however when I try to compile the code it gives me an error on the first period in the line of code below that states object is not defined.
bjConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsingPort
Thank You
Zeke Barlow
Go to the top of the page
 
post Apr 13 2011, 09:52 AM
Post#20



Posts: 0
Joined: --



Wow just came across this.
Thanks for the clarification!
I'm looking and looking at the code and I say to myself; gee these look like they should be indexes of some sort ... and guess what that's what they appear to be. Could it be any more confusing? Thanks Microcheese.
Is there no end to the lunacy of this (3,642 graphic explicatives / unflattering adjectives / unsavory comments hidden) company?
A modified Spock-ish quote: I have been... and always shall be mystified at how Microslop became the largest software provider in the world.
If you add up all of the seconds into one giant collection that I have spent: reinstalling, resetting a setting that got unset, wading through endless pages of help, forums, news groups and web pages looking for fixes and work arounds for solutions for problems that an intelligent software design company would not have, recovering from BSOD's, and 1000 other tedious things it adds up to a total of more than three entire months since they introduced DOS v1.01 on the IMB PC.
When I develop in the MS world, I spent more than 50% of the development time fighting the fight. LOL
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 09:08 AM