none
Error while trying to send email with Access "Microsoft Does not recognize" (Daniel Pineault) RRS feed

  • Question

  • Hey guys, I'm using this neat code by Daniel Pineault, with some slight modifications to send the email with delay and using a template. My issue is that when I call it, it pulls fine but I'm getting an error from outlook not recognizing e-mail addresses that exist when trying to send. The issues doesn't happen if I just place my personal email by itself, but I need to send this email to multiple individuals. Ideally I'd like to create a string and have it call from my distribution list in access or maybe even the distribution lists already in outlook. I've tried using just the e-mail, just the name, the name and email like [Last Name], [First Name] <email@email.com>, I have tried using the string and without the string. I've ran out of ideas here.

    '---------------------------------------------------------------------------------------
    ' Procedure : SendHTMLEmail
    ' Author    : Daniel Pineault, CARDA Consultants Inc.
    ' Website   : http://www.cardaconsultants.com
    ' Purpose   : Automate Outlook to send an HTML email with or without attachments
    ' Copyright : The following is release as Attribution-ShareAlike 4.0 International
    '             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
    ' Req'd Refs: Late Binding version  -> None required
    '             Early Binding version -> Ref to Microsoft Outlook XX.X Object Library
    '
    ' Input Variables:
    ' ~~~~~~~~~~~~~~~~
    ' sTo       : To Recipient email address string (semi-colon separated list)
    ' sSubject  : Text string (HTML) to be used as the email subject line
    ' sBody     : Text string to be used as the email body (actual message)
    ' bEdit     : True/False whether or not you wish to preview the email before sending
    ' sBCC      : BCC Recipient email address string (semi-colon separated list)
    ' aAttachments : Array of attachment (complete file paths with
    '                   filename and extensions)
    ' sAccount  : Name of the Account to use for sending the email (normally the e-mail adddress)
    '                   if no match is found it uses the default account
    '
    ' Usage:
    ' ~~~~~~
    ' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My <b>body</b>.", True)
    ' Call SendHTMLEmail("abc@xyz.com;def@wuv.ca;", "My Subject", "My <b>body</b>.", True)
    ' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My <b>body</b>.", True, , _
    '                    Array("C:\Temp\Table2.txt"))
    ' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My <b>body</b>.", True, , _
    '                    Array("C:\Temp\Table2.txt", "C:\Temp\Supplier List.txt"))
    ' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My <b>body</b>.", True, , _
    '                    Array("C:\Temp\Table2.txt", "C:\Temp\Supplier List.txt"), _
    '                    "cde@uvw.com")
    '
    ' Revision History:
    ' Rev       Date(yyyy/mm/dd)        Description
    ' **************************************************************************************
    ' 1         2007-11-16              Initial Release
    ' 2         2017-02-15              Added retention of default e-mail signature
    '                                   Added conditional compiler directives for early and
    '                                       late binding
    ' 3         2019-01-20              Updated Copyright
    '                                   Added usage examples
    '                                   Added sAccount option
    '---------------------------------------------------------------------------------------
    
    Option Compare Database
    
    Function SendHTMLEmail(ByVal sTo As String, _
                           ByVal sSubject As String, _
                           ByVal sBody As String, _
                           ByVal bEdit As Boolean, _
                           Optional sBCC As Variant, _
                           Optional aAttachments As Variant, _
                           Optional strSourceEMail As String, _
                           Optional sAccount As Variant)
        On Error GoTo Error_Handler
        '    #Const EarlyBind = 1 'Use Early Binding
        #Const EarlyBind = 0    'Use Late Binding
        #If EarlyBind Then
            Dim oOutlook          As Outlook.Application
            Dim oOutlookMsg       As Outlook.MailItem
            Dim oOutlookInsp      As Outlook.Inspector
            Dim oOutlookRecip     As Outlook.Recipient
            Dim oOutlookAttach    As Outlook.Attachment
            Dim oOutlookAccount   As Outlook.Account
        #Else
            Dim oOutlook          As Object
            Dim oOutlookMsg       As Object
            Dim oOutlookInsp      As Object
            Dim oOutlookRecip     As Object
            Dim oOutlookAttach    As Object
            Dim oOutlookAccount   As Object
            Const olMailItem = 0
        #End If
     
        Dim i                     As Integer
     
        Set oOutlook = CreateObject("Outlook.Application")
    
        If strSourceEMail = "" Then
        Set oOutlookMsg = oOutlook.CreateItem(olMailItem)
        Else
        Set oOutlookMsg = oOutlook.Session.OpenSharedItem(strSourceEMail)
        End If
     
        With oOutlookMsg
            .Display    'Had to move this command here to resolve a bug only existent in Access 2016!
            Set oOutlookRecip = .Recipients.Add(sTo)
            oOutlookRecip.Type = 1
     
            If Not IsMissing(sBCC) Then
                Set oOutlookRecip = .Recipients.Add(sBCC)
                oOutlookRecip.Type = 3
            End If
            .Subject = sSubject
            Set oOutlookInsp = .GetInspector    'Retains the signature if applicable
            .HTMLBody = sBody & .HTMLBody
            .Importance = 2    'Importance Level  0=Low,1=Normal,2=High
            .DeferredDeliveryTime = Format(Date + 1, "mm/dd/yyyy") & " 4:00:00 AM"
     
            If Not IsMissing(sAccount) Then
                For Each oOutlookAccount In oOutlook.Session.Accounts
                    If oOutlookAccount = sAccount Then
                        Set oOutlookMsg.SendUsingAccount = oOutlookAccount
                    End If
                Next
            End If
     
            ' Add attachments to the message.
            If Not IsMissing(aAttachments) Then
                If IsArray(aAttachments) Then
                    For i = LBound(aAttachments) To UBound(aAttachments)
                        If aAttachments(i) <> "" And aAttachments(i) <> "False" Then
                            Set oOutlookAttach = .Attachments.Add(aAttachments(i))
                        End If
                    Next i
                Else
                    If aAttachments <> "" And aAttachments(i) <> "False" Then
                        Set oOutlookAttach = .Attachments.Add(aAttachments)
                    End If
                End If
            End If
     
            For Each oOutlookRecip In .Recipients
                If Not oOutlookRecip.Resolve Then
                    oOutlookMsg.Display
                End If
            Next
     
            If bEdit = True Then    'Choose btw transparent/silent send and preview send
                '.Display
            Else
                .Send
            End If
        End With
     
    Error_Handler_Exit:
        On Error Resume Next
        If Not oOutlookAccount Is Nothing Then Set oOutlookAccount = Nothing
        If Not oOutlookAttach Is Nothing Then Set oOutlookAttach = Nothing
        If Not oOutlookRecip Is Nothing Then Set oOutlookRecip = Nothing
        If Not oOutlookInsp Is Nothing Then Set oOutlookInsp = Nothing
        If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing
        If Not oOutlook Is Nothing Then Set oOutlook = Nothing
        Exit Function
     
    Error_Handler:
        If Err.Number = "287" Then
            MsgBox "You clicked No to the Outlook security warning. " & _
                   "Rerun the procedure and click Yes to access e-mail " & _
                   "addresses to send your message. For more information, " & _
                   "see the document at http://www.microsoft.com/office" & _
                   "/previous/outlook/downloads/security.asp."
        Else
            MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Error Source: SendHTMLEmail" & vbCrLf & _
                   "Error Description: " & Err.Description & _
                   Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                   , vbOKOnly + vbCritical, "An Error has Occured!"
        End If
        Resume Error_Handler_Exit
    End Function

    This is my call:

    Dim strToEM1 As String
    
    strToEM1 = "testme.test1@abc123.com; testme.text2@abc123.com; testme.test3@abc123.com; testme.test4@abc123.com"
    
    SendHTMLEmail(strToEM1, "1W -  VAD Daily Audit - Please respond by 16:00", "", True, , , "C:\Users\jab03a\Desktop\TEST.msg")


    • Edited by InnVis Thursday, September 5, 2019 12:34 PM
    Wednesday, September 4, 2019 10:51 PM

