[AccessD] vbSendMail VBA Code (was: Outlook Warninig Message)

Dan Waters dwaters at usinternet.com
Tue Jun 6 19:55:39 CDT 2006


Hi Paul,

I tried the code below, but kept getting a 'User-Defined Type not Defined'
error on the Private WithEvents line.  Could you help with this?

Also, my email processing code is of course in a standard module.  From
reading Access help the WithEvents line needs to be in a Class module (or in
code behind a form).  

Thanks,
Dan 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Monday, June 05, 2006 3:31 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] vbSendMail VBA Code (was: Outlook Warninig Message)
Importance: High

I used the following, just a couple of events that vbSendMail has built in
to notify the user if the send of the email was successful or not....

Private WithEvents poSendmail As vbSendMail.clsSendMail     ' Used for the
vbSendMail funtion (email directly to SMTP server).
Private Sub cmdCancel_Click()
    Unload frmEmail
    End
End Sub
Private Sub cmdSend_Click()
    Set poSendmail = New clsSendMail
    
    poSendmail.PersistentSettings = False
    
    poSendmail.SMTPHost = Me.txtSMTP.Text
    
    poSendmail.Username = ""
    poSendmail.Password = ""
    poSendmail.POP3Host = ""
    poSendmail.UseAuthentication = False
    poSendmail.UsePopAuthentication = False
    
    poSendmail.From = Me.txtEmailFrom.Text
    poSendmail.FromDisplayName = Me.txtEmailFrom.Text
    poSendmail.Recipient = Me.txtEmailTo.Text
    poSendmail.ReplyToAddress = Me.txtEmailFrom.Text
    If (Me.txtSubject.Text <> "") Then
        poSendmail.Subject = Me.txtSubject.Text
    Else
        poSendmail.Subject = "Genesis"
    End If
    poSendmail.Message = Me.txtEmailBody.Text
    poSendmail.Send
    Set poSendmail = Nothing

    'MsgBox "Message has been sent............", vbInformation, "**** User
Message - Email Sent ****"
End Sub
Private Sub poSendmail_SendSuccesful()
    MsgBox "Email has been sent", vbInformation, "**** Email Sent ****"
End Sub
Private Sub poSendmail_SendFailed(Explanation As String)
    MsgBox "Email has NOT been sent for the following reason:" & vbCrLf &
vbCrLf & _
                 Explanation, vbInformation, "**** Email Failed ****"
End Sub





Message Received: Jun 05 2006, 08:01 AM
From: "Max Home" 
To: "'Access Developers discussion and problem solving'" 
Cc: 
Subject: Re: [AccessD] vbSendMail VBA Code (was: Outlook Warninig Message)

Ooo kaaaay, if I must... shame really, 'cos it is far easier to pick
somebody else's brains. 
Max
Ps. I will send it to you *when* I have done it - for your goodie bag.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: 04 June 2006 18:51
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] vbSendMail VBA Code (was: Outlook Warninig Message)

I'll agree with you - that was pretty lazy! ;-)

I've used this code for about 3 years, and I forgot that it was part of a VB
app. I use it in an Access application, and the code is in an Access
standard module. I use it so that emails get automatically sent from the
database as a result of an event. That way users don't need to take any
action to send it and don't even see it when it is sent! 

The code I sent was only a portion of what's available. You can go through
the vbSendMail app to see what else you could add. So, you might not need a
form at all.

All right - now get to work!

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Home
Sent: Sunday, June 04, 2006 11:56 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] vbSendMail VBA Code (was: Outlook Warninig Message)

Thanks Dan,
Actually I was being very lazy. As you know, the vb6 exe example displays a
"form" with lots of code behind the populated boxes. I was wondering if you
had done a similar form to save me having to reinvent the form and its
associated code.
Thanks
Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: 04 June 2006 17:02
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] vbSendMail VBA Code (was: Outlook Warninig Message)

Max - This is what I use within a standard module:

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

Public Function SendEmail(stgTo As String, stgEmailAddress As String,
stgSubject As String)

Dim poSendMail As Object

If stgTo = "" Or IsNull(stgTo) Then
Exit Function
End If

Set poSendMail = CreateObject("vbSendMail.clsSendMail")
poSendMail.SMTPHost = SMTPServerName
poSendMail.FromDisplayName = FullName(CurrentUser)
poSendMail.FROM = FullName(CurrentUser)
poSendMail.RecipientDisplayName = stgTo
poSendMail.Recipient = stgEmailAddress '-- Or use EmailAddress(stgTo)
poSendMail.ReplyToAddress = EmailAddress(CurrentUser)
poSendMail.Subject = stgSubject

'-- When email is originated from the developer's PC, don't actually
send email
'If CurrentPCName <> "DanWaters" Then
poSendMail.Connect
poSendMail.Send
poSendMail.Disconnect
'End If

Exit Function

End Function

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

SMTPServerName is a sub which looks up the SMTP Server Name from a table.
To send from your PC, you can try something like 'MAIL.USINTERNET.COM',
where USINTERNET is the domain name of your ISP. In a company environment,
you'll need to work with the IT folks to get the SMTP Server Name.

FullName and EmailAddress are subs which use CurrentUser. 

You can also send strings of multiple names and email addresses.

One thing to note: VBA Error Trapping will not pick up on vbSendMail
errors. To get that you'll need to write some WithEvents code, which I'm
not familiar with. Perhaps if you're familiar with VB6, that won't be a
problem. Writing this error trapping is described in the vbSendMail
Documentation file, which is pretty good reading. 

*** If anyone does write error trapping for vbSendMail, please post!! :-)

Let me know how this goes,
Dan


-----Original Message-----
Subject: Re: [AccessD] Outlook Warninig Message

Hi Dan,
This works well from outside of Access (using the example exe file). Do you
have an A3K version (this is VB6 as you know) and it would be nice to have
it within a DB.
Thanks
Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: 03 June 2006 19:23
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Outlook Warninig Message

Hi Rocky,

I use a program called vbSendMail. You can find it at
http://www.freevbcode.com/ShowCode.Asp?ID=109.

It's free, has great documentation, avoids email applications altogether,
and in 2 1/2 years I've had not one problem. It's also well documented.

Best of luck!
Dan 


-----Original Message-----
Subject: [AccessD] Outlook Warninig Message

When I try to do automation of access to Outlook, Outlook give the 
warning message:

"A program is trying to access e-mail addresses you have stored in
Outlook. Do you want to allow this?

You can then choose a number of minutes from a list box to allow the program
to access outlook.

For a program that's supposed to be running unattended, this won't do,
obviously. Is there a way to disable that message in Outlook?

MTIA

Rocky


-- 
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.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

-- 
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
-- 
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