UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Send Email From Query Result - Multiple Recipient Addresses In Field, Access 2016    
 
   
RustyKY
post Feb 10 2020, 04:18 PM
Post#1



Posts: 16
Joined: 4-August 11



Hello -I've tried without success to put a screenshot in this post. having failed, i'll try and describe by objective.
Within WORD I am able to open a document - merge the data from an Access Query and then email individual personalized message to to the individual whose address is in the field PRIMARYEMAIL. My issue arises when i have records that have multiple addresses in the PRIMARYEMAIL field separated with;

The message I send is quite simple - but includes personalized information from fields retrieved in the query.
The Query Returns records - that includes multiple fields - I've attempted to lay out the query column results below

Thanks is advance for any guidance offered. - Sorry Failed to state my question?? Is it possible to do this in Access - with multiple recipients in the PRIMARYEMAIL field

Qry_ScheduledToShip
ARRIVED -Confirmed -DUE DATE -ITEM -Destination -PrimaryEmail
7/2/2019 -YES -2/16/2020 -ITEMNAME -SOMEBODY -Office@company3.com
11/16/2019 -YES -2/21/2020 -ANOTHER -SOMEONE -Somebody@aol.com
11/1/2019 -YES -2/19/2020 -SOMETHING -ANYONE -Clancy@company1.com; helen@company1.com
11/12/2019 -YES -2/17/2020 -ANYTHING -NOBODY -John@company2.com; jane@company2.com

Example of the message I am sending follows:
«DESTINATION»
Our records indicate that your item «ITEM», is available and is to be delivered «DUE_DATE»
Thanks,
My Name
This post has been edited by RustyKY: Feb 10 2020, 04:20 PM
Go to the top of the page
 
theDBguy
post Feb 10 2020, 04:24 PM
Post#2


UA Moderator
Posts: 77,492
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just to make sure I understand the question, are you asking how to separate the multiple emails, so you can send a separate email to each address?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RustyKY
post Feb 10 2020, 05:29 PM
Post#3



Posts: 16
Joined: 4-August 11



Thank you for the reply Dbugy
I am hoping to send each record row an individual email. I can accomplish this in WORD - until record with multiple email addresses are in the PRIMARYEMAIL field; example
the records with 1 email address in the field goes fine// but when it reaches the record row with 2 addresses in the field, I get the error (again this is in Word, so I am hopeful i can find the way to resolve it from within ACCESS
Thanks Again,
Rusty
Go to the top of the page
 
ADezii
post Feb 10 2020, 05:41 PM
Post#4



Posts: 2,757
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Just subscribing, will whip up some code for you as soon as I get a chance. It really didn't take that long so here it goes. I simulated Qry_ScheduledToShip by populating a Table (tblData) with the Data. I used Late Binding and made a couple of major assumptions based on the information that you provided. Here is the end result in Access:
  2. Sample Data:
    ARRIVEDConfirmedDUE DATEITEMDestinationPrimaryEmail
    2/2/2019YES2/16/2020ITEMNAMESOMEBODYOffice@company3.com
    11/16/2019YES2/21/2020ANOTHERSOMEONESomebody@aol.com
    11/1/2019YES2/19/2020SOMETHINGANYONEClancy@company1.com; helen@company1.com
    11/12/2019YES2/17/2020ANYTHINGNOBODYJohn@company2.com;jane@company2.com
  3. Code Definition:
    CODE
    Dim oLook As Object
    Dim oMail As Object
    Dim olns As Object
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("tblData", dbOpenForwardOnly)

    Set oLook = CreateObject("Outlook.Application")
    Set olns = oLook.GetNamespace("MAPI")

    Do While Not rst.EOF
      Set oMail = oLook.CreateItem(0)
        With oMail
          .To = Trim(rst![PrimaryEMail])
          .Subject = "E-Mail to " & rst![Destination]
          .Body = "Our Records indicate that your Item " & rst![Item] & ", is available and is " & _
                  "to be delivered " & rst![DUE DATE]
          .Display
          '.Send   'automatically Sends the E-Mail without displaying it
           rst.MoveNext
      End With
    Loop

    rst.Close
    Set rst = Nothing
    Set MyDB = Nothing

    Set oLook = Nothing
    Set olns = Nothing
    Set oMail = Nothing
  4. Hope this helps.

