E-mail command button

[dcc15 via AccessMonster.com]

I have a Form with a send message cmdbutton that I have been trying to
automate by adding some code (am new to VBA and learning). I understand some
of what I have done but not all...
When the cmdButton is clicked a "Object Required" MA Access message pops up.
I have been over the code and can't see where or which line(s) might be wrong.
Any help or suggestions would be appreciated.
CODE:
Private Sub cmdSendNotification_Click()
On Error GoTo Err_cmdSendNotification_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim Text As String '-- E-mail text
Dim OpenDate As Variant '-- Rec date for e-mail text
Dim Subject As String '-- Subject line of e-mail
Dim Number As String '-- The NCMR number from form
Dim stWho As String '-- Reference to TBLUsers
Dim OpenBy As String '-- User who opened NCMR
Dim SQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Name/Group to send notification to
stWho = Me.ncmrSentTo
stWhere = TBLUsers.User = stWho
'-- Looks up email address from TblUsers
varTo = DLookup("", "TBLUsers", stWhere)

Subject = "!!New NCMR Problem!!"

Number = Me.ncmrnum
OpenDate = Me.ncmrdateopen
'-- User who opened NCMR
OpenBy = Me.ncmrby

Text = "A new NCMR has been reported. " & Chr$(13) & Chr$(13) & _
"NCMR Number: " & Number & Chr$(13) & _
"This NCMR has been opened by: " & OpenBy & Chr$(13) & _
"Open Date: " & OpenDate & Chr$(13) & Chr$(13) & Chr$(13) & _
"This is an automated message. Please do not respond to this e-
mail."

'Write the e-mail content
DoCmd.SendObject , , acFormatTXT, varTo, , , Subject, Text, -1

'Set the update statement to disable command button once e-mail is sent
SQL = "UPDATE TBLncmr SET TBLncmr.ncmrnotisent = -1 " & _
"Where TBLncmr.ncmrnum = " & Me.ncmrnum & ";"

On Error GoTo Err_Execute
CurrentDb.Execute SQL, dbFailOnError
On Error GoTo 0

'Requery checkbox to show checked after update statement has ran
'and disable send notification command button
Me.ncmrnotisent.Requery
Me.ncmrnotisent.SetFocus
Me.cmdSendNotification.Enabled = False

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

Exit_cmdSendNotification_Click:
Exit Sub

Err_cmdSendNotification_Click:
MsgBox Err.Description
Resume Exit_cmdSendNotification_Click

End Sub

 

[Rod Plastow]

Can't spot anything wrong.

Where is the code actually failing? To find out enter the VBA coding window
and put a checkpoint against the first line of code after the Dim statements.
Do this by clicking in the left margin to display a solid circle. You may
also need to 'comment out' the On Error Go To statement.

Now run the application as normal. Code execution will stop at the
checkpoint. Execute the code following line by line by pressing F8 until you
get your error message.

BTW: VBA has handy names for carriage return and line feed in vbCr, vbLf,
vbCrLf and vbNewLine - I find them easier to use and understand than
Chr$(13), etc.

Rod

 

[dcc15 via AccessMonster.com]

