Sending Emails

[Russell Grice]

Hi, Im pretty new to VBA development and would like a little help !
Trying to send an email after a button on a form is pressed. Have a module with email code in it:

Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.SentOnBehalfOfName = strFrom
MyMail.To = strTo
MyMail.CC = strCC
MyMail.Subject = strSubject
MyMail.Body = strBody
MyMail.Send

This works fine but I get the confirmation message ' A program is trying to send an email on your behalf...' when each email is sent. Not very useful.

So I want to try using CDONTS to send the email.
I have some code which I nicked from an ASP pages which I send CDONTS emails from.

Set newmail = CreateObject("CDONTS.NewMail")
newmail.From = "rgrice@cornwall.gov.uk"
newmail.To = "ktwalker@cornwall.gov.uk"
newmail.CC = "rgrice@cornwall.gov.uk"
newmail.BCC = "cchirgwin@cornwall.gov.uk"
newmail.Body = strBody
newmail.Subject = "TEST Message"
newmail.Send
Set newmail = Nothing

Unfortunatly Im getting 'Active X Control cannot create object' I know its probably something very simple...!
Cheers in advance
Russell

 

[simongallop]

Look at DoCmd.SendObject

There doesn't have to be a report / query etc associated with the email.

ie:

DoCmd.SendObject , , , strTo, strCC, , strSubject, strBody

HTH

 

[Russell Grice]

Thanks for that !

It works fine, but still requires user intervention. Which I would like to avoid, if possible..

I may be able to get our email guru to change the security setting on our exchange server to allow MS Access to send email without confirmation being needed..

Thanks again.
R

 

[Treas]

Russell,

This problem occurs after implementing a security patch on Outlook and needs intervention on the Exchange server (search microsoft knowledge base for details)

HTH

Mary

 

[simongallop]

I also forgot that after strBody you need to type in a coma and then false. This is because the default setting is that the message is editable. False means send it immediately, don't want to view it etc.

So should be:

DoCmd.SendObject , , , strTo, strCC, , strSubject, strBody, False

HTH

 

[Russell Grice]

Take it using the DoCmd you cant specify a different user to send the mail from. Such as:

Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.SentOnBehalfOfName = "some1@somewhere"

Until you get the mail created for you and on screen. Or can you..?

 

[iangirven]

Russell

i believe you can create a table containing the email addressed eg "address" field

use some code to open the table and loop though the adress field, setting the "strTo" mentioned in harry's reply to the "adress" field in the table.

of course this will only work if the email adresses are in the table and you want to send the email to all of them.

if its a case of you wanting to change the email address prior to sending then im afraid youll have to allow the email to be set to edit with
DoCmd.SendObject , , , strTo, strCC, , strSubject, strBody, True

dunno if ive misunderstood your problem but hope this helps

ian

 

[Russell Grice]

Yeah thats fine, my To: string is built up from an email address field, I loop through a recordset adding to the string dependant on a number of criteria.

My main problem with using the DoCmd is that to send from a different user (which I have to do) I have to wait for the email to be created then manually change the From: field. The form that Im updating could concievably create 20 emails when a new record is saved (triggered from a button click event) and having to change 20 emails isnt really a smart enough solution.

