Automail from Access

[Lissa]

For those of you who have implemented auto emails from Access - I'm assuming it's necessary to have an instance of Access and Outlook open at all times in order for the emails to get sent... (??)
Is there any way to accomplish auto emailing without having to have either application open all the time?

Thanks

 

[wiremonkey22]

I use something like this but it only emails the person the .TO specifies not sure of your requirements but it works with out outlook being open however access must be open. make sure to reference Microsoft outlook 12.0 object library also the .display doesent send the e-mail it allows the person to write what ever they want i dont use .send because of security settings dont allow it
you can also pre write the email using the strbodytext if you wish i didnt in this example. Something like strbodytext = " blah blah" then use .body = strbodytext

Public Function emailme()
On Error GoTo err
Dim olapp As Outlook.Application
Dim olns As Outlook.NameSpace
Dim olfolder As Outlook.MAPIFolder
Dim olmailitem As Outlook.MailItem
Dim strbodytext As String
Set olapp = CreateObject("outlook.application")
Set olns = olapp.GetNamespace("mapi")
Set olfolder = olns.GetDefaultFolder(olFolderInbox)
Set olmailitem = olfolder.Items.Add("IPM.Note")
With olmailitem
.Subject = "Your subject here"
.To = "E-mail address goes here"
.Display
End With
Set olmailitem = Nothing
Set olfolder = Nothing
Set olns = Nothing
Set olapp = Nothing
Exit Function
err:
MsgBox err.Description
End Function

Sorry this dont answer how to without either open not sure if its possible or if this helps at all it just looked sad sitting there with no replies. Good Luck!

 

[Lissa]

Thanks for the reply wiremonkey. I was just curious to know what other people out there were implementing.
I have an automailer working... and I can schedule at task for it.. but I seem to be running into other issues. When Access uses Outlook to send the mail I encounter the MS Outlook prompt "A program is trying to automatically send e-mail on your behalf. Do you want to allow this?...." and I would have to click yes for the email to go through... but our IT dept is not crazy about ClickYes or Redemption to take care of the clicking yes. So I'm just working on another solution.

Thanks again!

 

[DCrake]

There are fixes about that bypass the security issue. If you do a search I am sure you will find a solution.

 

[HiTechCoach]

Thanks for the reply wiremonkey. I was just curious to know what other people out there were implementing.
I have an automailer working... and I can schedule at task for it.. but I seem to be running into other issues. When Access uses Outlook to send the mail I encounter the MS Outlook prompt "A program is trying to automatically send e-mail on your behalf. Do you want to allow this?...." and I would have to click yes for the email to go through... but our IT dept is not crazy about ClickYes or Redemption to take care of the clicking yes. So I'm just working on another solution.

Thanks again!

I like to communicate directly with an SMTP server to send the email. This avoids using an email client like Outlook.

For some sample code see:

Send Email with SMTP

 

[Lissa]

Hey Thanks for the suggestions...
HiTechCoach - I think I will try incorporating the SMTP code!

 

[Simon_MT]

The real advantage of SMTP over Outlook is that you don't fill up a users Sent box with your emarketing campaign.

I also use SMTP, the trick is to configure the users SMTP in IIS Management to your mail server. It does actually go through OutLook but it should Reverse DNS on it so that your emails aren't treated as spam.

Simon

 

[irish634]

I like to communicate directly with an SMTP server to send the email. This avoids using an email client like Outlook.

For some sample code see:

Send Email with SMTP

HitechCoach,

Do you run into any compatibility issues with Access 2000, 2003, and 2007?

I ask because on my work PC I have Access 2000. I can actually send email using CDO without specifying an SMTP server, etc like in your link. Bascally, I need no configuration, so the code below is disabled.

    'With objConfig.Fields
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        '.Update
   ' End With

Now when I get home, I have Access 2003. The CDO mod will not work unless I enable the code above. Once I do that, everything works normally.

I bring the db back to work (with the code enabled) and the code does not work.

Have you run into this before?

 

[HiTechCoach]

I have not has an issue between the versions of Access.

The different may be with CDO and if you are using an Exchange Server

I would assume that you don't have an exchange server one at home. Ids that correct?

Do you use an Exchange server at work?

 

[irish634]

I would assume that you don't have an exchange server one at home. Ids that correct?

Do you use an Exchange server at work?

Yes. We use an exchange server here at work. That makes sense now. I presume I'll have to modify my code a bit to accommodate both.

 

[HiTechCoach]

Yes. We use an exchange server here at work. That makes sense now. I presume I'll have to modify my code a bit to accommodate both.

Your VBA code is not the same as the code that I gave you a link to.

My code from the link I provided:

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" <me@my.com>"
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication."

'==This section provides the configuration information for the remote SMTP server.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.your.com"

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"

'Server port (typically 25) may have to change based on ISP or firewall
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

It always works as long as the Machine running the Access code has an internet connection.

Tested:
Laptop (no network) Outlook with ISP's SMTP servers.
Workstation on Win2000 Domain with Outlook configured for Exchange server.
Workstation On Win 29093 domain with Outlook configure Exchange server.
Terminal Server in a domain with Exchange server, with no Email client on Terminal Server.

Every test worked without any code changes and was going through a remote SMTP server to send the message every time.

I think there is an issue with your code. If you would like me to test it, please post all the VBA code you are using.

 

[vgersghost]

Lissa:

This is the code I have been using for SMTP for awhile and it works great and is simple.

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

Dim mail
Dim config
Dim fields
Dim strTo As String

strTo = "whoever1.com,Whoever2.com,"
strTo = strTo & "whoever3.com,Whoever4.com,"

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 'cdoSendUsingPort ' SMTP SERVER
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.224.0.15" ' IP OF SERVER
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With

Set mail.Configuration = config

With mail
.From = "whoever.com" 'strfrom
.To = strTo
'.CC = strCC
.subject = strSubject
.TextBody = strBody '" ****** Do Not Reply to this Email ****** " & Chr(13) & Chr(13) & strBody
'.AddAttachment (strAttachment)
.send
End With

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

End Function

 

[irish634]

Your VBA code is not the same as the code that I gave you a link to.

<<<snip>>>

Every test worked without any code changes and was going through a remote SMTP server to send the message every time.

I think there is an issue with your code. If you would like me to test it, please post all the VBA code you are using.

Ok... I'll go through it and see where I'm hanging up. If I get stuck I'll give you a shout.

Thank you!