Results 1 to 6 of 6

Thread: MS Access sending e-mail
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Access sending e-mail

    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?

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,933
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access sending e-mail

    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
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Oct 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access sending e-mail

    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

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,933
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access sending e-mail

    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  5. #5
    New Member
    Join Date
    Oct 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access sending e-mail

    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

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access sending e-mail

    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 by Micron; Oct 20th, 2016 at 12:58 AM.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •