Programming Access to Send Emails?

[BMW150]

Hello All,



I am new to access and I was wondering if you could help with something.

I am trying to get Access so that it sends out an email when a specified date is reached (a column with future dates).
Even a single email sent at the end of every month with a list of items that had reach their specified dates that month.

I hope this information isn't too confusing, and I can try to clarify if I think of better wording.

Thanks!

[June7]

Use VBA to open a recordset of records that meet date criteria, loop through the recordset, send email. There are at least two ways to send email.

1. SendObject method

2. VBA opens an Outlook object and manipulates the object, review: https://www.accessforums.net/program...ook-21903.html

[rnodern]

When you use the outlook mail client programmatically via VBA, you will encounter a message box which asks the user to allow the usage of Outlook.

This is throws a pretty big spanner in the works, if you wish to completely automate a solution and not have pesky message boxes coming up.

That's where CDO comes in:

Using code similar to the below, you are able to avoid using your mail client and send Email Message objects straight to an SMTP server.

The below function, accepts 4 strings; the email body, the FROM email address, the TO email address and the SUBJECT. It then creates an email object, and sends it via the designated SMTP server

Function Send_Email(strBody as string, strFrom as string, strTo as string, strSubject as string)
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "1.2.3.4" 'This is the IP address of the SMTP server 
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
 
    With iMsg
        Set .Configuration = iConf
        .To = strTo
        .CC = ""
        .BCC = ""
        .From = strFrom
        .Subject = strSubject
        .TextBody = strbody
        .Send
    End With
 
    Set iMsg = Nothing
    Set iConf = Nothing
End Function

If you don't know the SMTP server IP address, you can get this from outlook by:
Tools -> Email Accounts -> view or change existing email accounts
Select the email account and click Change. The SMTP server address (typically DNS) will be in the Microsoft Exchange Server field.

[June7]

What I see in that box is a URL string, not an IP. Would be interesting to see if this possible on our network but I suspect IT has that locked out.

[rnodern]

Correct. You can always ping it to resolve the ip address. I thought that IT would have locked it down to, but it works here. As long as you use your own or an address you have access to on the exchange, it should work. I hope it does for you! Ive used that little function heaps of times.

[June7]

Okay, it works.

However, I have my Outlook set somehow so I don't get the warning message box so that also works fine but might be worth changing code anyway.

[rnodern]

I'm really glad that it worked for you. Thanks for the confirmation

[June7]

Darn. My code fails on these lines.

.BodyFormat = olFormatRichText

.Attachments.add (strZip)

I can probably do without the first but the second is essential.

These both work in my original code that uses Outlook.

[ItsMe]

June,

I have not used CDO to send .ZIP but use it all the time for SMTP attaching .PDF .DOC .TXT

I use the following for my CDO.Configuration. It adds the SMTP authenticate.

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

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "111.111.111.40"

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Automation@Domain.com"

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Passw0rd"

.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "ItsMe <DontSpamMe@Domain.com>"

.Update

Also, I use this for attachments in my CDO.Message

strFileName = "FileName"
strType = ".pdf"
strMyDirectory = "\\ServerName\MainFolder\SubFolder\GettingClos er\" & strShipOrder & strType

.AddAttachment strMyDirectory