Answers

All replies

  • Probably something in the copy-paste (at least what it seems)

    Set oOutlookRecip = .Recipients.Add(sTo)

    This line seems like a comment....check if its "working" and better put a breakpoint to see what exactly its setting

    Thursday, September 5, 2019 6:17 AM
  • It's pasting exactly what I'm putting down, don't understand the actual problem with outlook to be honest. See my post above I explained how it pastes things weird.
    Thursday, September 5, 2019 3:07 PM
  • Probably something in the copy-paste (at least what it seems)

    Set oOutlookRecip = .Recipients.Add(sTo)

    This line seems like a comment....check if its "working" and better put a breakpoint to see what exactly its setting

    Do you mean because the line is in green in the posted code block?  That's just an artifact of the forum, not the actual code in Access.  The code formatter sees the single-quote that indicates a comment in VB, and thinks that everything up to the next single-quote is a text literal that should be formatted with a special color.  It's not reflecting how the code is interpreted by Access at all. 

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, September 5, 2019 4:30 PM
  • Mr. Goldgar, do you have any thoughts on the issue?
    Thursday, September 5, 2019 5:29 PM
  • Your posted code to call the function is incorrect, and I'm guessing that it's not your actual code.  You have:

    SendHTMLEmail(strToEM1, "1W -  VAD Daily Audit - Please respond by 16:00", "", True, , , "C:\Users\jab03a\Desktop\TEST.msg")

    And it would have to be either

    Call SendHTMLEmail(strToEM1, "1W -  VAD Daily Audit - Please respond by 16:00", "", True, , , "C:\Users\jab03a\Desktop\TEST.msg")

    or 

    SendHTMLEmail strToEM1, "1W -  VAD Daily Audit - Please respond by 16:00", "", True, , , "C:\Users\jab03a\Desktop\TEST.msg"

    However, to get into the function at all, the calling code would have had to be correct, so that isn't the real problem.  What error do you actually get?  In my tests, if I leave out the template e-mail (which I can't make work), the function works fine.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, September 5, 2019 6:19 PM
  • For some reason when I copy/pasted the Call it didn't come through. I'm using it as you described on the first example. Anyways, here's a picture - better at explaining it than I am!

    and this is the code associated to that error:

    Call SendHTMLEmail("Jaime.Batista@xyz.com; Ruthlaarni.Alesna@xyz.com; marelise.grobler@xyz.com; Lyndsey.Perrine@xyz.com; Julissa.Soto@xyz.com", "GT7 -  VAD Daily Audit - Please respond by 16:00", "", False)

    Same thing happens if I use a string expression or if I add a ";" at the end of the last email. I think it's considering it one big email instead of separate emails... It doesn't work for multiple emails at all.


    • Edited by InnVis Thursday, September 5, 2019 7:12 PM
    Thursday, September 5, 2019 6:50 PM
  • Any chance your system delimiter is not ";" and is something else like ","

    Just enter a couple of emails by hand and check how they are separated.

    Thursday, September 5, 2019 7:44 PM
  • Did this, and my system (outlook in general) is ";"
    Thursday, September 5, 2019 7:46 PM
  • It looks like the error is in the step where recipient names are resolved against the address book.  Since your current list of recipients consists only of valid e-mail addresses, what happens if you temporarily comment out that step:

    '        For Each oOutlookRecip In .Recipients
    '            If Not oOutlookRecip.Resolve Then
    '                oOutlookMsg.Display
    '            End If
    '        Next
    

    ?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, September 5, 2019 7:49 PM
  • Just tried this and it did not work. Still getting the same error, so damn weird.
    Thursday, September 5, 2019 8:02 PM
  • Just tried this and it did not work. Still getting the same error, so damn weird.
    Hmm.  Outlook has an option to check names automatically, and I think that's probably enabled by default.  I don't know what version of Outlook you're using, but try clicking File -> Options -> Mail, scroll down to the options for "Send messages", and un-check the option "Automatic name checking".  Then try your modified code again.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, September 5, 2019 8:17 PM
  • Just tried this... Didn't work either. For some reason I think when the call pastes the "To" e-mail, it's pasting it and outlook is recognizing it as one single email. 
    • Edited by InnVis Thursday, September 5, 2019 8:35 PM
    Thursday, September 5, 2019 8:27 PM
  • Just tried this... Didn't work either.
    Just to verify:  you un-checked "Automatic name checking" and tried the code with the call to the .Resolve method commented out … and it didn't make any difference?  You still got the "Check Names" dialog?  That's baffling.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, September 5, 2019 8:34 PM
  • Yes sir, I went ahead and unchecked the "automatic name checking." I restarted my outlook just in case AND the part of the function that you mentioned earlier is commented out.
    Thursday, September 5, 2019 8:38 PM
  • Yes sir, I went ahead and unchecked the "automatic name checking." I restarted my outlook just in case AND the part of the function that you mentioned earlier is commented out.

    Well, I'm out of ideas for now.  Out of curiosity, does it still display the "Check Names" dialog if you run it with no template e-mail:

    Call SendHTMLEmail(strToEM1, "1W -  VAD Daily Audit - Please respond by 16:00", "", True)

    ?

    I ask because, as I mentioned, I am not experiencing this problem, and one of the ways your setup and mine differ is that I don't have a template e-mail.  I couldn't get that to work without more research in how to set it up than I have time to do at the moment.

    If leaving out the template e-mail doesn't many difference, then I can't help thinking there's some difference in our Outlook configuration or options that is causing it, but it will be hard to figure out what.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, September 5, 2019 9:22 PM
  • If you notice my example text with the picture I did not use a template. As a matter of fact the only work-around I have now is to create a template with the distribution list pre-set into it and null the string for the "To" or add just a single e-mail for it to work. It's very strange.

    Out of curiosity, once I figure with whole "To" issue out, do you know if there is a way I can turn my Template into an HTML and just use it directly on the code instead of using template emails? I tried viewing the source of my template in HTML, but it's a massive code that when I paste into the VBA code, it destroys it.

    Thursday, September 5, 2019 9:51 PM
  • I'm wondering if your regional settings require a different separator than the ;?

    Here's a modified version that breaks the recipients into individual entries and reports back errors in the immediate window when it tries to resolve them

    Function SendHTMLEmail(ByVal sTo As String, _
                           ByVal sSubject As String, _
                           ByVal sBody As String, _
                           ByVal bEdit As Boolean, _
                           Optional sBCC As Variant, _
                           Optional aAttachments As Variant, _
                           Optional sAccount As Variant, _
                           Optional ImportanceLvl As OutlookImportanceLevel = olImportanceNormal)
    10        On Error GoTo Error_Handler
              '    #Const EarlyBind = 1 'Use Early Binding
        #Const EarlyBind = 0    'Use Late Binding
        #If EarlyBind Then
                  Dim oOutlook          As Outlook.Application
                  Dim oOutlookMsg       As Outlook.MailItem
                  Dim oOutlookInsp      As Outlook.Inspector
                  Dim oOutlookRecip     As Outlook.Recipient
                  Dim oOutlookAttach    As Outlook.Attachment
                  Dim oOutlookAccount   As Outlook.Account
        #Else
                  Dim oOutlook          As Object
                  Dim oOutlookMsg       As Object
                  Dim oOutlookInsp      As Object
                  Dim oOutlookRecip     As Object
                  Dim oOutlookAttach    As Object
                  Dim oOutlookAccount   As Object
                  Const olMailItem = 0
        #End If
    
              Dim i                     As Integer
    
    20        Set oOutlook = CreateObject("Outlook.Application")
    30        Set oOutlookMsg = oOutlook.CreateItem(olMailItem)
    
    40        With oOutlookMsg
    50            .Display    'Had to move this command here to resolve a bug only existent in Access 2016!
    
    60            For Each recip In Split(sTo, ";")
    70                If Trim(recip & "") <> "" Then
    80                    Set oOutlookRecip = .Recipients.Add(recip)
    90                    oOutlookRecip.Type = 1
    100               End If
    110           Next recip
    
    120           If Not IsMissing(sBCC) Then
    130               For Each recip In Split(sBCC, ";")
    140                   If Trim(recip & "") <> "" Then
    150                       Set oOutlookRecip = .Recipients.Add(recip)
    160                       oOutlookRecip.Type = 3
    170                   End If
    180               Next recip
    190           End If
    
    200           .Subject = sSubject
    210           Set oOutlookInsp = .GetInspector    'Retains the signature if applicable
    220           .HTMLBody = sBody & .HTMLBody
    230           .Importance = ImportanceLvl    'Importance Level  0=Low,1=Normal,2=High
    
    240           If Not IsMissing(sAccount) Then
    250               For Each oOutlookAccount In oOutlook.Session.Accounts
    260                   If oOutlookAccount = sAccount Then
    270                       Set oOutlookMsg.SendUsingAccount = oOutlookAccount
    280                   End If
    290               Next
    300           End If
    
                  ' Add attachments to the message.
    310           If Not IsMissing(aAttachments) Then
    320               If IsArray(aAttachments) Then
    330                   For i = LBound(aAttachments) To UBound(aAttachments)
    340                       If aAttachments(i) <> "" And aAttachments(i) <> "False" Then
    350                           Set oOutlookAttach = .Attachments.Add(aAttachments(i))
    360                       End If
    370                   Next i
    380               Else
    390                   If aAttachments <> "" And aAttachments(i) <> "False" Then
    400                       Set oOutlookAttach = .Attachments.Add(aAttachments)
    410                   End If
    420               End If
    430           End If
    
    440           For Each oOutlookRecip In .Recipients
    450               If Not oOutlookRecip.Resolve Then
    460                   Debug.Print "Could not resolve the e-mail address: ", oOutlookRecip.Name, oOutlookRecip.Address, _
                                      Switch(oOutlookRecip.Type = 1, "TO", oOutlookRecip.Type = 2, "CC", oOutlookRecip.Type = 3, "BCC")
    470                   bEdit = True
    480               End If
    490           Next
    
    500           If bEdit = True Then    'Choose btw transparent/silent send and preview send
    510               .Display
    520           Else
    530               .send
    540           End If
                  'next 2 lines to create a draft
                  '410           .Save
                  '420           .Close (False)
    550       End With
    
    Error_Handler_Exit:
    560       On Error Resume Next
    570       If Not oOutlookAccount Is Nothing Then Set oOutlookAccount = Nothing
    580       If Not oOutlookAttach Is Nothing Then Set oOutlookAttach = Nothing
    590       If Not oOutlookRecip Is Nothing Then Set oOutlookRecip = Nothing
    600       If Not oOutlookInsp Is Nothing Then Set oOutlookInsp = Nothing
    610       If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing
    620       If Not oOutlook Is Nothing Then Set oOutlook = Nothing
    630       Exit Function
    
    Error_Handler:
    640       If Err.Number = "287" Then
    650           MsgBox "You clicked No to the Outlook security warning. " & _
                         "Rerun the procedure and click Yes to access e-mail " & _
                         "addresses to send your message. For more information, " & _
                         "see the document at http://www.microsoft.com/office" & _
                         "/previous/outlook/downloads/security.asp."
    660       Else
    670           MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                         "Error Number: " & Err.Number & vbCrLf & _
                         "Error Source: SendHTMLEmail" & vbCrLf & _
                         "Error Description: " & Err.Description & _
                         Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                         , vbOKOnly + vbCritical, "An Error has Occured!"
    680       End If
    690       Resume Error_Handler_Exit
    End Function
    Hopefully it will help shed some light on what is going on.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Friday, September 6, 2019 12:44 PM
  • When I create an email from scratch in outlook, I have to use ";" to separate the users. I think for some reason when it's pasted over into outlook from the function, Outlook is recognizing the string as one SINGLE email.
    Friday, September 6, 2019 12:45 PM
  • Try the revised code as it should at the very least report back which e-mail address is causing the issue.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by InnVis Friday, September 6, 2019 1:45 PM
    Friday, September 6, 2019 1:27 PM
  • OMFG it worked! That was bizarre, man thanks - I LOVE YOU (no homo)!
    Friday, September 6, 2019 1:45 PM
  • This has been beneficial for all.  Thanks to you, we identified a weakness in the function and I will now update my blog to reflect this so it benefits everyone.  Thank you for flagging it and working with me to find a solution!

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, September 6, 2019 1:51 PM