MS Access sending e-mail

[Kinez101]

Current process is selecting individuals manually to send e-mail. I'd like to use Access to somewhat automate the process to send static e-mail (except for date update) to different individuals. I have 4 tables set up:
tblDate - contains calendar thru 12/31/2025
tblEmail - would contain receivers emails and cc information
tblEmailDefault - contains generic message
tblEmailRecip - temporary table where I can dump information from report and use information from the table to select individuals to send e-mail.

Example of e-mails:

Good Morning,

Can you please provide your Total Deposit amount with the breakdown of cash and checks ASAP(no later than 2 hours), so the proper adjustments can be made.

10/17/16 (changes)
Total Deposit:
Cash:
Checks:
Over/Short (Reason):
Open Account: (if Any)

**Please be advised, we have transferred to a new system and all variances need to be corrected by 10am (Central Standard Time) the following day.

What is the best way to accomplish such using MS Access/Outlook?

 

[alansidman]

Here is a tutorial video from Mike Alexander that shows you how. Note that he is using an older version of Access in this video, but the process is basically the same.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

 

[Kinez101]

I rather send it as a group with TO and CC fields instead of individual e-mails. Any ideas what I could change in the code to do so?

This is the code I have so far:

Function SendMyEmails()
Dim MySet As ADODB.Recordset


Set MySet = New ADODB.Recordset
MySet.Open "tblSendEmailTo", CurrentProject.Connection, adOpenStatic

Do Until MySet.EOF

DoCmd.SendObject , "", "", MySet![Store_Leader], MySet![Loss_Prevention], "", "", "", True, ""

MySet.MoveNext
Loop


End Function

 

[alansidman]

Microsoft Access Email FAQ

 

[Kinez101]

I came up with this code. CC field is same as TO field. How do I get it to change?

Option Compare Database


'------------------------------------------------------------
' SendMyEmails
'
'------------------------------------------------------------
Function SendMyEmails()
Dim rst As DAO.Recordset
Dim SL
Dim LP


Set rst = CurrentDb.OpenRecordset("tblSendEmailTo")

Do Until rst.EOF

SL = SL & rst("Store_Leader") & ","
LP = LP & rst("Loss_Prevention") & ","

rst.MoveNext

Loop

SL = Left(SL, Len(SL) - 1)
LP = Left(LP, Len(LP) - 1)


DoCmd.SendObject , "", "", SL, LP, "", "", "", True, ""

rst.Close
Set rst = Nothing

End Function

 

[Micron]

Try DoCmd.SendObject acSendNoObject , "", "", SL, LP, "", "", "", True
IIRC, you do not need the ending double quotes when the last optional parameter is not used.
Shouldn't your recipient lists be separated by semicolons and not commas?
EDIT: I highly recommend that every module have OPTION EXPLICT as well (adding it after the fact does not provide the same benefits).