[AccessD] Automated emails from Access requiring validationfor each email

Fri Sep 25 12:51:16 CDT 2009

Possibly I can get educated here re SMTP. 

I use CDO via Access to send email to eliminate the Outlook issues. The
issue I have found is that most non-business plans through an ISP block the
regularly used SMTP ports. Mine does. The only way I have been able to get
around this is to use the GMAIL SMTP server on their secured ports which
give you access with your Gmail credentials. The risk here is that if you
send many messages at one time, less than 100 in my case, and have bounces
or rejections Gmail may shut down your account.

How do list users handle situations where you don't have access to corporate
servers? 

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, September 25, 2009 6:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automated emails from Access requiring validationfor
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