MS Access sending e-mail

Kinez101

New Member
Joined
Oct 18, 2016
Messages
23
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
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).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top