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.

 

[baybie]

Hello welshspoon, you could create a "Like No" query which you would then use to create a similarly named Report. This can now be used in the Object Name part of the SendObject macro. You can use either the report or the query in the macro, works either way.

 

[famehrie]

Hi,
I'm new to this whole Access thing and I want to say that the steps and ideas mentioned above have helped me out greatly. Especially the ones from Nathan1967.
My situation is as follows:
I have a combo box with 2 columns. I only want to send the second column. How would this be entered into the e-mail string?

 

[mp9]

Famehrie - you can use your combo-box's

.Column()

property to refer to the second column. Note that this property's index parameter starts at 0 for the first column, so to refer to the second column you'd use something like

cmbMyComboNameHere.Column(1)

HTH.

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

 

[famehrie]

Thanks mp9! That did the trick

 

[hwkranger]

kudo's to you guys, lots of helpful info here. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[dansnd]

Hi everyone,

Thanks to the above I have managed to get Access to open Outlook to send an email. (I used the SendObject command rather than Outlook as I had the same problem as several others defining Outlook). Thanks to you all for this success!

 

[jbento]

All,
This thread totally helped me, but I had one problem. I had to go into the Microsoft Visual Basic area, and then go to Tools, down to References, and I had to click the radio button associated with "Microsoft Outlook 9.0 Object Library". Everything works great after I did that!!!!!!!!

I TRULY WANT TO SAY THANKS TO ALL OF YOU!!!!!

THIS THREAD GETS ALL STARS FROM ME!!!!! Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[Cricker]

Bonjour!!!! I am having a little problem with sending emails. I have this code put in so far and it works fine but one thing is wrong... Actually 2 things.

First I want to use a query so i can take only certain email addresses and send them to certain people. When i try a query i get an error message. I think it is becasue i'm looping through and grabbing all emails. Could that be it???

Hopefully someone can help me....

cw

Second when i get all the addresses (ALOT and the max is set up to 250 at work) how can i take only 250 and send them and then another 250 and so on until they r all sent. Is there a way i can put them all ih a folder or something (holding area) and then send them and send others??

Option Compare Database

Sub SendEmail()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strEmail As String
strEmail = " "
Set db = CurrentDb

Set rs = db.OpenRecordset("QRY_EmailList", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
If rs!Email_1 = "" Then
If rs!Email_2 = "" Then
Else
If strEmail = "" Then
strEmail = rs!Email_2
Else
strEmail = rs!Email_2 & "; " & strEmail
End If
End If
Else
If rs!Email_2 = "" Then
If strEmail = "" Then
strEmail = rs!Email_1
Else
strEmail = rs!Email_1 & "; " & strEmail
End If
Else
strEmail = rs!Email_1 & "; " & rs!Email_2 & "; " & strEmail
End If
End If
rs.MoveNext
Loop
Set lobjOutlook = CreateObject("Outlook.application&quot
Set lobjMail = lobjOutlook.CreateItem(0)
Let lobjMail.Subject = "TESTING E-Mail" 'Change the subject to anything you
'Let lobjMail.To = ""
'Let lobjMail.CC = ""
Let lobjMail.BCC = strEmail 'your list of email addresses
'Let lobjMail.HTMLBody = "Do you want the email to have a message?" 'Change
'the message of the email
Let lobjMail.Body = "Do you want the email to have a message?"
'Change the message of the email
lobjMail.Save 'This command will save the email as a draft
'lobjMail.Send 'I commented this out so you can see the email before it sends
End Sub

 

[smurf01]

Nathan 1967, I have been looking at this thread and was wondering if you could help me also. I have an Access database that stores information regarding complaints for reels of paper that we buy in. The way it works is this we may have 20 reels delivered on one load if say 3 reels have a defect then we enter this info into a subform which then updates to the database. what i would like to do is to e-mail the details to the relevant supplier and copy to other people in our organistaion. I have tried e-mailing the form but i can only send as an attachment, what i would like is if the data went as just text however becuase there may be more than one record i need to see it in columnar form. The fields i am using are as follows

Complaint_Number
Entry_Date
Supplier
Making_ID
Reel_No
Reason
Operator_Action

is it possible you could look at this for me, I would very much appreciate it.

If it is easier to see the database let me know and I can e-mail to you


Regards

Paul

 

[jsaliers]

Hello.

I have a database where an engineer can order a project. I am trying to set it up so the database will automatically send an e-mail notification (sendObject Method) to the assigned party, as well as managers involved, and the requester. I am currently testing it, and am running into an error - "Too few parameters. Expected 1." Any ideas on why I am getting this error?

'***Start Code ***
Private Sub Command58_Click()
On Error GoTo Err_Command58_Click

'Declaration of Variables
Dim email As String
Dim cc As String
Dim origin As String
Dim body As String
Dim dbs As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

'Open and populate the recordset with required data
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & RequesterName.Value)
Set rs2 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & AssignedTo.Value)

