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.

 

[quest4]

Thank you again for the response, Daniel. You are absolutely correct and Office 2000 and as far as W2K goes, it does not have one CDo in it, it has four. Thank you for everything and have a great day.

 

[AkaMarcel]

The post for the OutletRedemption by MP9 on 02/23/2003 saved my behind.

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/

I accidentally installed the Outlook security patch when installing SR3 and I almost jumped out the window. This add in application is great. 3 different lines of code and it avoided the crippling effects of the pop-up authorization.

THANK YOU, THANK YOU, THANK YOU

 

[famehrie]

This thread keeps going and going and going...

I had asked a question in this thread back in September but never got a response so I'll try again.

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
Kevin

 

[adamroof]

famehrie...
I was looking for same thing with no luck.

What i did was create a Signature Table and then appended to code calling that signature.

strBody = strBody & vbCrLf & vbCrLf & vbCrLf & me.sigDesc (or in my case see below)

Creating a field somewhere might be easier to call, but i wanted users to edit the sig as neeeded easier.

If the recordset is not open you'll add this...
(i have only one sig in the table)
****
Dim strSig, strBody As String
Dim db as DAO.Database
Dim rst as DAO.Recordset

set db = CurrentDB
set rst = db.OpenRecordset("SELECT sigDesc FROM tblSig WHERE sigID = 1&quot

rst.Edit
strSig = rst!sigDesc
strBody = strBody & vbCrLf & vbCrLf & strSig

rst.Update
rst.Close
set rst = nothing
db.Close

****
Might be overkill, but i got it to work in my situation. I'll keep track of other responses to your question to find out any easier way!

Good luck!

 

[sklambert]

Hello,

This thread has been very helpful and informative. I am trying to set up a command button to send email, and I got the error which was mentioned in the first few posts:

"User-defined type not defined" Once I click ok the debugger highlights this section of the code "Dim objOutlook As Outlook.Application" in blue and this section of the code "Private Sub Command34_Click()" in yellow,"...

I tried to add a reference to Microsoft Outlook, as stated in the solution to this, but Microsoft Outlook was not in my list of choices to check off. Please let me know if there is anything I can do to solve this. I am using Access 2000.

 

[danvlas]

Use 'late binding':

Dim objOutlook As Object

It has been answered somewhere above...

But if you don't have Outlook on the system, you'll get a run-time error (429 if I recall well: ActiveX component can't create object)




Daniel Vlas
Systems Consultant

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

 

[quest4]

Sklambert, in the end I used just DoCmd.SendObject and it pickup Outlook on its' own, so to speak. I stop using the Dim and everything started to fly for me. Hope that helps. Good luck and have a good day.

 

[sklambert]

OK. This is valuable. But now I am trying to test this on a machine that only has Outlook Express and it's not working. I can test this later on a machine that has the full blown version. But for now, I am trying to use SendObject, using Netscape 7.1. I have the following code:

'****begin code****

Private Sub SENDEMAIL_Click()

Dim ORIGIN, DESTINATION, SENDTO, SUBJECT, MESSAGE As String

ORIGIN = "ebay2628@yahoo.com"
DESTINATION = [EMAIL ADDRESS]
SENDTO = [EMAIL ADDRESS]
SUBJECT = "Your item has been shipped."
MESSAGE = "We have shipped your item. The tracking number is " & [TRACKING NUMBER] & "."

DoCmd.SendObject acSendForm, , acFormatTXT, SENDTO, , , SUBJECT, MESSAGE, True

End Sub

'*****end code********

What happens is I get a window to send a new message in Netscape, and the SUBJECT and body (MESSAGE) are populated correctly, but not the send to email address (SENDTO). The From field in the email is defaulting to the settings in Netscape, which I can live with. I was hoping to use the "ebay2628" email address, so the user could reply to that address, no matter which email account I use, but if that cant be done, it wouldn't be an issue. Any help would be appreciated.

 

[danvlas]

DoCmd.SendObject , , , [SENDTO], , , [Subject], [Message], True

That should work.

No need for acForm since you're not passing form name. Also, no need for acFormatTxt, for the same reason.
Can't change From address using SendObject.
If Access 2000, you may encounter the most annoying bug with this Access version: first email goes fine, the others not (and not even an error message pops up).




Daniel Vlas
Systems Consultant

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

 

[sklambert]

Thanks for the input. However, the modified code still does not fill in the SEND TO field in the email. I get the same results (Subject and body populated, but not the recipient's email address). Also, now I get an "illegal operation", and Access shuts down after the new mail message is created in Netscape.

 

[sklambert]

Hello again,

I am now trying to use Outlook to send mail. I am using the following code:

'******begin code*****

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

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

'**************************************************************
'*create string with email address

strEmail = [EMAIL ADDRESS]

strBody = txtTDate & Chr(13) & Chr(13)
strBody = strBody & "Dear " & [FIRST NAME] & " " & [LAST NAME] & "," & Chr(13) & Chr(13)
strBody = strBody & "Your item was shipped today." & _
" Please let us know when you receive it." & Chr(13) & Chr(13) & Chr(13)
strBody = strBody & "Shipping method: " & [SHIPPING METHOD] & Chr(13)
strBody = strBody & "Date shipped: " & [DATE SHIPPED] & Chr(13)
strBody = strBody & "Tracking / Confirmation number: " & [TRACKING NUMBER] & Chr(13)
strBody = strBody & "Sincerely," & Chr(13) & Chr(13)
strBody = strBody & "Acme Corporation"

'***creates and sends email
With objEmail
.To = strEmail
.SUBJECT = "Your item has been shipped"
.body = strBody
.Send
End With


Set objEmail = Nothing
objOutlook.Quit

Exit Sub

End Sub
'****end code*****

When I try to send mail, I get the following error:

"Run-time error '-1975500795 (8a404005)':

Outlook does not recognize one or more names"

In the debug screen, the .Send is highlighted in yellow. What am I doing wrong?

 

[tandromp]

I have a form that has client information in it and I have it so that when the user clicks a button that emails them certain information. However I am having trouble with the format of the email. What would be ideal is if I could set it up so that when they open the email, all they see is what the one record would look like on the form. If it was just a picture that would be excellent. If this can't be done how would I insert a picture into the email. Also how do I do general formating such as changing the font size, color, and making it bold, italic... Thanks for any help

 

[danvlas]

Build a html having the format you want and...

.HTMLBody = strHtmlCode




Daniel Vlas
Systems Consultant

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

 

[JanCMar]

Hello
This thread has been great! Thank you.
I also am writing an application in Access which sends emails, using SendOjbect, working, (with lots of help from this site).
However, the security thing is a pain. Yes, I've used a little app called Express ClickYes, and that works beautifully.
But... now I'd like to explore other options. Can anyone explain to me the differences between CDO, MAPI, SMPT, SendObject, Outlook.Application etc. Particularly whether they use the security of Outlook, or do they bypass Outlook altogether? I found an example using MAPI and see that it also uses Outlook security.
Has anyone done anything with CDO or SMPT?
Any help would be greatly appreciated.
Thanks again for this great thread.

 

[Adams]

Maybe this information is in this great thread and I have missed it. My question is I have several offices that I email information to depending on the office number. Can I create a macro or SQL Statement that would pull the office number, create the report and send an email w/the attachment. If so, do I need to create several queries that has that office number in the criteria?

Thanks
Karen

 

[vndirect]

First, I'd like to thank you everyone for excellent contributions to this thread.

I have a small question: When I use the code to send email using Outlook from an Access database, the function works fine. I use ".display" to see the email before sending out. However, when the code is run, the active window becomes in Access, not in Outlook, even though the email is displayed in the background. I have to click on the Outlook window to switch to the email. Can anyone help me find a command line that can keep the control on the Outlook Email , then will go back to Access after I send the email?

Thanks a lot in advance.
Peter

 

[danvlas]

Karen:

You can base the report on a parameter query.
The parameter should be provided by a form based on office numbers.
A command button would start a loop through the records in the form and send the report for each and every record.

Or...you can click on the link below my signature...

HTH


Daniel Vlas
Systems Consultant

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

 

[tronc11]

Hi all - I have followed this thread through and am so close to getting my issue to work - but now my head hurts. I have a subform that lists training course attendees (in a continuous form) and their email addresses. I want to be able to send an email to just the listed attendees, but can't seem to get it to loop through just the 3 or 4 attendees in my list. It sends to the first attendee but not the rest, any hints on the loop part of all this code - I can't see anything similar in this thread.

thanks in advance.

Tronc

 

[Adams]

Thanks for the link to the AutoMailer that you have created. I installed the version AutoMail97 and I am receiving an error: You must install the DnARAMRef2K file to run the program. Where would I find this file?

Thanks
Karen

 

[blackbri]

This has been one of the most useful threads I have ever found while trying to solve a problem. Thank you nathan1967 and danvlas!

My question is that due to the success of my emailing database there have been more requests. Since I am using Pdf995 to change my report to Pdf then email it out through outlook, I must set pdf995 to save the file with the same name every time. So all of my code reflects looking for a static file. I would like to be able to change the name of that file when I am sending it out via another database.

Example: Original database was sending a score card to suppliers for our company so file is Supplier Score Card.pdf

The new database is sending a budget report monthly to upper management so pdf995 is still going to auto save it as Supplier Score Card.pdf but I want to change the name of it to Budget Report.pdf at time of sending.

Any help would be appreciated. BTW, I am using Access as my database and sending through Outlook.

Thank you,