[AccessD] Automated emails from Access requiring validation for each email

Heenan, Lambert Lambert.Heenan at chartisinsurance.com
Fri Sep 25 10:21:29 CDT 2009


What if there are lots of Exchange servers in an organization? Anyone got some code to reliably discover the SMTP server via code?

Lambert 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, September 25, 2009 9:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automated emails from Access requiring validation for each email

Hi Max,

Yes - I've used this for several years.  The manual that comes with the download is very good.  But you're right - it's for VB6.  So, I wrote similar code in an Access standard module and it works just fine.

1) Find out what the SMTP Server name is.  Ask you company or customer IT department, or your ISP.  My ISP is 'usintenet.com' - my SMTP Server Name is 'mail.usinternet.com' - I think this is typical but not universal.

2) Download the VBSendMail zipped file.  Register the two files vbSendMail.dll and mswinsck.ocx.  The manual has instructions on this.
Definitely review the manual.  In the downloaded files are two test screens
- they are easy to use to verify that you've set up your PC correctly.

3) Paste this code into a standard module.  Change the strings in the test procedure and run the test procedure.  I just now wrote and tested this procedure so it should work.  

Big Advantage - no annoying Outlook 'security' message.
Minor Disadvantage - no record of email being sent in Sent Items folder.

Note: Some SMTP Servers have a limit on the number of characters in the recipient's email address.  One of my customers has a limit of about 130 characters.

Good Luck!
Dan

------------------------------------------------------------------

Private Sub TestSendSMTPEmail()

    Call SendSMTPEmail("Test SMTP Email", "mail.domain.com", "First1
Last1;First2 Last2", "first1.last1 at domain.com;first2.last2 at domain.com", "My Name", "my.name at domain.com", "Message",
"FilePath1.doc;FilePath2.xls;FilePath3.pdf")
    
End Sub

Public Sub SendSMTPEmail(stgSubject As String, _
    stgSMTPServerName As String, _
    stgRecipientNames As String, _
    stgRecipientAddresses As String, _
    stgSenderName As String, _
    stgSenderAddress As String, _
    Optional stgMessage As String = "", _
    Optional stgAttachmentList As String = "")
    
    Dim poSendMail As Object
        
    '-- Send multiple emails - note that names must be separated by a semicolon (;) _
        and email addresses must also be separated by a semicolon (;)
    
    '-- Set up email parameters
    Set poSendMail = CreateObject("vbSendMail.clsSendMail")
    
    poSendMail.SMTPHost = stgSMTPServerName
    
    poSendMail.RecipientDisplayName = stgRecipientNames
    poSendMail.Recipient = stgRecipientAddresses
    
    poSendMail.FromDisplayName = stgSenderName
    poSendMail.FROM = stgSenderAddress
    poSendMail.ReplyToAddress = stgSenderAddress
    
    poSendMail.Subject = stgSubject
    
    If Not IsEmpty(stgMessage) And stgMessage <> "" Then
        poSendMail.message = stgMessage
    End If
    
    '-- Send multiple files by placing a ';' between the full path for each file
    If Not IsEmpty(stgAttachmentList) And stgAttachmentList <> "" Then
        poSendMail.Attachment = stgAttachmentList
    End If

    poSendMail.Connect
    poSendMail.Send
    poSendMail.Disconnect
    
End Sub

-------------------------------------------------------------------------


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Friday, September 25, 2009 6:53 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Automated emails from Access requiring validation for each email

Dan, will that work in Access.  The site says it is for VB6.

max


On 25/09/2009, Dan Waters <dwaters at usinternet.com> wrote:
> You can use an SMTP method which doesn't need Outlook.  I've used
vbSendMail
> for several years.
>
> http://www.freevbcode.com/ShowCode.asp?ID=109
>
> Good Luck!
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Gould
> Sent: Friday, September 25, 2009 2:09 AM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] Automated emails from Access requiring validation 
> for each email
>
> I have a module that sends emails to every student that meets certain 
> criteria. It cycles through the recordset and sends an email with an 
> attached report (RTF format) to the student. I am using 
> DoCmd.SendObject
and
> everything works perfectly except that someone has to sit at the 
> computer, wait until the email is about to be sent and then click on "Allow".
>
>
>
> Is there a way to safely automate the sending of the emails (we are 
> using Access 2007 and Outlook 2007) so they don't require a babysitter?
>
>
>
> TIA
>
>
>
> David Gould
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list