Also using outlook (as I've mentioned above) to create emails means having to confirm each one. We have the Outlook Security Patch installed. I know (or think) that the exchange server security settings can be changed so that some programs dont need you to authorise these email. I have spoken to colleagues of mine and they aren't particulaly keen to go down this route.

This leaves me with trying to send by using CDONTS. But Im still getting my 'Activex Control cannot create object'. I know that I need to register my activeX control or put in a reference to it, but I dont know the name of the .ocx file in question, or how to write this in code.

Can anyone help ?
Cheers

Russell

 

[rich.barry]

This here
http://support.microsoft.com/support/kb/articles/Q161/0/88.ASP

is far better than the DoCmd.SendObject route.

 

[Russell Grice]

Thanks for your reply. As I've said further up the page, I've tried this, it works but I get the security message 'A program is trying to send an email on your behalf...' or something like that. You then have to authorise each email that gets sent which is a bit of a pain. Which Is why I wanted to go down the CDO for NTS route...

 

[DBL]

Although I haven't had this problem myself, I'm sure I've seen other posts in the forum that show that the problem is Outlook and not the SendObject. As far as I am aware, there isn't a way round it. Search the forum under Outlook and see what comes up.

 

[Russell Grice]

Finally got it working !!

Added in references for CDO libraries and ActiveX library(although not sure if thats needed) nicked some code from MS site.

Public Function SendEMail_CDO(strFrom, strTo, strCC, strSubject, strBody)

Dim mail
Dim config
Dim fields

Set mail = CreateObject("CDO.Message")
Set config = CreateObject("CDO.Configuration")
Set fields = config.fields

With fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 ' SMTP SERVER
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.1.14.64" ' IP OF SERVER
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With

Set mail.Configuration = config

With mail
.From = strFrom
.To = strTo
.CC = strCC
.Subject = strSubject
.TextBody = strBody
.Send
End With

Set mail = Nothing
Set fields = Nothing
Set config = Nothing

End Function

And it didnt require authorisation ! which I guess means it gets around the Outlook Security Patch !! YAY !

 

[DBL]

Well done, Russell!

 

[DW]

Hi all

Very interested in this, any chance of a demo.

DW

 

[Russell Grice]

Microsoft Knowledge Base article I got the idea/code from.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q286431

 

[PAH]

Are you doing this through Access?
I've been trying to find a way to have an e-mail created using variables.
As in - if an order changes send out an e-mail saying Order#[whatever order that changed] has changed, and possibly even attach a snapshot report.

 

[Russell Grice]

How would an order change ? If this is something trappable like a button click then you could write some code in the onclick event of that button to send your email.

You can send email at least three different ways from Access. You can use the DoCmd statement:

DoCmd.SendObject , , , strTo, strCC, , strSubject, strBody, False

You can save values to the string variables strTo, strCC, strSubject, strBody.

The 'false' means the email is sent automatically, true would create the email on screen for editing before sending. Note: dont think you can alter the From email address before the email is created.

The second way would be to use Outlook. You would need to register the 'Microsoft Outlook 10 object library' (well its 10 for me) done via the tools, references menu in the Access code window.

You would create a module called something like 'ModSendEmail' with code similar to the following:

Public Function SendEMail_Outlook(strFrom, strTo, strCC, strSubject, strBody)
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Set MyOutlook = New Outlook.Application
'This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' Reply to address
MyMail.SentOnBehalfOfName = strFrom
' This addresses it
MyMail.To = strTo
' This copies it to whoever
MyMail.CC = strCC
'This gives it a subject
MyMail.Subject = strSubject
'This gives it the body
MyMail.Body = strBody
'attachments
'MyMail.Attachments = "c:\somepath\somefile.???"
'Then send it!
MyMail.Send
'Cleanup after ourselves
Set MyMail = Nothing
End Function

Then to execute this code, in you onclick event you would write something like.

'Do your update record(s) code..

strSQL = "SELECT * FROM tbl_order where order_number = " & order_number & ";"
Set rs = db.OpenRecordset(strSQL)
rs.edit
rs("something") = somethingelse
rs.update
rs.close
set strSQL = nothing
set rs = nothing

'build up your strings

strTo = "someone@somewhere.com"
strFrom = "me@mydomain.co.uk"
strCC = "someoneelse@somewhere.com"
strBody = "Order number " & cstr(order_number) & " has changed..."
strsubject = "Updated Order"

' then send the email
strResponse = SendEMail_Outlook(strFrom, strTo, strCC, strSubject, strBody)

Or you could go down the CDO and SMTP route which is what I did (see the MS KB article).

Hope this helps..?


[This message has been edited by Russell Grice (edited 05-30-2002).]

 

[jatfill]

The most transparent mail control I've ever implemented was using SMTP and circumventing Outlook altogether. It took some time, but I have even at this point been able to send attachments, etc. from Access via SMTP.

The nicest thing about SMTP is that you don't have to rely on Outlook or any MAPI mail client to deliver your message... the only big hurdle I am in front of at the moment is dealing with servers that require a logon to send messages (not impossible though).

 

[NOL]

I OWE U !!!

I've been hunting for 1 whole week.. tried everything i cld possibly think of!!

Even contemplated win Socket programming !!!
and here it was , all the while , the simplest of all solutions !!!

Thanks a lot!!
Dunno what i'd do without this forum!!!

Gina!

 

[Stinger_]

Would any of U be so kind to send me some final code?

I tryed useing the snippets here, but to no avail...

I'm desperate..

Thx in advance