Full Version: Automate Email in a Form
UtterAccess Forums > Microsoft® Access > Access Forms
miholmes
Good Morning,
Well first off thanks to the people who helped me yesterday. I just joined here yesterday and in less than 2 hours had my question addressed and fixed. So thank you for sharing your knowledge with me! =-)
Okay here's question #2.
I have a form that I enter technician jobs into. It contains the person who called in, their email address, their problem and a tracking number. I would like to find a way to generate an email, and have it sent to the person who called the job in after I flip to a new record.
Is this something that I can do?
Also I apologize if this should be in a different Forum. I thought I would start here.
adaytay
Hiya,
o you came back for more then... lol we'll have you addicted to this place yet!! (look, I've got loads of posts, can't stay away from the place!!)
You certainly can email from a form, that's no problem. What email system would you use? Outlook? Notes? Or something else?
Ad
miholmes
I use Microsoft Outlook 2003.
And THANKS! =-)
~Michael
markus
I am also looking for a VERY similar resolution in my database. I created a database to track Account requests and would like to create a button that when the user clicks on it autogenerates an e-mail to the emailfield with blah blah blah for the text of the message.
o not know how intergrated Outlook is with access. I used a generic button for mail and I am using it for reports (works great), but do not know about autogenerating e-mail.
Oused Access 2000 and Outlook 2000.
On a lighter note, thanks for everyones help on these boards. This is by far the best Access website I have found, everyone is helpful (even with people like myself who are not gurus).....but man I just love playing around with access (call me a nerd!).
Thanks,
Markus
adaytay
Ok then, this will be a challenge as I've only ever done 1-to-1 training before... lol
The first thing you need to do is to circumvent the inbuilot security that displays the message "A program is trying to send email on your behalf" - downloading and installing the "Outlook Redemption" dll file that bypasses the Outlook security settings. This is available from here - there is a free version that you can use, the only difference between the free and the paid version is that when you register the free version a dialog box appears.
Now onto the emailing... I use a public function to do the legwork for me, and just call it with the necessary information - here's the code I use (I'm using A2003, so Markus you should amend the line that says "CreateObject("Outlook.Application.11")" to [b]CreateObject("Outlook.Application.9")[b]
CODE
Public Function EmailMessage(strTo As String, strSubject As String, strBody As String, bnReadReceipt As Boolean, bnDeliveryReceipt As Boolean)
Dim Safemail As Variant, myOlApp, myItem, myRecipient, myBody
Dim myfolder, mynamespace, myAttachments, strDisclaimer As String
Set myOlApp = CreateObject("Outlook.Application.11")
Set myItem = myOlApp.CreateItem(0)
Set Safemail = CreateObject("Redemption.SafeMailItem")
Set Safemail.Item = myItem
Set myRecipient = Safemail.Recipients.Add(strTo)
strDisclaimer = vbCrLf & vbCrLf & "Please note that this email has been automatically generated by an internal system"
    With Safemail
        .Subject = strSubject
        .Body = strBody & strDisclaimer
        .ReadReceiptRequested = bnReadReceipt
        .OriginatorDeliveryReportRequested = bnDeliveryReceipt
        .Send
    End With
    
Set myOlApp = Nothing
Set Safemail = Nothing
End Function

Shout if you have any more questions, or have any difficulty implementing this frown.gif
Ad
markus
I implemented the code into my program (made some changes, do not need receipts), but when I run it comes up with an error that states Active X cannot create object (I already changed the 11 to a 9) and it points to the following line:
et Safemail = CreateObject("Redemption.SafeMailItem")
All my code is as follows:
Public Function EmailMessage(strTo As String, strSubject As String, strBody As String)
Dim Safemail As Variant, myOlApp, myItem, myRecipient, myBody
Dim myfolder, mynamespace, myAttachments, strDisclaimer As String
Set myOlApp = CreateObject("Outlook.Application.9")
Set myItem = myOlApp.CreateItem(0)
Set Safemail = CreateObject("Redemption.SafeMailItem")
Set Safemail.Item = myItem
Set myRecipient = Safemail.Recipients.Add(strTo)
strDisclaimer = vbCrLf & vbCrLf & "Please note that this email has been automatically generated by an internal system"
With Safemail
.Subject = strSubject
.Body = strBody & strDisclaimer
.ReadReceiptRequested = bnReadReceipt
.OriginatorDeliveryReportRequested = bnDeliveryReceipt
.Send
End With
Set myOlApp = Nothing
Set Safemail = Nothing
End Function
Any idea on what could be the issue?
Thanks,
Markus
miholmes
Good Morning!
kay this is an attempt, and I make no claim to be good with Access! LOL. That being said below you will find my script. I don't get an error message, I also don't get an email. =-) I attached this to a button with an On click event. Let me know what I've done wrong. =-)
Public Function EmailMessage(strTo As String, strSubject As String, strBody As String, bnReadReceipt As Boolean, bnDeliveryReceipt As Boolean)
Dim Safemail As Variant, myOlApp, myItem, myRecipient, myBody
Dim myfolder, mynamespace, myAttachments, strDisclaimer As String
Set myOlApp = CreateObject("Outlook.Application.11")
Set myItem = myOlApp.CreateItem(0)
Set Safemail = CreateObject("Redemption.SafeMailItem")
Set Safemail.Item = myItem
Set myRecipient = Safemail.Recipients.Add(strTo)
strDisclaimer = vbCrLf & vbCrLf & "Please note that this email has been automatically generated by an internal system"
strTo = [Email]
strSubject = “Technology Helpdesk – Job #” & [JobNumber]
strBody = "<h1>Thank you for contacting the Technology Helpdesk. We have received your request for assistance, and have entered it into our database. Below you will find information regarding your request. <p> Job Number: “ & [Jobnumber] & “</p> <p> Description: “ & [DESCRIPTION] & “</p> <p> Building: “ & [Building] & “</p> <p> Room: “ & [Room] & “</p> <p> Entry Date: “ & [EntryDate] &“</p> <p> To view the status of this job please visit http://intranet.cr.k12.ia.us/tech/Jobs/index.asp and choose your respective school. If you have further concerns please call the helpdesk at 558-2900. </p> <p> Thank you and have a great day! </p> <p> </p> <p> Technology Helpdesk </p> <p> Computer Services </p> <p> Cedar Rapids Community Schools </p> <p> </p>”
str
With Safemail
.Subject = strSubject
.Body = strBody & strDisclaimer
.ReadReceiptRequested = bnReadReceipt
.OriginatorDeliveryReportRequested = bnDeliveryReceipt
.Send
End With

Set myOlApp = Nothing
Set Safemail = Nothing
End Function
adaytay
Hi Markus,

You may need to check the reference to Microsoft Outlook Object Library is ticked in Tools > References (in the VBE Window). You might also want to ensure that the dll I gave you the link for is installed correctly; off the top of the head I *think* you may also need to register this as an additional reference in this window, too - I'll check once I'm back in the office. Let me know if you have further problems, sorry for the delay!

Ad
Edited by: adaytay on Tue Apr 12 19:16:57 EDT 2005.
SBTinPerth
Just a thought... do you have the Redemption library?
believe it needs to be installed on every machine that uses your database. The web site is:
http://www.dimastr.com/redemption/
Christopher Thomas
adaytay
Hi Christopher,
actually gave them the link to that in my second post on this thread.... but thanks for the assist.
There's actually a way to send email using SMTP so that you can use a specific "from" email address - PM me if you want more details and I'll send you the link.
Ad
adaytay
Hi Christopher,
When trying to send you a PM:
"This user is not accepting private messages."
Can you sort this then I can send you the link?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.