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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Kinez101

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

Kinez101

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

Well-known Member
Joined
Jun 3, 2015
Messages
1,803
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:

Forum statistics

Threads
1,089,376
Messages
5,407,903
Members
403,169
Latest member
Luna17

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top