Send e-mail from access databases 65

[tpowers]

Now I am a new guy on the block, so bear with me. I have a form that users are entering client information into all day, and I have been asked to set the database up so that when the are done entering the record that it will automaticlly send the client a confirmation e-mail that the record as been recieved and entered into our database.

Again I am new at this so if so one as a clue of how to do this that would be great.

 

[famehrie]

This is one of the best threads I have seen. I have created a database using the ideas and code used in this thread and it woorks great. I was wondering if there is a way to code access to add the default Outlook signature to the body of an e-mail? If anyone has any idea that would be great.

Thanks

 

[nat1967]

Hi nai1992,

Its been a long time since I looked at this thread and WOW!!! I am blown away by all the questions and great help!!

ok, setting a flag is possible and done with the MailItem property.

example follows:

**********************************

Dim objEmail As Outlook.MailItem

With objEmail


.To = Email
.Subject = "ILL Request"
.Body = strBody
.FlagDueBy = #10/31/2003# '<---since its a date, it must be included with pound signs ##
.Send

End With

****end example*****



Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jdegeorge]

Nathan

You do seem to be the email expert! I haven't seen that many stars in a long time!

I have a situation where I want the database to automatically send me an email via outlook whenever someone signs in with a pre-determined text message.

I have never done this before so I have no clue as to how I would even begin to modify your code.

Is this easy to do?

Jim DeGeorge

 

[nat1967]

Hi Jim,

Thanks for the compliment!

Where is the user signing in? is this a form?


or is this using Access security sign in?


Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jdegeorge]

Nathan

I have a form where the user selects his/her name from a dropdown box, which is then hidden so I can store their ID on all the work they do.

I'd like to have the email sent to me when they click the signon form's OK button.

Jim DeGeorge

 

[nat1967]

ok, this sounds like its doable. Not knowing all the variables you are using, here is a simple example:

strRecipient is the email address you want to send the email to.
Me!lstUser is my listbox.

'*****example code****
Private Sub cmdOK_Click()
Dim strRecipient As String

strRecipient = &quot;somewhere@yahoo.com&quot; '<--your email address

DoCmd.SendObject , , , strRecipient, , , Me!lstUser & &quot; has signed into the database&quot;, , False

End Sub


'****end example****

In help, look up SendObject to get all the variables. The example above will send an email to you telling you who just logged into the database when they click the OK button.

HTH

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jdegeorge]

Nathan

Adjusting the code to fit my form's values, etc., here's what I came up with:

[tt]Dim vUser, vName As Variant
Dim strRecipient As String

