Two Problems

[YoungManRiver]

All,

Have two problems which are:

1. Need a good example of sending email from within Access.
want to use config for the email I enter into a table
for this so export of Data into Zipped Excel file can
be MIME encrypted and sent to corporate location,

2. Problem with "Afterupdate" event on drop-down, in a
non-modal POPup, where the subroutine called in the
class module, gets re-assigned to all objects (buttons,
fields, labels) so none of the buttons on the screen
function any longer, even the "RETURN", as the "GET"
subroutine in the class module is always called even
when using the "X" form close button to finally get out
of the screen.

If needed I will find place/space to put up both form and class module code in question.

YMR

 

[jrbarnett]

For point 1, see:

Send email from an Access Database: thread702-396121

How to send email from Access: thread705-1165838

How to send email from Access with attachment from Mozilla: thread705-1311726

For point 2: we can't really help you without further information (table structure, existing code etc). Can you post the code here, including details of what you have tried so far and we will try to help you.

John

 

[MajP]

Please restate number 2. I have absolutely no idea what you are asking.

 

[YoungManRiver]

Restate of # 2 as requested:

2. Problem with "Afterupdate" event on drop-down, in a
non-modal POPup, where the subroutine called in the
class module, gets re-assigned to all objects (buttons,
fields, labels) so none of the buttons on the screen
function any longer, even the "RETURN", as the "GET"
subroutine in the class module is always called even
when using the "X" form close button to finally get out
of the screen.

OK,

So I have two screens with the following properties/qualities:

A. Screen One - is a selection driven screen and need to add new values to the drop-down combo-box fields in it. So I call a single new POPup screen, with parms to let the POPup know which field is needing an update.

B. Screen Two - a non-modal POPup screen, called from screen 1 with passed parms allowing the labels and processing to conform to the field a value will be added to on screen 1,

My problem is with screen 2. Screen 2 has the following properties/fields/objects:
a. {cboxFL1} combo-box,
b. {tboxFL1} text-box,
c. {tboxFL2} text-box,
d. {tboxFL3} text-box,
e. {tboxFL4} text-box,
f. {labTIT} label - Title,
g. {labMOD} label - Mode,
h. {labFL1} label - field1,
i. {labFL2} label - field2,
j. {labFL3} label - field3,
k. {labFL4} label - field4,
l. {btnDEL} button - Delete,
m. {btnEDT} button - Mode,
n. {btnRET} button - Return,
o. {btnSAV} button - Save,

Button "btnEDT" toggles the screen between the "ADD" and "EDIT" modes which changes whether "tboxFL1" or "cboxFL1" is visible and processable. When the "EDIT" mode is "ON" and "cboxFL1" is viewable and a record is selected ie "event=AfterUpdate" then the processing it calls runs a query and propgates all the fields (including non-visible "tboxFL1") from the table in question, which is changed by which parm was passed.

Example:

Screen 1 is a User Admin screen where two buttons on Screen 1 are labeled:

"E Type Mgmt."
"Priv Mgmt."

Which are managing the addition, edit, delete of a.) Employee Types, and b.) Login Privileges. Obviously the first query is against a table named "e_type" and the second is against a table named "group".

Now as I stated before the problem occurs when the "AfterUpdate" event runs due to selection of a value in the {cboxFL1} field. Code for this button is:

Private Sub cboxFL1_AfterUpdate()
    Dim SCRNfields As clsENTsetup
    Set SCRNfields = New clsENTsetup
    Set SCRNfields.Sourceform = Form_sfmDATusr
    Set SCRNfields.Targetform = Me
    SCRNfields.MAN_get
End Sub

Which you see calls the subrountine "MAN_get" in the class module clsENTsetup. Sourceform and Targetform are used to keep track of the calling and processing forms, so fields will process in the class module correctly.

Whatever is going on, causes every control on the form (buttons, fields, etc.) to now call this "MAN_get" subroutine, so that no other controls on the form now have any action or effect.

Uploaded .zip file with screenshots and .cls VBA code at:

