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.

 

[jsaliers]

I think I misstated what I meant. When the e-mail is sent, I did not specify an attachment, but one was included anyway. It was a text file, and appears to be a text version of the main table. I found this very strange because I didn't code for an attachment, and do not want one. I guess my question is, is there a way to specify no attachments?

Thank you again.

Jon

 

[jabrony76]

ObjectRequired:

Don't know if you'll get this with all the responses but Thanks for the Lotus Notes Routine! Works great but I have one question. The code you provided keeps a copy of the email in the "Drafts" folder of Lotus Notes. Can I switch this to the "Sent" folder? The Code I'm using is below in red.

Thanks!
Andy

[red]
Public Function EmailUsers()

Dim Session As Object
Dim db As Object
Dim doc As Object
Dim rtf1 As Object
Dim eo1 As Object
Dim vComplaint As String

vComplaint = Forms![frm_Complaint_Form]![Complaint #:]

'Start new Lotus Notes Session
Set Session = CreateObject("Notes.NotesSession&quot
Set db = Session.GetDatabase("", "&quot
'Open Mail Database. This will prompt for a password if not already open-
'For full automation, Notes should already be open.
Call db.OpenMail
'Make new document
Set doc = db.CreateDocument

'Build e-mail
With doc
.Form = "Memo"
.SaveMessageOnSend = True
.SendTo = "Andrew_J_Michalski"
.cc = ""
.Subject = "New Complaint# " & vComplaint
Set rtf1 = .CreateRichTextItem(doc, "Body&quot
'Import Text
Call rtf1.AppendText("New Complaint Created. See attached form for further information. " & Variable)
'Attach Files
Set eo1 = rtf1.EmbedObject(1454, "", "C:\Documents and Settings\amichal\Desktop\QIA Data Stats.doc&quot
'Send Mail
Call .Send(True)
MsgBox "An Email has been sent to QI and Support Staff informing them of this complaint. Thank you.", vbInformation, "Confirmation"

End With
'Deallocate Objects
Set rtf1 = Nothing
Set doc = Nothing
Set Session = Nothing
End Function

 

[fule12]

hi all

Can someone help me with my problem.

I create Query from code as belou and is working OK if query retern only 1 record.But becous normaly i have more then one record , how to send e- mail to more then 1 person ?

--------------- start code ----------------
Private Sub SendToEmail_Click()
On Error GoTo Err_SendToEmail_Click


If IsNull(Me.ReferenceNo) Then
MsgBox " Enter Quotation Reference Number", vbOKOnly, " Empty Field"
Me.ReferenceNo.SetFocus

ElseIf IsNull(Me.Status) Then
MsgBox " Select Status ", vbOKOnly, " Empty Field"
Me.Status.SetFocus
ElseIf IsNull(Me.Delivery) Then

MsgBox "Enter Text in Delivery Instruction ", vbOKOnly, " Empty Field"
Me.Delivery.SetFocus
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Label10.Visible = True
End If


DoEvents

Dim strClient As String
Dim strSQL As String
Dim db As Database
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim rstQOrder As Recordset
Dim strEmailTo As String
Dim strRefNo As String
Dim strBody As String
Set objOutlook = CreateObject("Outlook.Application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

' create sql string
strSQL = "SELECT Confirm_O.OrderID, Order.OrderNo, Vessel.VesselName,"
strSQL = strSQL & "Employees.DirectLine, Employees.DepaEmail, Employees.FirstName, "
strSQL = strSQL & "Employees.LastName, Confirm_O.ReferenceNo, Confirm_O.Status, "
strSQL = strSQL & "Confirm_O.Date, Confirm_O.Delivery, Suppliers.Company, "
strSQL = strSQL & "Suppliers.ContactName, Suppliers.SEMailAddress, "
strSQL = strSQL & "Vessel.OwnersID, Owners.OwnerName AS [Text], "
strSQL = strSQL & "Owners.OwnerName, Employees.Title "
strSQL = strSQL & "FROM (Vessel INNER JOIN (Suppliers INNER JOIN (Employees INNER JOIN ([Order] INNER JOIN Confirm_O ON Order.OrderID = Confirm_O.OrderID) "
strSQL = strSQL & "ON Employees.EmployeeID = Order.EmployeeID) ON Suppliers.SupplierID = Order.SupplierID) "
strSQL = strSQL & "ON Vessel.VesselID = Order.VesselID) INNER JOIN Owners ON Vessel.OwnersID = Owners.OwnersID "
strSQL = strSQL & "WHERE Confirm_O.OrderID = " & Forms![confirmOrder].Form![OrderID]

'Get DB and Recordset Pointers

Set db = CurrentDb()
Set rstQOrder = db.OpenRecordset(strSQL)
'Check to see if there are any order details to transfer
If rstQOrder.EOF And rstQOrder.BOF Then
'No order details to transfer
MsgBox "No order details to transfer.", , "No Records"
Me.Label10.Visible = False

Else
strClient = rstQOrder.Fields("Company&quot
strRefNo = rstQOrder.Fields("ReferenceNo&quot
strEmailTo = rstQOrder.Fields("SEmailAddress&quot

strBody = "Fm : " & Chr(13)
strBody = strBody & "Ref : " & strRefNo & Chr(13) & Chr(13)
strBody = strBody & "TO :" & rstQOrder.Fields("ContactName&quot & Chr(13) & Chr(13)
strBody = strBody & "RE : We would like to confirm the order according to your quotation." & Chr(13)
strBody = strBody & " Include all certificates where applicable." & Chr(13) & Chr(13)
strBody = strBody & "__________________________________" & Chr(13) & Chr(13)


With objEmail

.To = strEmailTo
.Subject = "Order Confirmation " & Chr$(32) & strRefNo
.Body = strBody
'.Send
.Display
End With
End If
'Close and Release Pointer

'Close rst & db
rstQOrder.Close
Set rstQOrder = Nothing
db.Close
Set db = Nothing
Set objEmail = Nothing
Me.LblMailClient.Visible = False
'Uncomment the next line if you want Outlook to shut down when its done.
'objOutlook.Quit


Exit_SendToEmail_Click:
Exit Sub

Err_SendToEmail_Click:
If Err.Number = 94 Then
Resume Next
ElseIf Err.Number = 2501 Then
Resume Next
Else

MsgBox Err.Number & vbCr & Err.Description
Me.LblMailClient.Visible = False
End If

Exit Sub

End Sub
---------------------- end code -----------------

thanks Fule

 

[tpowers]

Hello everybody, I have a question about e-mailing with out look. my code is at the very to of this post and I was wondering about the section of the code that states .send

when I use .display my outlook will just display the email and when I use .send it will send the e-mail but I it tells the end user that a program is trying to send e-mail on their behalf and that the need to click yes for the program to send it or no for the program not to send it.

My question is, is there any code that I can use that will just send the e-mail with out asking the end user anything.

If so, can someone help me with it.

Thank you in advance,

TPowers

 

[danvlas]

I've seen this in Outlook Express: Tools-Options-Security-uncheck 'Warn me when other applications try to send mail as me'.

Don't know where it is in Outlook, but it's a start...

HTH

Daniel Vlas
Systems Consultant

 

[tpowers]

Well I have looked in outlook and I could not find anything so I will keep looking, but if anyone can come up with anything please let me know.

 

[nat1967]

Hi tpowers,

What you are experiencing is part of the security messures implemented by MS. There is not a way around it that I have found or read about.

Here is a link at MS that helps explain. Also, in this link there will be a couple of knowledge base articles that go into further detail.

http://office.microsoft.com/assistance/2000/Out2ksecFAQ.aspx

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[mp9]

To Danvlas, TPowers and Nathan1967 - there is a way around this annoying but ultimately worthy security measure is a nifty little tool called Outlook Redemption which you can read more about at

http://www.dimastr.com/redemption/

http://www22.brinkster.com/accessory/

 

[coddy13]

You can lower your security settings under Outlook to prevent the message also..

Brian Branch

http://www.bbengineering.com

 

[ianbar]

I know this question was asked ealier in the thread, but is it possible to email all the people based on the results of a query? This would save me a great of work if it was possible.

 

[tpowers]

Hello everyone, mp9, I looked at that link you provided and I found some good stuff. Thank you.

Now my next question is that in my code I am doing a .send and that brings up that box that informs the user that the program is trying to send e-mail for them but if the click no I get a run-time error that reads:

'Run -time error '287'

Application =defined or object-defined error how would I set up the conditional statement that would catch that error?

Thank you in advance,

TPowers

 

[grtammi]

Hey everyone ...

As I was scrolling thru all the posts in this thread in all my infinite boredom (actually, I was having combination of Javascript and VB 6.0 floating thru my head, mixing themselves up and making lots of errors), I came across tpowers post on Dec 13 about receiving the Project or Library not defined error ...

You really need to watch the versions of Office that your users are running - if some are running Office 97, they are using Outlook Library 8.0; those running Office 2000 are running Outlook 9.0, and XP, 10.0 - same goes with each of the other automation libraries, such as Excel and Word.

Now, what really happens, is that Microsoft products (especially Access) are selfish, and figure that they're the only program around. For example, I designed a database with Access (I'm running Office 2000), and therefore had a reference to the Outlook 9.0 library. All was fine and dandy, my users (mixture of Office 2000 and XP) could open fine. The problem came when an XP user opened the database. Since they were running Outlook XP and hence type library 10.0, when they opened the database, Access automatically converted the type library to 10.0 from 9.0. Not really a big issue, until a user of Office 2000 comes along later to try and open the database; guess the error they got?

"Project or library etc etc etc" - why?

Since they are using Outlook 2000 (library 9.0), and the XP user converted the database to the 10.0 library, you get a bad reference, and hence an error when any code tries to run.

I think (and other's probably have a better solution) your best bet, for networked database anyways, is to go local front-ends and networked backends - this way, if an XP user opens the database, it only affects their front end, and not everyone elses.

HTH

Greg

PS: Hey, I ramble pretty good for a cold Canadian (-29F here yesterday)

PPS: Nathan1967, thanks for the e-mail a while back about my Word FAQ - I didn't think anybody would read it!
If you don't understand, just nod and smile ...

 

[rhinomac]

Does anybody know how to set the sender or to set who the e-mail is from. I have an autosend email access code I wrote and I want the email to appear that it comes from my group email address, instead of my personal address. There is no .from option.
heres a little sample:

Set objOutlook = CreateObject("Outlook.application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = email
'.SentOnBehalfOfName = sender (**Am I on the right track**)
.subject = subject
.Body = strbody
.Send
End With


Set objEmail = Nothing

 

[danvlas]

I'd say you're on the exact track...




Daniel Vlas
Systems Consultant

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

 

[cuok]

Hi
Here is a function to Get the sender Computer name .
put the function as a new module and name it fOSGetMachineName or any name but not the same name as the function (fOSMachineName).
'============= begin function code ===============
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = left$(strCompName, lngLen)
Else
fOSMachineName = &quot;&quot;
End If
End Function

'========== end function code ==============

add this to your e-mail code:

.Body = &quot;Domain: &quot; & fOSMachineName() 'Get the Computer name


CUOK

 

[essage]

Hey, I just added all the code to my database, but after I clicked the button to send, an Microsoft Access message box poped up saying &quot;Object Required.&quot; I used the code given by nathan, and I know outlook is referenced. Any help would be much appreciated!

 

[nat1967]

essage,

Where in the code was highlighted? Which object?

Also, check for any bad references. Its possible the Outlook reference has been corrupted.

post back and let me know

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[essage]

That is the weird part, no part of the code was highlighted. It just says &quot;object required&quot; with no explanation. How can I check for a bad Outlook reference? Here is the code I used:

Private Sub CmdEmail_Click()
On Error GoTo Err_CmdEmail_Click

'******begin code******
Dim Email As String
Dim ref As String
Dim fun As String
Dim Dated As String
Dim Note As String

'**create variables for Outlook
Dim objOutlook As Object
Dim objEmail As Object

'**gathers information from your form. this sets the string variable to your fields
Email = frmAppNum!Email
ref = frmAppNum!SendSales
fun = frmAppNum!fun
Dated = frmAppNum!Date
Note = frmAppNum!name

'***creates an instance of Outlook
Set objOutlook = CreateObject(&quot;Outlook.application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = Email
.Subject = ref
.Body = Note
.Display 'sends the email in Outlook. Change to DISPLAY if you want to be able to
'modify or see what you have created before sending the email
End With

'**closes outlook
objOutlook.Quit
Set objEmail = Nothing

Exit_CmdEmail_Click:
Exit Sub

Err_CmdEmail_Click:
MsgBox Err.Description
Resume Exit_CmdEmail_Click

End Sub

 

[nat1967]

HI,

I think I have your problem solved. (see bold area)

Email = frmAppNum!Email
ref = frmAppNum!SendSales
fun = frmAppNum!fun
Dated = frmAppNum!Date
Note = frmAppNum!name

Since you are referencing fields on your form, I would use 'Me'. = Me!Email

I made the change and the code worked afterwards. Also, I would suggest another change. Not mandatory, just a suggestion based on how the code acted.

Either change .Display to .Send or remove objOutlook.Quit. Currently, Outlook is not sending the email but it attempts to shut itself down. Just a thought...

Anyway...let me know how this works for you.



Have A Great Day!!!,

Nathan
Senior Test Lead

 

[danvlas]

No weird thing in NOT highliting the offending line:

On Error GoTo Err_CmdEmail_Click

Comment that and the code will break on error. Currently, the error is trapped and passed to:

Err_CmdEmail_Click:
MsgBox Err.Description
Resume Exit_CmdEmail_Click

And by the way, label Exit_CmdEmail_Click should be placed before setting object variables to Nothing...

Just a thought







Daniel Vlas
Systems Consultant

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