vUser = [Forms]![frmSignOn]![SignOn]
vName = DLookup(&quot;[User]&quot;, &quot;tblPassword&quot;, &quot;[UserID#] = &quot; & vUser)
strRecipient = &quot;somwhere@aol.com&quot;

If vUser <> 1 Then
DoCmd.SendObject , , , strRecipient, , , vName & &quot; has signed into the CGA database!&quot;, , False
End If[/tt]

It works beautifully!!! Thanks for the help, and enjoy yet another star on this long list of stars!


Jim DeGeorge

 

[quest4]

Nathan, this is one incrediable post with many real good ideas in it. Though many are similar to what I am about to try and do, nothing is really to close. I have never tried to e-mail anything out of an Access 2000 dbase, but I have been asked to, marvelious. So let me start by asking a couple of dumb questions, can I e-mail a short message to a given list of users with a hyper-link to this dbase? Can it open the dbase to the correct record, kind of like the way Tek-Tips does? Thank you in advance for any assistance.

 

[nat1967]

HI guest4,

Is it possible? yes.

however, since you mention tek-tips, are you doing a web based application? if so, you would need to investigate ASP, VBNet, Pearl, PHP, or other web based applications. Access could house the backend information for you but you would be better off using another language to actually do your coding. unfortuately, i am not any good at them. :-(

Access does offer the data access page which can create the forms for you. Personally, I havent had much luck with that.

PHP is probably the easiest to learn and work with. (my opinion)

Good luck on your project. Sounds like quite a lot of fun!!!

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[quest4]

Nathan, thank you very much for the information. This is not a web-based dbase, just your standard old multi-user type dbase. The Pkey in this dbase is ECNNumner, which controls everything. The dbase resides in H:\Dbases and its' name is ECN_DBase. This is the first time I have ever played with e-mail in a dbase, so I am a little bit lost here. I just need a simply message with a hyperlink to the ECNNumber, if possible, and e-mail it to the people in the tabe, tblAuthorizedUsers, which also has the e-mail addresses, and we do have Outlook2000, too. What would be the best way to proceed with this? Thank you again for the information, I really appreciate any assistance I can get on this.

 

[grobermatic]

Hi,

I'm getting:


Runtime Error '429'

Activex Component can't create object

I have the reference of Outlook 9.0 and I'm using the following code :


Sub Send_Email()

Dim OLApp As Outlook.Application
Dim strSubject As String
Dim OLMailItem, newMail, newRecipient
Dim strMailBody As String

strSubject = &quot;&quot;
strMailBody = &quot;&quot;

Set OLApp = CreateObject(Outlook.Application)
Set newMail = OLApp.CreateItem(OLMailItem)
Set newRecipient = newMail.Recipients.Add(&quot;email@work.com&quot

With newMail

.Subject = strSubject
.Body = strMailBody

End With

newMail.Open

End Sub

I get the error on the following line :

Set OLApp = CreateObject(Outlook.Application)

Can anyone help?

Thanks in advance


Craig

 

[nat1967]

Hi Guest4,

To my knowledge, there is no way to accomplish what you requesting. Access is dependent upon itself running to perform queries and stuff. It still sounds to me like this is going to be something needed to be coded in a different application and use Access to house the data. Maybe someone with more knowledge than me will stop by and help you out. I just have never seen or read about any examples like you are trying to create.

Sorry. :-(

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Hi Craig,

You forgot the quotes around Outlook.Application.

Set OLApp = CreateObject(&quot;Outlook.Application&quot

that should clear it up.

HTH

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[quest4]

Thank you for the response, Nathan. This is near the end of my little nightmare. Archiving I think I can handle but this is unknown ground for me. So please let me ask you a couple of dumb quetions, because from what I have seen here in this post you are the man, Nathan. As I click command buttons, can I at least have Access send an e-mail notice to someone that ECNNumber XX is ready for their approval? By that I mean can the e-mail have the ECNNumber appear in the notice. I don't want it to be as complicated as what you did for tpowers, a form letter, just something simple. Please let me know if that is something that is doable. Thank you for taking the time to assist me.

 

[nat1967]

Hi Guest4,

Yes, Access can certainly send the email you need. The simplest way is to use Docmd.SendObject.



Have A Great Day!!!,

Nathan
Senior Test Lead

 

[quest4]

Thank you for the assistance, Nathan. That makes my day at least a little brighter, now one other dumb question, do I use that function that you first wrote for tpowers to e-mail the notices to the people who are in the tblAuthorizedUsers table? Thank you again for the assistance, I do appreciate it very much.

 

[nat1967]

Hi Guest4,

You sure can with some small changes. It uses the Outlook email model instead of the SendOject method but it would work assuming you use Outlook and reference the Outlook object model.

will the form you are sending the email from show the users information? (name, email, etc...)

if so, then just map the field names to tpowers example and you should be golden.

HTH

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[quest4]

Nathan, thank you again for the information. I have just completed the Approval command buttons, about a dozen or so. I have a table, tblAuthorizedUsers, which has the LogOn and e-mail addresses in it, plus what each user is authorized to approve. Each of the buttons has two or three users who can approve the section or item. What I am thinking about is when CmdButton, ApdCCD, is clicked having it e-mail the approved users for the next CmdButton, ApdEng, which has two approved users. Those two users would receive an e-mail which says something like: ECN [ECNNumber] is ready for your approval. Please process it as soon as possible.
Each CmdButton would be sent to a different group of users. My Question at this point is this still something that is doable? Can I still use tpowers code? What do I have to do to modify it? Would it help to see the code for the CmdButton? Is this done with a module or would this be done by individual procedures? Thank you again for your time and assistance, I really appreciate it.

 

[nat1967]

hi guest4,

From the sounds of it, I dont think you need to do something as complicated as the Outlook option. Docmd.SendObject should do just fine.

I would do something like this behind each command button:

'*****example code****
Private Sub cmdCCD_Click()
On Error GoTo errhandler:
Dim strRecipient As String

strRecipient = &quot;somewhere@yahoo.com;nowhere@yahoo.com&quot; '<--email address or addresses of the ApdEng people

DoCmd.SendObject , , , strRecipient, , , &quot;ECN&quot; & Me!ECNNumber & &quot; is ready for your approval.&quot;, , False '<---formats and send you email

Exit Sub

errhandler:

msgbox err.number & &quot; &quot; & err.description
exit sub

End Sub


'****end example****


Have A Great Day!!!,

Nathan
Senior Test Lead

 

[quest4]

Thank you again for the assistance, Nathan. I have one question before trying this, I have a procedure already in the OnClick event, what do I do about that procedure and its' error handle stuff? Basically all it is doing is check to see if the user is in the tblAuthorizedUsers table and the checking to see if that user is authorized to approve that section. Would it help to see that code? Thanks again for all of the assistance.