'Gather the information about the e-mail
email = rs2("Email&quot
If rs1("Job&quot = "Product Engineer" And rs1("EmpStatus&quot = "Active" Then
cc = "ProductManagerEmail@company.com"
Else
cc = "OtherManagerEmail@company.com"
End If
origin = rs1("Email&quot

'Set up the body of the e-mail to add all fields
body = OrderDate & Chr(13) & Chr(13)
body = body & "Project: " & ProjectNumber & " - " & ProjectName & Chr(13)
body = body & "Name of Requester: " & RequesterName & Chr(13)
body = body & "Context of Test: " & TestContext & Chr(13) & Chr(13)
body = body & "Description of Work: " & DescriptionOfWork & Chr(13) & Chr(13)
body = body & "Assigned To: " & AssignedTo & Chr(13)
body = body & "Start Date: " & StartDate & Chr(13)

'Send the e-mail to the recipients
DoCmd.SendObject acSendForm, , acFormatTXT, email, cc, , "You have been assigned a project by " & origin & "!", body, True

'Add a new record
DoCmd.GoToRecord , , acNewRec

Exit_Command58_Click:
Exit Sub

Err_Command58_Click:
MsgBox Err.Description
Resume Exit_Command58_Click

End Sub

'***End Code***

Currently, everyone on our network uses Outlook, so I was considering using that. However, if I switch to the Outlook Application method, I would like to send a TASK, not an e-mail. Is there a way to send this as a task, and not as an e-mail?

Thanks in advance for all your help.

Jon

 

[cuok]

Hi

Your WHERE clause most probably is referencing a control on a form. For example,

strSQL="Select * from tblPeople where PeopleID= forms!SomeForm!PeopleID"

You have to remember that Jet does not know about Forms/Reports etc. So it tries to evaluate forms!SomeForm!PeopleID as a parameter instead of looking at the actual control itself. And your error message is the result.

The proper way would to concatenate the value returned by the referenced control. (Note: If you're not sure how to handle strings and dates in this manner, check out the article titled Use Variables in SQL behind forms)

strSQL="Select * from People where PeopleID=" & forms!SomeForm!PeopleID



From Andy Baron:

When a parameterized query is run in Access, Access provides an expression service that evaluates many parameters. For example, a reference to a control on an open form will be evaluated automatically, and the query containing that parameter will use the value contained in that control. This expression service is not available when you use VBA code to execute a parameterized action query or to open a recordset based on a parameterized query that returns records.

Your code must supply the exact parameter values to be used. This is done through the use of QueryDef objects. QueryDef objects have a Parameters collection containing Parameter objects that have a Name property, a Value property and an index in the collection. These objects and properties are used to supply the required parameter values.

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery&quot

'several syntaxes are possible
qdf(0) = Me!MyControl
qdf.Parameters(1) = 123
qdf![forms!frmParameters!txtNewDate] = #2/2/98#

Set rst = qdf.OpenRecordset(dbOpenSnapshot)
'or qdf.Execute dbFailOnError

With Access queries that contain paramters that are all references to Access controls on open forms, you can simulate the expression service that Access provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery&quot

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError


CUOK

 

[jsaliers]

Don't get me wrong here, but I am not sure I understand what you are trying to say. To the first part of the response, in my post, I did properly refer to the parameters, as you stated.

Set rs1 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & RequesterName.Value)
Set rs2 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & AssignedTo.Value)


As for the second part of your response, I have not a clue what you are trying to get at. I have self-tought myself Access, with the help of the people in these forums, and I am still somewhat new to this, so I am missing the point of what you were getting at with the second half of the response. Could you please explain how this applies to my post? I apologize for sounding ignorant, and I do appreciate your help.

Thank you in advance.

Jon

 

[cuok]

Hi!

in your:

"Set rs1 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & RequesterName.Value)"

Your WHERE clause most probably is referencing a control on a form so try somthing like this:
Set rs1 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & forms!SomeForm!RequesterName.Value)"

so for rs2.

good luck
CUOK

 

[jsaliers]

I made the changes that you indicated (sorry I didn't see that before!), but I still get the same error. From the error, it would appear that I am missing one of the parameters, but I am not sure which statement it is missing from, or what parameter is missing.

Thank you much,

Jon

 

[cuok]

try this:

Set rs1 = dbs.OpenRecordset("Select * FROM Ref_Employee WHERE Name = " & forms!SomeForm!RequesterName.Value)"

add: Dim strSQL as string
then:
strSQL = ("Select * FROM Ref_Employee WHERE Name = " & forms!SomeForm!RequesterName.Value)"
then:
set rs1 = dbs.OpenRecordset(strSQL, dbOpenDynaset)

All in DAO!
Good luck!
CUOK

 

[jsaliers]

I made the specified changes, but again, the same error. I think I am going to start trying the Outlook method as well as continuing to play with this one. I am still open to suggestions. If anybody knows how to send a task instead of an e-mail in Outlook, please help me.

Thank you for your help.

Jon

 

[cuok]

Once more:

"'" & forms!SomeForm!RequesterName.Value & "'"

i think its going to work

CUOK

 

[cuok]

Hi

maybe it not seen clear so:

" ' "

CUOK

 

[jsaliers]

It works! I am very pleased. Thank you. Two small questions...
(1)When I press the button and the code executes, a send message window pops up with the e-mail message, and I have to click on send in order to send the message. If I set the last parameter of the statement below to False, will this just send it without allowing the user to preview?

(2)I am glad I hit (1) at least once...There is an attachment to the e-mail, that I did not specify. It is a text file. However, I did not specify an atttachment. Any ideas?

Thank you again for all your help.

Jon

 

[cuok]

"If I set the last parameter of the statement below to False, will this just send it without allowing the user to preview?"

YES! EXAMPLE:

DoCmd.SendObject acSendTable, "TblName", acFormatXLS, "cuok@bezeqint.net", , , _
"Current Spreadsheet of TblName", , False

should do this.

2 to attach a text file add like this line:

.Attachments.Add "C:\Filename.txt"

or:

Dim objOutlookAttach As Outlook.Attachment

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If



Be sure to have the appropriate Outlook reference checked.
(in design view, tools, refernces)

good luck
CUOK