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.

 

[nat1967]

Hi guest4,

just put my suggestion at the end to make it the last thing that fires. you can have multiple things happening in each event so shouldnt be a problem. your code will just go line by line doing what is asked until it reaches the end. now, this would go between your error handling which i assume starts at the top and ends at the bottom.

HTH

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[quest4]

Thank you Nathan. If I understand you, I put you code right after the
End If
and and just before the
Exit_CCDApproved_Click:
Did I get that correct? Thank you again for all of the help.

 

[dainamae]

This may not be the best place to post this, but I am hoping someone will be able to help...

I have gotten the SendObject command to work correctly on my .adp Access Program (SQL 2000 backend). I am attempting to send a report, but unfortunately, by using the SendObject it sends a report for all records in that view.

Any suggestions on how to filter the report so it only sends the report for a specified JobNumber?

 

[kakworks247]

Hello to all -- GREAT THREAD! I'm hoping someone can help me too.

I have a Access Program that works on a single user machine. When I took it to the company and ran it on a network machine the email quit working. Here's the scoop. The program takes an email, parses through it placing items into a table. It asks if you want to send an email. Click Yes and outlook email opens and you press Send to send email off.
Problem -- Outlook opens the email and you send it once. The next email to be parses is logged into the table but when you say Yes to send email it just seems to skip it. Any ideas? The code I use to send is below:

Any help greatly appreciated.