http://www.pctools.com/forum/showthread.php?p=184582#post184582

Hope this restate clears it up.

OMR

 

[YoungManRiver]

Y'all with the EMail links!

Thanks!

Will steer away from the OUTLOOK solutions though.

Want it to always work and not every PC has OUTLOOK.

Thanks again!

YMR

 

[MajP]

I can not get your code to download, I think your reaching if you think someone is going to register to a site in order to download your code. Find a site that does not require people to register.

My guess. Your class is trapping the events for all the controls on the form. Does the class run through the controls collection and assign "with events" to the controls?

 

[YoungManRiver]

MajP,

OK will load elsewhere, but it is .zip file.

YMR

 

[YoungManRiver]

All,

OK uploaded the .zip file to:

http://www.pctools.com/forum/showthread.php?t=51785

and

http://www.techsupportforum.com/mic...ffice-support/251804-two-access-problems.html

Neither require a login to see the .zip attachment, but I think cookies must be "ON".

If one doesn't work try the other.

YMR

 

[jrbarnett]

To send an email in a way that doesn't use Outlook, you can use SMTP methods - see thread181-1246083

If you don't want to use Outlook, your only alternative is to use MAPI - look up DoCmd.SendObject in the VBA help.

John

 

[YoungManRiver]

John,

Thanks! Building a module for that stuff now!

Let you know how it works!

YMR

 

[YoungManRiver]

All,

Here is what I built:

Public Sub Send_EMail()
    Const 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
    Dim iConfig As New CDO.Configuration
    Dim Flds As ADODB.Fields
    iConfig.Load cdoSourceIIS
    Set Flds = iConfig.Fields
    With Flds
        Set objMessage = CreateObject("CDO.Message")
        objMessage.Configuration.Fields.CdoConfiguration.cdoSendUsingMethod = 2
        objMessage.Configuration.Fields.CdoConfiguration.cdoSMTPServer = "smtp.YourSMTP.com"
        objMessage.Configuration.Fields.CdoConfiguration.cdoSMTPAuthenticate = cdoBasic
        objMessage.Configuration.Fields.CdoConfiguration.cdoSendUserName = "YourSMTPAddress@blah.com"
        objMessage.Configuration.Fields.CdoConfiguration.cdoSendPassword = "YourPassword"
        objMessage.Configuration.Fields.CdoConfiguration.cdoSMTPServerPort = 25
        objMessage.Configuration.Fields.CdoConfiguration.cdoSMTPUseSSL = False
        objMessage.Configuration.Fields.CdoConfiguration.cdoSMTPConnectionTimeout = 60
        objMessage.Configuration.Fields.Update
        objMessage.Subject = "Example CDO Message"
        objMessage.From = "bla@bla.com"
        objMessage.To = "blo@blo.com"
        objMessage.TextBody = "This is some sample message text."
        objMessage.AddAttachment "c:\Test.pdf"
        objMessage.Send
    End With
End Sub

Then I got to reading that this is a IIS and/or needs IIS to work, which is not available where this application deploys, so that said, am I back to square one????

YMR

 

[YoungManRiver]

All,

Found this solution that does not need IIS or Outlook.

Public Sub Send_EMail()
    Dim db, qry2, ToAddr, Subjct, SndFil, Query2
    Set db = CurrentDb()
    Query2 = "SELECT * FROM tblDATeml;"
    Set qry2 = db.OpenRecordset(Query2)
    With qry2
        .MoveFirst
        While Not .EOF
            ToAddr = ![eml_tad]
            Subjct = ![eml_sbj]
            SndFil = ![eml_sfl]
            DoCmd.SendObject acQuery, SndFil, acFormatXLS, ToAddr, , , Subjct, , False
            .MoveNext
        Wend
        .Close
    End With
    gry2 = Nothing
    db = Nothing
End Sub

Still testing the code.

YMR

 

[YoungManRiver]

No comments at this point, must be good! Been too busy on other code to test just yet, but will tonight.

YMR