This post has been edited by ADezii: Feb 10 2020, 06:11 PM
Go to the top of the page
 
RustyKY
post Feb 10 2020, 07:01 PM
Post#5



Posts: 16
Joined: 4-August 11



Thank you for the reply Dbugy
I am hoping to send each record row an individual email. I can accomplish this in WORD - until record with multiple email addresses are in the PRIMARYEMAIL field; example
the records with 1 email address in the field goes fine// but when it reaches the record row with 2 addresses in the field, I get the error (again this is in Word, so I am hopeful i can find the way to resolve it from within ACCESS
Thanks Again,
Rusty
Go to the top of the page
 
RustyKY
post Feb 10 2020, 07:25 PM
Post#6



Posts: 16
Joined: 4-August 11



Thanks for the Reply and sample ADezii;
i'll give it a whirl and see what comes about (I know very little), have been fortunate to learn what I know by you and others helping me throughout the years.

Go to the top of the page
 
RustyKY
post Feb 10 2020, 09:58 PM
Post#7



Posts: 16
Joined: 4-August 11



Thanks Again ADezzi; it's likely obvious that I am trying to waddle may way through this (a programmer I am NOT:-)
to share a bit more information:

My Database is Equine.accdb
Table I am pulling records from is FEIMPT

Records are retrieved from this Parameter query I've named qryPostFoaling_Reminder_qry
SELECT FEIMPT.ARRIVED, FEIMPT.PREG, FEIMPT.[DUE DATE], FEIMPT.MARE, FEIMPT.FARM, FEIMPT.PrimaryEmail, FEIMPT.UTERINE, FEIMPT.[CULTURE 3], FEIMPT.BREED
FROM FEIMPT
WHERE (((FEIMPT.PREG)="yes") AND ((FEIMPT.[DUE DATE]) Between [Show Mares Due Between - First Date] And [Enter Second Date]) AND ((FEIMPT.UTERINE) Is Null) AND ((FEIMPT.[CULTURE 3]) Is Not Null) AND ((FEIMPT.BREED)="THBRED"));

Example of Records retrieved from the above query (for this example I have converted to EXCEL Table): NOT SURE IF I'VE GOTTEN THIS UPLOADED SO YOU CAN SEE OR NOT
The table has a total of 3 Records in individual rows: Field Names are ARRIVED, PREG, [DUE DATE], MARE, FARM, PRIMARYEMAIL, UTERINE, [CULTURE 3], BREED
Attached File  QryPostFoaling_Reminder_qry.jpg ( 99.03K )Number of downloads: 3


MY GOAL IS to send an individual message to each record's PRIMARYEMAIL address
As I made mention before - I can to some extent Execute the process from WORD or ACCESS -by running the query, saving the result ->Word Merge ->Link Data To Existing Word Document -> Selecting My Word Document -> After the Document Opens > Go To Mailings > Finish & Merge > Send Email Messages

The process executes and is successful where the PRIMARYEMAIL Field has only 1 email address, but fails to execute when the record row has 2 email addresses in the field separated with semicolon



Go to the top of the page
 
theDBguy
post Feb 10 2020, 10:05 PM
Post#8


UA Moderator
Posts: 77,492
Joined: 19-June 07
From: SunnySandyEggo


Hi Rusty. Here's what I understood from your answer to my question. If the primary email has more than one email address in it, you still only want to send out one email for that record. The next question is, then, to which email address (out of the many)?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Feb 10 2020, 11:05 PM
Post#9


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


Which email do you want? If you just want one, could do something like get all characters to the left of the ";" character. That will get the first one.