DoCmd.SendObject acSendNoObject, , acFormatTXT, RequestorEmail, , , "Response to ECR", _
"Dear " & RequestorName & "," & vbCrLf & vbCrLf & TextFileToString_FX(GetDBPath_FX & "ECR reply.txt&quot _
& vbCrLf & "Model(s): " & Models & "with your concern being: " & ReasonCode & _
". You have supplied the following description of the problem: " & Description & "." & vbCrLf _
& vbCrLf & "Regards," & vbCrLf & vbCrLf & "name" & vbCrLf _
& "title" & vbCrLf & "company" & vbCrLf _
& "location" & vbCrLf

(For confidentiality I removed some of the real words)

I am also going to follow-up with the Excel post above because now they want all the info put into Excel Spreadsheets too.

Any help would be appreciated. If you need more code or information, just let me know.

 

[quest4]

Nathan, I try the code and it fail, probably my modifications. This is what I am STARTING with, and I know it is wrong:
If DCount("[LogOn]", "tblAuthorizedUsers", "[LogOn]='" & fOSUserName() & "' AND [ApdCCD] = True&quot = 0 Then
MsgBox "You Are Not Authorized To Approve This Section."
Me!CCDApproved.SetFocus
'Re-Enter Approval, same field
Else
'The User is Authorized
Me.CCDApprovedBy = fOSUserName()
'Enter LogOn in Approved By textbox
Me.CCDDate = Date
'Enter Today's Date in the Date textbox
Me.CCDComments.SetFocus
'Set Focus on Comment textbox
Dim strRecipient As String
If DCount("[LogOn]", "tblAuthorizedUsers", "[LogOn]='" & fOSUserName() & "' And [ApdAsy] = True", [E-MailAddress]) = 0 Then
strRecipient = "[E-MailAddress]"
'<--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
End If
End If
I know that using the DCount might work for the current user ONLY, but I am not exactly sure just how to proceed here. After the SetFocus, I think I should probably check all of the LogOn to see which two or three have ApdAsy = True and then get their E-MailAddress and use those E-MailAddress to send the e-mail messages. Does anyone have any suggestions on how to do this? Thank you so very much for all of the time and assistance.

 

[danvlas]

kakworks: you have encountered probably the most annoying bug in Access 2000:

http://support.microsoft.com/?kbid=260819

1. Change the list separator from a comma (,) to a semicolon( in Control Panel. Don't know why, but it solved the problem on my computer.

or, if that does not work

2. Try to get the latest version of Jet 4.0

or

3. If you use Outlook or somee email program that exposes itself as an ActiveX object, switch to Automation.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[nat1967]

Guest4,

your code was bugged up good. i set up a table using your names and got this to run.

'****start****
Private Sub Command0_Click()
On Error GoTo errhandler:

If DCount(&quot;[LogOn]&quot;, &quot;tblAuthorizedUsers&quot;, &quot;[LogOn]='&quot; & fOSUserName & &quot;' AND [ApdCCD] = True&quot = 0 Then
msgbox &quot;You Are Not Authorized To Approve This Section.&quot;
'Me!CCDApproved.SetFocus

'Re-Enter Approval, same field
Else
'The User is Authorized

Me.CCDApprovedBy = fOSUserName
'Enter LogOn in Approved By textbox
Me.CCDDate = date
'Enter Today's Date in the Date textbox
Me.CCDComments.SetFocus
'Set Focus on Comment textbox
Dim strRecipient As String
If DCount(&quot;[LogOn]&quot;, &quot;tblAuthorizedUsers&quot;, &quot;[LogOn]='&quot; & fOSUserName & &quot;' And [ApdAsy] = True And [E-MailAddress] = '&quot; & [E-MailAddress] & &quot;'&quot = 1 Then
strRecipient = [E-MailAddress]
'<--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
End If
End If

Exit Sub
errhandler:
msgbox Err.number & &quot; &quot; & Err.Description

End Sub

'*****end******

The second DCount was setup incorrectly and caused most of the problems. Check that one to see the changes I made to it.

Hope we finally have you down the right path.

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[grobermatic]

Hi,

Thanks for the response Nathan, that fixed that line of the code.

However I now get Runtime Error 438 - Access doesn't support this property ot method. on the line :

newMail.open

Can't understand why this is happening .. any help would be appreciated

Thanks


Craig

 

[nat1967]

Hi grobermatic,

try: newMail.Display

HTH

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[grobermatic]

Just the Job... thanks a lot

Craig

 

[quest4]

Thank you for the response, Nathan. I added the code, and the procedure ran and put the date in the correct textbox as did my LogOn, but I did not recieve a e-mail notice. Also, as a afterthought, can ECN Notice be added to the Subject line of the e-mails? Thank you again for all of the assistance.

 

[quest4]

Nathan, ever have one of those days where you know you should go home right now and hind under the bed? My misteak I click the wrong button that is why it did not e-mail me. But it is now gotton really strange, when I click the correct button, I get an error message:
Access can not field the field 'txtfield1' referred to in your expression.
Now when I do a find on txtfield1 it only appears in one line of code. ?That line is in a procedure that opens .tif files, which now makes no sense to me at all. That procedure was working good both before and after I added that code to the first procedure button, all of the other buttons are working correctly, less e-mail. Any idaes why this other procedure would interfer with this e-mail procedure? Thank you again for the help.

 

[kakworks247]

danvlas -- I will be at the company this week and will try out your suggestion. I did want to make sure you understood the email did not need to send to multiple people on the same email -- it's just a reply to each person who filled out the form.
Kimberly

 

[danvlas]

Kimberly: exactly. The first email goes out fine. All subsequent SendObject calls fail without even saying what was wrong.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cbase]

The most useful and detailed thread I have ever seen.
Well done!

Don

 

[nat1967]

Guest4,

1) you stumped me on that one. the only reason i can think of is you are somehow running the two procedures together. If you command button calls a specific event procedure, it will only execute what is between the start/end sub unless, within you code you are calling another sub or function.

2) &quot;can ECN Notice be added to the Subject line of the e-mails?&quot; - yes, just modify the Docmd.Sendobject line to reflect the changes. If you look up the SendObject function, you will be able to see all of the possible fields and modify each one accordingly.

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[kakworks247]

Daniel,
I'm hoping to test this out tonight. Thanks for all your help. This is the greatest resource on the Internet. I do have one 'stupid' question. What exactly is Automation? (am I showing how green I am at this yet?) I've been trying to teach myself how to do all the access programing, vb programming, etc and get this project done within the time frame. I haven't even delved into ActiveX as far as figuring out what it does, how it works, where to find it, etc. I'm just trying to keep my head above water with this project. I can't tell you how helpful this website has been. I send a big thanks to everyone!
Kimberly

 

[quest4]

Nathan, thank you for the response. I am totaly confused on this one, if have looked till I am blue in the face to find at least one common dinominator that could tie these two procedures together but nothing, thetdon't even run at the same time, the only characteristic they have in common is they both run off of the OnClick event, but on different pages of the form. And they are looking for different types of data. I will check in HELP to see if I can figure out the change I need to make. Thank you for all of you time and assistance.

 

[danvlas]

Kimberly: don't worry, everybody's green in the beginning.
Automation sounds scary, but it isn't. Actually, it's quite simple. It means 'opening' an application from your program and then using that application's objects and methods to force it do what you want. The bad thing is that you can only use applications that expose themselves as objects. You can use Outlook, CDO, EasyMail, but not Outlook Express, Netscape Messenger and the like.

In our case, the 'remote' application would be Outlook.

Here's the replacement of your code (paste the function in a module:

'============Code Start=======================
Function fSendMail(EmailAddress, SubjectLine, BodyText)
Dim objOutlook As Object
Dim objMailItem As Object

'Open Outlook if it isn't already open
Set objOutlook = CreateObject(&quot;Outlook.Application&quot

'create a mail message in Outlook
Set objMailItem = objOutlook.CreateItem(0)

'Now set mail message attributes:
With objMailItem
.To = EmailAddress
.Subject = SubjectLine
.Body = BodyText
.Send
End With

'Now close objects and cleanup
Set objMailItem = Nothing
Set objOutlook = Nothing
End Function
'===========Code End=================


Instead of your old code, place this:

Dim strEmailAddr As String
Dim strSubject As String
Dim strBody As String

strEmailAddr = RequestorEmail
strSubject = &quot;Response to ECR&quot;
strBody = &quot;Dear &quot; & RequestorName & &quot;,&quot; & _
vbCrLf & vbCrLf & _
TextFileToString_FX(GetDBPath_FX & &quot;ECR reply.txt&quot _
& vbCrLf & &quot;Model(s): &quot; & Models & &quot;with your concern being: &quot; & ReasonCode & _
&quot;. You have supplied the following description of the problem: &quot; & Description & &quot;.&quot; & vbCrLf _
& vbCrLf & &quot;Regards,&quot; & vbCrLf & vbCrLf & &quot;name&quot; & vbCrLf _
& &quot;title&quot; & vbCrLf & &quot;company&quot; & vbCrLf _
& &quot;location&quot; & vbCrLf


Call fSendMail(strEmailAddr, strSubject, strBody)

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[kakworks247]

Daniel,
I tried the comma semicolon fix to no avail. I placed the automation you so kindly outlined for me and it worked perfectly. I of course have one more question. Is there a way to have the email 'pop up' so the person can add personalized text to the form email? They would then just have to press Send to send it off.
Thanks again,
Kimberly