Thank you,
First, I changed all the Chr$(13) to vbCr, this came from some old code I
have been using (took a while to figure out Chr$(13) was a carriage return,
vbCr makes more sense to me also.
I found the first error:
Line: stWhere = "TBLUsers.User = stWho
should be:stWhere = "TBLUsers.User = """ & stWho & """" (don't yet fully
understand the use of the " and & signs, I'd appreciated any explanation.
The mail works but I get another error after sending (Run-time error '3061':
Too few parameters. Expected 1.) on line:
CurrentDb.Execute SQL, dbFailOnError
What is supposed to happen is when notification is sent a Update is supposed
to mark the [TBLncmr.ncmrnotisent] checkbox (not sure about the -1) and when
checked the command button is disabled (can't send redundant messages).
Suspect code:
'Write the e-mail content
DoCmd.SendObject , , acFormatTXT, stvarTo, , , Subject, Text, -1

'Set the update statement to disable command button once e-mail is sent
SQL = "UPDATE TBLncmr SET TBLncmr.ncmrnotisent = -1 " & _
"Where TBLncmr.ncmrnum = " & Me.ncmrnum & ";"


'On Error GoTo Err_Execute
CurrentDb.Execute SQL, dbFailOnError
'On Error GoTo 0

'Requery checkbox to show checked after update statement has ran
'and disable send notification command button
Me.ncmrnotisent.Requery
Me.ncmrnotisent.SetFocus
Me.cmdSendNotification.Enabled = False

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdSendNotification_Click:
Exit Sub

Err_cmdSendNotification_Click:
MsgBox Err.Description
Resume Exit_cmdSendNotification_Click

End Sub
-----------------------------------------------------------------------------

Private Sub Form_Current()

'Enable Send Notification command button
'if Notification Sent checkbox is not checked
If Me.ncmrnotisent = True Then
Me.cmdSendNotification.Enabled = False
Else
Me.cmdSendNotification.Enabled = True
End If
End Sub
------------------------------------------------------------------------------


A few other issues/ annoyances:
1). Can the Spellcheck be disabled in the code for this message?
2). I'd like the message to just send (after it been tested to be good), can
the code send the message so the user won't have to interface with Outlook?

Thanks for your help

 

[Rod Plastow]

Let me deal with the easiest questions first; this will give me time to print
and study the code and, hopefully answer all your questions. (I've had the
'Too few parameters' message in the past and (in my case) it had to do with
executing parameterised command queries.)

The ampersand (&) is the string concatenation symbol. It is most commonly
used when one segment of the result string is a literal and the other is a
string variable. Here's a trivial example: suppose you wanted to construct
the labels for month 1 through month 12; your code would look something like:

For i = 1 to 12
strLabel = "Month " & i
Next

The other common use of the concatenation symbol is to break up long text
lines that would otherwise flow off the right-hand edge of your screen, into
successive lines. In this case it is combined with the continuation
character of underscore (_).

"Mary had a little lamb. " & _
"It's fleece was white as snow."

is exactly the same as coding

"Mary had a little lamb. It's fleece was white as snow."

Double and single quotation marks can be confusing. The rule is that you
enclose a literal string inside a pair of double quotation marks. These
marks simply delimit the string and are not included in the result. If you
want to include a double quotation mark as part of the resulting string then
you type it twice. This often results in sets of three double quotation
marks - one being the delimiter and the other two an instruction to include a
double quotation mark in the resulting string. Ugh!

For example if you need a where clause that should read

WHERE City = "Boston"

then it is coded as

"WHERE City = ""Boston"""

In your case your comparison string is a variable so you end up with a
construct similar to

"WHERE City = """ & Me.txtCity & """

But all is not lost as Access SQL understands single quotation marks in such
cases. The above may be more conveniently coded as

"WHERE City = 'Boston'" or "WHERE City = '" & Me.txtCity & "'"

The SendObject method/action uses the application defined as your default
mail client. In your case (as in most cases) this is Outlook. There is no
way I know of sending email directly from Access. It is my experience that
interfacing with Outlook is not a pain in this situation. However you will
get the security message that a program is trying to send email on your
behalf. You cannot suppress this message, it's the price we all have to pay
because of the misbehaving minority. Allen Browne in a current post
included a link that suggested the message might be avoided getting Outlook
to actually 'send' the email as Outlook considers itself to be a trusted
source. (Thanks for that Allen. When I have a couple of days I will try and
construct all the necessary modules and linkage. )

I think the spell check can be surpressed but only using Access to
programmatically open a copy of Outlook, alter the options and then send the
email, but if you're going to all that trouble then you might as well
complete the job and include the suggestion in the preceding paragraph.

Oh, nearly forgot: the -1 in your SQL is simply the integer value for True.
You can substitute True and make things more obvious.

I've been thinking while writing this about that missing parameter message.
It has to be caused by the parsing of the SQL string. I don't like the
semicolon at the end, try removing it. Also sometimes you get invisible
characters in your code, try rewriting the entire SQL string in new lines and
deleting the old lines. The comand line processor interprets a question mark
as a parameter placement symbol. Do you have any question marks in your text?

But ....... !

Why bother updating the underlying table when you obviously have the
checkbox displayed on your form. Simply

Me.ncmrnotisent = True

and dispense with all that CurrentDB.Execute and Me.ncmrnotisent.Requery
nonsense.

Hope this all helps,

Rod

 

[dcc15 via AccessMonster.com]

Thanks much for the explanation(s).
It's just about where I want it (I think).
As I said before I got Bits and pieces of this code from a "template".
The "Send To' info is obtained from combo box in the form that queries
TBLUsers, this feature of the code could be handy but in this application the
notification will always be sent to NCMR Group from TBLUsers so I'd like to
drop the combo box and put it in the code, My attempts so far have been
unsuccessful (I know i'm close). I like getting the address/addresses from
the table, I would just like to be able to say which one in the code (for
this app.).

'-- Name/Group to send notification to
stWho = Me.ncmrSentTo '-- Combo box in the form "NCMR Group"
stWhere = "TBLUsers.User = """ & stWho & """"
'-- Looks up email address from TblUsers
stvarTo = DLookup("", "TBLUsers", stWhere)

Thanks

 

[Rod Plastow]

Hi again,

Yes you're close - but as the acrobat said .....

DLookup returns only one value. It sounds as though you have a group of
addressees and I assume you want to send one message to all of them as a
group, not individual messages to each. So DLookup is not appropriate in
this case.

Before I proceed may I comment on the code sample you supply. Yes I
understand that you have plagiarised this from various templates so please
don't take it personally. If you were to use the DLookup function I suggest
it would be better coded as:

stvarTo = DLookup("", "TBLUsers", "WHERE User = '" & Me.ncmrSentTo &
"'")

The use of intermediate string variables is eliminated and any reader does
not have to check back and forth as to what each contains.

Back to the topic: what you want to achieve is a string that mimics the
address lines in Outlook, each email address being separated by a semicolon.
Personally I would build a function that accepts one or more comparison
parameters and returns the formatted string of matching addresses. I however
need to know whether you want to use ADO or DAO when accessing your tables
before suggesting some sample code. I also need to confirm what comparison
parameters you may have.

Rod

 

[dcc15 via AccessMonster.com]

You just went over my head (not hard to do at this point), I have seen ADO
and DAO...something about libraries, where/how do I find what I'm using.
Tried the line you suggested but couldn't get it to work (I see what it's
doing), tried disabling various combinations (including all) of the lines
that were/are doing this function.
Thanks

Hi again,

Yes you're close - but as the acrobat said .....

DLookup returns only one value. It sounds as though you have a group of
addressees and I assume you want to send one message to all of them as a
group, not individual messages to each. So DLookup is not appropriate in
this case.

Before I proceed may I comment on the code sample you supply. Yes I
understand that you have plagiarised this from various templates so please
don't take it personally. If you were to use the DLookup function I suggest
it would be better coded as:

stvarTo = DLookup("", "TBLUsers", "WHERE User = '" & Me.ncmrSentTo &
"'")

The use of intermediate string variables is eliminated and any reader does
not have to check back and forth as to what each contains.

Back to the topic: what you want to achieve is a string that mimics the
address lines in Outlook, each email address being separated by a semicolon.
Personally I would build a function that accepts one or more comparison
parameters and returns the formatted string of matching addresses. I however
need to know whether you want to use ADO or DAO when accessing your tables
before suggesting some sample code. I also need to confirm what comparison
parameters you may have.

Rod[/QUOTE]

 

[Rod Plastow]

Hi,

ADO = ActiveX Data Objects (more standard, not restricted to JET databases)
DAO = Data Access Objects (more integrated with native JET functionality)

Yes the objects reside in libraries both of which are referenced by default
in v2003 and v2007, so you can mix and match if you want. References are set
from the VBA code window - Tools + References. Have a look at yours; the
ones with a check mark are the referenced libraries.

Can't think why my DLookup suggestion does not work. Don't waste time for
now and revert to the code you know works.

I moved to ADO because a few versions ago Microsoft said that was the way to
go, so the following example uses ADO. (If you twist my arm I will try to
remember the DAO equivalent.) I've attempted to use you names as far as
possible but please double check and substitute your names where I do not
know them.

Public Function EmailAddressString (r_Cmp as String) As String
Dim rstUsers as new ADODB.Recordset
With rstUsers
.ActiveConnection = CurrentProject.Connection
.LockType = adLockReadOnly
.Open "SELECT Email FROM tblUsers WHERE MyField = '" & r_Cmp & "'"
Do until .EOF
EmailAddressString = EmailAddressString & !Email + ";"
.MoveNext
Loop
.Close
End With
End Function

This function allows you to pass a string parameter. It accesses the users
table filtered by this parameter. Each email address retrieved is
concatenated to the result string with a semicolon delimiter. The use of the
plus rather than the ampersand is deliberate; the semicolon only gets
appended if the Email variable has a value.

OK, the result string has a spurious semicolon on the end but I don't think
this worries Outlook.

Rod

 

[dcc15 via AccessMonster.com]

Hi, I see I'm not the only one doing all nighters
Wow, look at all those "libraries"...what are they all for and when do you (I)
use them?!?
There seems to be an ADO & DAO checked but I noticed there are "others" of
both down the list, I'm using v2003 so hopefully I won't have to go there
just yet.
I don't understand the code (not unusual for me),
With the code I'm using I have a [user] column with a record "NCMR Group" the
field has all the addresses with the semicolon between them and the
current code pulls the whole field as written.

Would I replace "MyField with NCMR Group, if so can mutiple "users" be put in
here?
Then replace:
stWhere = "TBL_Users.Group = """ & stWho & """"
stvarTo = DLookup("[EMail]", "TBL_Users", stWhere)
with the code you have suggested (disabling the associated Dim's).
Don't take this wrong, but I understand most of the code I'm using now (the
""" & stWho & """" part and how/when to use the quotes still got me
scratching my head a little). My quick pastes were un successful, but I will
have some upcomming e-mail buttons on a couple of forms I'm creating now so
I'll give it a try then (maybe I'll have some more "book" knowkedge by that
time).
Thanks again

 

[Rod Plastow]

Hi,

Yup it's 00:30 here. I was just going to bed after checking the track of
the latest typhoon when I spotted your reply - so, top off another bottle of
San Miguel and here we go.

Regarding the libraries: for now leave well alone. If you use one of the
fancy Microsoft controls (e.g. calendar control or tree control) Access will
kindly reference the required library automatically. However notice that the
libraries are not restricted to Access; you will see Outlook, Excel, Word,
etc. libraries there as well. Checking these libraries enables Access to
'know' the objects, methods and properties of those applications, thus
enabling all that fancy cross application programming everyone's always
asking about. Why are there different versions and why is not the latest
version checked by default in all cases? Please redirect that question to
Bill Gates.

Yes, both an ADO library and a DAO library are checked which is why you can
mix and match both scheme in your code. However you must fully qualify each
and every object reference as there is name duplication between them. Simply
coding 'Recordset' will confuse VBA - actually I don't think it issues an
error message but takes the first Recordset class it finds. It then gets its
knickers in a twist when it can't find the particular method in this library,
usually at execution time. So it's necessary to code DAO.Recordset or
ADODB.Recordset, etc.

OK, I misunderstood. I thought you had a table of individual users, each
with an email address and you wanted to select a subset of those users and
concatenate their email addresses. I now understand the concatenation is
already done so DLookup is the function to use. Please treat my sample code
as an academic but totally irrelevant exercise.

I think you should be OK now. I still do not understand why my version of
the DLookup function did not work but stick with the code that works; "If it
ain't broke, ..."

However ... and there is an MVP contributor to this site named Klatuu who
would concur ... from what you describe your database design leaves much to
be desired. We could spend another late night discussing database design.

Now I really am going to bed. The typhoon has veered north and will miss us
by 50 to 75 miles.

Rod

 

[dcc15 via AccessMonster.com]

San Miguel...? must be in the Pacific (I'd say the PI, but I don't think the
time would be right), anyway hope you don't see this till you get some sleep.

I am very concerned about DB "normalization" on this project. First let me
say
I have been working with the "data" for over 10 years in a "Flat" file DB
which as the company has grown has become difficult/problematic (significant
redundant entry and need to perform functions outside of the DB...), plus
there is a greater need for multiple user interface with the DB; So, This is
a DB to be used for processing non-conforming material that can be reported
by the customer, supplier or internally. All start with the same basic
information, NCMR (first table) and then wander in different directions and
then come back together on completion. “Customer†Goes to RMA (Return
Material
Authorization, second table), which could have multiple RMA’s against one
NCMR (currently can’t do this without entering a whole new record), The RMA
deals with credits (which take a long time to resolve). “Supplier†goes to
SCAR
(Supplier Corrective Action, third table). The three come back together
(“Internal†goes straight to this) with a CAR (Corrective Action, fourth
table),
again could have multiple CAR’s for Customer and Supplier (generally only one

for Internal), the CAR table will contain the bulk of the data (Cause,
Actions,
action assignments, quantities and dates). All of these records need a
separate
ID and need to be related back to the original NCMR. There is also other
possible
off shoots such as Stock Purges and maybe some more (I hope not). Of course
lots
of Reports too.
OK, I need ID’s that can be issued (internally and externally) and used
for relating several types of data and querying. This ID format, in one form
or
another is widely used in my industry by other companies (some with better
success
than others), I see problems with leading zero’s and not knowing what “Typeâ€
of ID
it is (some put the “ID Type†(prefix) in forms/reports as fixed which can
cause
problems when querying or analyzing data. . Manually managing these ID’s (I’m

avoiding calling them numbers) is not practical, so with much effort and lots
of
help I have VBA code that generates a meaningful ID that can be easily (I
think/hope)
to each. Here is the ID generating code I’m hoping to use as, call it, the
foundation for this DB (The only redundant data will be ID’s as needed to
relate
tables to each other.
NOTE: ID is stored as Text
This is the Data and I've crumbled a lot of paper trying to organize it in a
manner
that should eliminate redundant data and still be able to tie it all together
(I
can crumble some more).
==================================================
Private Sub Form_BeforeInsert(Cancel As Integer)

On Error GoTo MyErrorHandler
'-- Generates record ID
Dim strPref As String

strPref = "NCMR-"
strYrMo = Format(Date, "yyyymm")
strWhere = "[NcmrNum] Like """ & strPref & strYrMo & "*"""
varResult = DMax("[NcmrNum]", "TBL_NCMR", strWhere)

If IsNull(varResult) Then
Me.ncmrnum = strPref & strYrMo & "001"
Else
Me.ncmrnum = Left(varResult, 6 + Len(strPref)) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

MyErrorHandlerExit:
Exit Sub

MyErrorHandler:
MsgBox "Error Description: " & Err.Description & " Error Number: " & Err.
Number
Resume MyErrorHandlerExit

End Sub
====================================================
Thanks,
DCC

 

[Rod Plastow]

Hi,

Right first time: I'm now in the PI.

Yes databases tend to grow like Topsy rather than be designed from first
principles. I've long contended that Access, good as it is encourages
intuitive, non normalised database design.

There is nothing wrong with your code that I can see. However may I make
some personal observations.

1. I'm a member of the meaningless key fraternity. No matter how strongly a
real data element suggests itself as a primary key I normally key my tables
with an autonumber whose pupose is solely to identify each row and has no
other meaning apart from that. Every rule is made to be broken and I would
not insist on a separate key if I was constructing say a table of record
states - 'Active', 'Closed', etc. are quite adequate as keys in themselves.

Think through the ramifications of using the NCRM id as a primary key.

2. Consider not storing the prefix and making the id an autonumber such that
Access takes care of the incrementation for you. Whenever you retrieve the
id value for display purposes use the function:

Format(NcrmNum, """NCRM-""000")

You have to use double quotes here, single quotes do not give the desired
result. Define foreign keys to the autonumber as long integer.

I suggest that if we talk further it should be offline before I am accused
of turning this discussion board into a chat site. My email is
(e-mail address removed)

Rod

 

[dcc15 via AccessMonster.com]

I really appreciate your help and advise, my main problem with the autonumber
approach is the "other users" in particular the executives that insist on
making their own queries and reports, if it was only me I would have
certainly used some form of what you suggested (would be much easier). Anyway,
it's starting to come together (third form, second table with one e-mail
notification) and I'm learning a lot about writing code. You're right this is
getting a bit chatty so with this I'll end this thread. I'll save your
address for when I get really stuck, next thread will probably be about
sending e-mail (more reading).
Thanks again for all your help.
DCC