Not sure what email client you are using, but the semi colon should work for multiple addresses..

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ADezii
post Feb 11 2020, 08:38 AM
Post#10



Posts: 2,757
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I missed the '1 E-Mail for multiple addresses' part so I modified the Code to Send (Display in Demo) each E-Mail to a single Address. In the case of multiple addresses, it will be sent to the 1st one listed. You can Send to the 2nd, 3rd, 4th, etc. if they exist, but this would require additional Code. I also figured that a Demo would be very useful in your case, so I've attached one.
  2. Modified Section of Code:
    CODE
    '************************* CODE INTENTIONALLY OMITTED *************************
    Do While Not rst.EOF
      Set oMail = oLook.CreateItem(0)
        With oMail
          If UBound(Split(rst![PrimaryEMail], ";")) = 0 Then     '1 Recipient
            .To = Trim(rst![PrimaryEMail])
          Else
            .To = Trim(Split(rst![PrimaryEMail], ";")(0))     'Multiple Recipients, Send to 1st
          End If
            .Subject = "E-Mail to " & rst![Destination]
            .Body = "Our Records indicate that your Item " & rst![Item] & ", is available and is " & _
                    "to be delivered " & rst![DUE DATE]
            .Display
            '.Send   'automatically Sends the E-Mail without displaying it
             rst.MoveNext
      End With
    Loop
    '****************************************************************************
  3. Make required changes to the Demo to suit your specific needs.

This post has been edited by ADezii: Feb 11 2020, 08:41 AM
Attached File(s)
Attached File  Multiple_EMails.zip ( 22.82K )Number of downloads: 1
 
Go to the top of the page
 
RustyKY
post Feb 11 2020, 10:10 AM
Post#11



Posts: 16
Joined: 4-August 11



Again much appreciated - it'll be a bit later in the day before I can take a look
Ideally - what I am trying to do is send a single message to individuals individuals in the TO: including any and all addresses in the PrimaryEmail field.
As an Example using the records included in the previous screensnip from the query I ran

This Message: Note that I've enclosed with brackets, the query fields for this example.
[MiddleBrook Farm] - The [Thbred] mare [ACOMA] imported on [7/2/2019] is due to foal [2/16/2020]. The purpose of this message is to serve as reminder that [ACOMA] will need a post foaling Negative uterine swab culture to qualify for live cover this year.
The message would be sent to [Office@middlebrookequine.com] (the address in the primaryemail field)

THIS MESSAGE:
[Ford Stud]- The Thbred mare [HAPPILY (IRE)] imported on [11/1/2019] is due to foal [2/1/2020]. The purpose of this message is to serve as reminder that [HAPPILY (IRE)] will need a post foaling Negative uterine swab culture to qualify for live cover this year.
The message would be sent to [cclancy@coolmore.com; holeary@coolmore.com] (the addresses in the primaryemail field separated by a semicolon).
I have no real need to preview the message before being sent (as I will have already reviewed the data fields).

Any guidance appreciated, in the interim I can simply send individual messages to the different addresses if more that one address is included in the PrimaryEmail field
Rusty


Rusty

Go to the top of the page
 
ADezii
post Feb 11 2020, 10:50 AM
Post#12



Posts: 2,757
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
The message would be sent to [cclancy@coolmore.com; holeary@coolmore.com] (the addresses in the primaryemail field separated by a semicolon).

Then, Post# 4 should do the trick.
Go to the top of the page
 
RustyKY
post Feb 11 2020, 06:29 PM
Post#13



Posts: 16
Joined: 4-August 11



YOU ARE RIGHT ON - POST 4 DID PERFECT
VERY MUCH APPRECIATE YOUR WILLINGNESS TO HELP ME AND ALL THE OTHERS
Rusty
Go to the top of the page
 
ADezii
post Feb 11 2020, 06:49 PM
Post#14



Posts: 2,757
Joined: 4-February 07
From: USA, Florida, Delray Beach


Good Luck with your Project. thumbup.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th February 2020 - 03:39 PM