[AccessD] Email using SMTP

Now if you wanted to get really fancy;)
Here is how to execute a vbs script with CDO that sends an email but it 
is executed from within an SQL Stored Procedure
that you could call from Access

http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp

Barbara Ryan wrote:

>Thanks, Steve and Marty....  It's nice to know that I have several options.
>I learn something new every day......Barb
>
>
>----- Original Message ----- 
>From: "MartyConnelly" <martyconnelly at shaw.ca>
>To: "Access Developers discussion and problem solving"
><accessd at databaseadvisors.com>
>Sent: Monday, January 30, 2006 4:10 PM
>Subject: Re: [AccessD] Email using SMTP
>
>
>  
>
>>Here is another method of using CDO similar to Steves but doesn't
>>require a userid password.
>>Requires WinXP or Win2000
>>This method may only work inside a domain ie you have a online node via
>>a cable modem
>>but not on a dial up.
>>
>>
>>'The example code is using CDOSYS (CDO for Windows 2000 or XP).
>>'I dont think I would want to go back to CDONTS for earlier systems
>>'It does not depend on MAPI or CDO or Outlook
>>'It does not use your mailbox to send email.
>>' So you can send mail without a mail program or mail account
>>' This code builds the message and drops it into a pickup directory,
>>' and the SMTP service running on the machine
>>' picks it up and send it out to the internet.
>>
>>'So why use CDO code instead of Outlook automation or
>>Application.SendMail in VBA.
>>' It doesn't matter what Mail program you are using (It uses the SMTP
>>server).
>>' It doesn't matter what Office version you are using.
>>' supposedly you can send an object or file in the body of the mail
>>(some mail programs cant do this)
>>' haven't verified this
>>' You can send any file attachment you like.
>>' No Outlook Security warning so no need for Redemption
>>
>>' You probably wont have your mail server full expanded smtp address
>>'If you go into netscape mail or outlook and look for the smtp name
>>'It will look like mine, "shawmail" or "shawnews" this dns resolves
>>'to "shawmail.cg.shawcable.net" CDO doesn't resolve this short name so
>>'The quickest way to get this actual address without using registry et al.
>>'is run cmd and ping "shawmail" to return full qualified smtp address.
>>'This code wont run exactly unless you are on cable and signed on in the
>>Shaw domain
>>
>>Sub SendCDO()
>>' This example use late binding of CDOSys, you don't have to set a
>>    
>>
>reference
>  
>
>>' You must be online to net when you run the sub
>>' You must be running WinXP or Win2000
>>Dim cdoMessage As Object
>>Dim objCDOMail As Object
>>Dim strschema As String
>>On Error GoTo ErrorHandler ' Enable error-handling routine.
>>'
>>Set cdoMessage = CreateObject("CDO.Message")
>>Set objCDOMail = CreateObject("CDO.Configuration")
>>strschema = "http://schemas.microsoft.com/cdo/configuration/"
>>objCDOMail.Load -1 ' CDO Source Default
>>'If you have illegal or wrong smtp address here it will run for 30-60
>>seconds and finally give transport error
>>With objCDOMail.Fields
>>.Item(strschema & "sendusing") = 2 ' cdoSendUsingPort
>>.Item(strschema & "smtpserver") = "shawmail.cg.shawcable.net" ' "Your
>>SMTP server address here"
>>.Item(strschema & "smtpserverport") = 25 'specify port number
>>.Update
>>End With
>>
>>With cdoMessage
>>Set .Configuration = objCDOMail
>>.to = "martyconnelly at gmail.com"
>>.From = "Winnie The Pooh <martyconnelly at shaw.ca>"
>>.CC = ""
>>.BCC = ""
>>.Subject = "This is another test from marty"
>>.TextBody = "This is the text in the body just cdo defaults"
>>.AddAttachment "C:\temp2\rptSampleCount.rtf"
>>.AddAttachment "C:\temp2\frontimage.jpeg"
>>.send
>>End With
>>
>>Set cdoMessage = Nothing
>>Set objCDOMail = Nothing
>>Exit Sub ' Exit to avoid handler.
>>ErrorHandler: ' Error-handling routine.
>>Debug.Print Err.Number & "-" & Err.Description
>>Set cdoMessage = Nothing
>>Set objCDOMail = Nothing
>>Exit Sub
>>End Sub
>>
>>
>>
>>Barbara Ryan wrote:
>>
>>    
>>
>>>I've found some SMTP components on the web that allow you to email
>>>      
>>>
>directly from Access using SMTP.
>  
>
>>>Does anyone know of any VBA code (e.g., using SMTP "commands") that I
>>>      
>>>
>could use WITHOUT using a component (as well as NOT using any electronic
>mail application)
>  
>
>>>Thanks,
>>>Barb Ryan
>>>
>>>
>>>      
>>>
>>-- 
>>Marty Connelly
>>Victoria, B.C.
>>Canada
>>
>>
>>    
>>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada