Results 1 to 11 of 11
  1. #1
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49

    eMail Addresses Not "Resolving" in Outlook

    I'm using this code provided by Daniel Pineault to automate the sending of emails with attachments.

    When I use the following:

    Code:
    Dim varRec As Variant
    Dim strSubject As String
    Dim strBody As String
    Dim varAttachment As Variant
     
    varRec = ""
    varRec = Array("Recipient1@Somewhere.org", "Recipient2@Somewhere.org","Recipient3@Somewhere.org","Recipient4@Somewhere.org")
    varAttachment = Array("C:\Users\whammett\Documents\TestAttachment.xlsx")
    strSubject = "TEST"
    strBody = "This email is testing new report distribution automation.  Please just delete, no other action is required."
    Call SendOutlookEmail(varRec, strSubject, strBody, False, , varAttachment)
    All email addresses resolve and are accepted by outlook.

    However, when I tried to do this:

    Code:
    Public Function GetEmailDistribution(strFacility As String, intReportID As Integer) As Variant
    Dim strSQL As String
    Dim rstRecipients As DAO.Recordset
    Dim strRec As String
    strSQL = ""
    strSQL = strSQL & "SELECT ReportUserEMail FROM tblReportUsers INNER JOIN tbxPFSReportUser ON tblReportUsers.ReportUserID = tbxPFSReportUser.ReportUserID"
    strSQL = strSQL & " WHERE [tbxPFSReportUser].[PFSReportID] = " & intReportID & " And [tbxPFSReportUser].[" & strFacility & "] = True"
    strSQL = strSQL & " ORDER BY tblReportUsers.ReportUserEMail;"
    Set rstRecipients = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
    strRec = ""
    If Not rstRecipients.EOF And Not rstRecipients.BOF Then
        Do Until rstRecipients.EOF
            strRec = strRec & rstRecipients(0) & "; "
            rstRecipients.MoveNext
        Loop
    End If
    GetEmailDistribution = Left(strRec, Len(strRec) - 2)
    And I change the code to:
    Code:
    varRec = Array(GetEmailDistribution,”SHB”,1)
    Outlook doesn’t seem to resolve the email addresses by the time the .Send command is initiated as I get a message box saying “Microsoft Outlook Does not Recognize” and then lists the email addresses until it runs out room for the characters. I click cancel and then tab out of the To field of the email and the addresses are recognized.
    If I step through the code and wait a moment or two before allowing the .Send commend to resume, the addresses resolve as well.



    Any help would be appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you see the notice at the bottom of the info at the link you mentioned?
    I don't know if it's relevant to your issue, but there is an update from Daniel

    Update*****

    Things have changed since the article was first published. If you are experiencing issues with the CreateObject(… line, then see my post entitled: CreateObject(“Outlook.Application”) Does Not Work, Now What?

  3. #3
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    I saw that. It isn't the createobject line where the problem is occurring.

    Thanks for the suggestion.

  4. #4
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Off to UA with this question.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I've had success by sending one email at a time instead of stacking them together. Some email recipients deem that method a privacy issue as all recipient email addresses are viewable to all recipients.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Best practice is to add the addresses to the BCC sent to field, this prevents anyone else from seeing them.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    You're using a comma to separate the email addresses in the original code and a semi-colon you separate them in the function. No idea if that's the problem, but it's the only obvious difference that I could see.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by Minty View Post
    Best practice is to add the addresses to the BCC sent to field, this prevents anyone else from seeing them.
    I had trouble with this method. If there is a bad email address in the BCC list, some SMTP servers will quit at that point and not send any of the remaining emails. The only notification you get is from the disgruntled recipients.

  9. #9
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    Quote Originally Posted by davegri View Post
    I've had success by sending one email at a time instead of stacking them together. Some email recipients deem that method a privacy issue as all recipient email addresses are viewable to all recipients.
    I get that, but that is not a concern here. The information is Personal Health Information and if anyone were to share it improperly, after they were fired, they'd probably be fitted for a nice orange jump suit. Also, some of these email distribution lists have 100+ persons on them. That would take quite a bit of time.

    Quote Originally Posted by Robyn_P View Post
    You're using a comma to separate the email addresses in the original code and a semi-colon you separate them in the function. No idea if that's the problem, but it's the only obvious difference that I could see.
    I think the code I posted was in different stages of testing when I actually posted it. I've been trying every combination of deliminators. It doesn't work if they are both commas or both semis. Thanks for looking.

  10. #10
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    The answer came for UA. There was an issue with my Function.

    Split Function was the key:

    Code:
    Public Function GetEmailDistribution(strFacility As String, intReportID As Integer) As Variant
    Dim strSQL As String
    Dim rstRecipients As DAO.Recordset
    Dim strRec As String
    strSQL = ""
    strSQL = strSQL & "SELECT ReportUserEMail FROM tblReportUsers INNER JOIN tbxPFSReportUser ON tblReportUsers.ReportUserID = tbxPFSReportUser.ReportUserID"
    strSQL = strSQL & " WHERE [tbxPFSReportUser].[PFSReportID] = " & intReportID & " And [tbxPFSReportUser].[" & strFacility & "] = True"
    strSQL = strSQL & " ORDER BY tblReportUsers.ReportUserEMail;"
    Set rstRecipients = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
    strRec = ""
    If Not rstRecipients.EOF And Not rstRecipients.BOF Then
        Do Until rstRecipients.EOF
            strRec = strRec & rstRecipients(0) & ", "
            rstRecipients.MoveNext
        Loop
    End If
    GetEmailDistribution = Split(Left(strRec, Len(strRec) - 2), ", ")
    End Function

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    That would take quite a bit of time.
    Not in Access. The emails would be queued within seconds in the Outlook Outbox and Access is done. I was mailing out more than that and it only took a few minutes to clear the outbox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-14-2016, 11:19 AM
  2. Replies: 4
    Last Post: 07-12-2014, 02:02 PM
  3. Replies: 1
    Last Post: 12-10-2013, 12:59 AM
  4. Replies: 8
    Last Post: 04-18-2013, 04:41 PM
  5. Replies: 2
    Last Post: 07-29-2011, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums