Outlook does not recognize one or more names error when sending mass email via VBA

I have some VBA code that is executed from Access that loops through a recordset and builds about 400 email messages. With each iteration of the loop, some parameters are passed to a function that creates an Outlook object to send the message. If I run this code and allow Outlook to display the message and send each one manually, it works fine. But if I include the "send" command in the code, I get the message that "Outlook does not recognize one or more names".

I believe this is occurring because VBA is attempting to send the email before the Exchange server has had time to resolve the email address to a contact. I am not using names, but actually using the full email address. I tried turning off "Automatic Name Checking" and "Suggest names while compleing To,Cc, and Bcc fields" in Outlook, but this had no effect. Any ideas?
NashVegasAsked:
Who is Participating?
 
NashVegasConnect With a Mentor Author Commented:
Omgang, thank you for all of your help. I apologize that I abandoned this for so long as other matters became pressing. I've played around with your suggestion but I still get the error. It seems that Exchange server wants to find the contact in our Exchange server database (about 30,000 people) and replace the email address with the person's name. I don't know anything about Exchange, so I don't know if this is a local setting or a global one. As I mentioned in my original post, turning off name checking locally did not resolve.

The only way I could resolve this was to add a 10 second pause between the ".Save" and the ".Send" lines. This is an unsatisfacory workaround because this email message has to come from a high level executive who does not want to sit and watch 400 emails get sent, one every 10 seconds. Does anyone have another suggestion?
0
 
omgangCommented:
Can you post the main routine and the function?
What version of Access?
OM Gang
0
 
NashVegasAuthor Commented:
I'm using Office 2007 (Access and Outlook).
Option Compare Database
Option Explicit

Sub SendMessage(DisplayMsg As Boolean, ByVal msg As String, ByVal recip As String, ByVal ccRecips As String, chkNa As Boolean)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        
          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add(recip)
              objOutlookRecip.Type = olTo

              ' Add the CC recipient(s) to the message.
              If ccRecips <> "" Then
                Set objOutlookRecip = .Recipients.Add(ccRecips)
                objOutlookRecip.Type = olCC
              End If
              
              ' Blind Copy Occ Health if message contains any N/A issues.
              'If chkNa Then
              '  Set objOutlookRecip = .Recipients.Add("")
              '  objOutlookRecip.Type = olBCC
              'End If
              
             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Important OH Compliance Information for your Protocol Personnel"
             .Body = msg
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             Dim AttachmentPath As String
             AttachmentPath = "C:\Temp\OHC Interface Handout.pdf"
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add(AttachmentPath)
             Else
                MsgBox "You need to create the C:\Temp directory and save the OHC Interface Handout PDF file and then restart this routine"
                Exit Sub
             End If

             ' Resolve each Recipient's name.
              For Each objOutlookRecip In .Recipients
                  objOutlookRecip.Resolve
              Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
End Sub

Open in new window

0
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
omgangCommented:
NashVegas, I have a similar routine I used to use for broadcasting messages to a list of recipients.  In my routine, I commented out this part

             ' Resolve each Recipient's name.
              For Each objOutlookRecip In .Recipients
                  objOutlookRecip.Resolve
              Next

Give that a try with a small list of recipients and see if that works.
OM Gang
0
 
NashVegasAuthor Commented:
OM,
Thanks for the suggestion. Commenting out these lines had no affect unfortunately.
0
 
omgangCommented:
The only other thing different that I'm seeing is that my procedure destroys the object variables.  You might try destroying the object variables in your procedure and see if that makes a difference.  I've used mine in Access 2000 and 2003 with Outlook 2000 and 2003.  Never tried it in 2007 though.  OM Gang

Exit_SendMessage:
        'clear object variables
    Set olOutlookAttach = Nothing
    Set olOutlookRecip = Nothing
    Set olOutlookMsg = Nothing
    Set olOutlook = Nothing
    If Not rs Is Nothing Then rs.Close
        Set rs = Nothing
    Set db = Nothing
    Exit Sub
0
 
NashVegasAuthor Commented:
The error occurs right at the ".Send" line, so this happens before the objects are cleared. But just for kicks I went ahead and killed the msg, recip, and attach objects as well but this had no affect.
0
 
omgangCommented:
Here's the sub that worked for me.  You'll notice that I declare and instantiate an Outlook Inspector object.  I don't really recall why except that when I began the project (quite a few years ago) I may have been working with Outlook configured to use Word as the message editor and anticipated needing it.  I have not tried running the routine in Access 2007 with Outlook 2007 but may be able to do so tomorrow.
The pause every 300 messages (I have used the routine to send up to 17,000 messages) was to allow the Outlook Outbox to empty and prevent the mailbox from filling up.
OM Gang
Sub SendMessage(Reg As String)
On Error Resume Next

        'declare and open instance of MS Outlook
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim olOutlook As New Outlook.Application
    Dim olOutlookMsg As Outlook.MailItem
    Dim olOutlookRecip As Outlook.Recipient
    Dim olOutlookAttach As Outlook.Attachment
    Dim olInspector
    
    Dim strMsgBody As String, strAttachmentName As String
    Dim strRecipList As String, strAttachmentPath As String
    Dim strTemplateName As String, strTemplatePath As String
    Dim strSubject As String
    
    Dim blBuildBody As Boolean, blAttachment As Boolean
    Dim blUseStationary As Boolean, blUseTemplate As Boolean
    Dim blDisplayMsg As Boolean
    
    Dim intCounter As Integer
    
    Dim sglStartTime As Single
    Dim sglPauseInterval As Single
    
'configuration--------------------------------------------

        'message body
    'blBuildBody = True          'call function to build message body
    blBuildBody = False         'don't build message body
    
        'attachments
    'blAttachment = True         'include attachment
    blAttachment = False        'don't include attachment
        'assign attachment path to string variable
    strAttachmentPath = "P:\OMGang\Attachments\"
        'assign name of attachment to string variable
    'strAttachmentName = "TheAttachment.pdf"
    
        'message template
    'blUseTemplate = True        'use pre-built Outlook template
    blUseTemplate = False       'don't use template
        'assign template path to string variable
    'strTemplatePath = "P:\OMGang\Templates\"
    'strTemplateName = "Template.oft"
    
        'message stationary
    blUseStationary = True      'use default Outlook stationary
    'blUseStationary = False     'don't use stationary
    
        'message subject
    strSubject = "This is the Subject"
    
        'display message option
    'blDisplayMsg = True         'display message before sending
    blDisplayMsg = False        'don't display
    
        'assign name table or query containing the recipient list
    strRecipList = "qryRecipients"
    
        'initialize counter variable
    intCounter = 0
    
        'used to pause processing so that Outlook can catch up
        'set length of pause in seconds
    sglPauseInterval = 120
    
'end configuration----------------------------------------
    

        'build message body?
    If blBuildBody = True Then

            'build message body for this region
            'function call - this module
            'returns "Failed" on error
        strMsgBody = BuildMsg(Reg)
        If strMsgBody = "Failed" Then
                'call error handler with specifics - this module
            Call ErrorHandler("BuildMsg", 1, False)
            GoTo Exit_SendMessage
        End If
    End If
    
    
        'create instance of current database
    Set db = CurrentDb
        'open recordset based on table/query - recipients
    Set rs = db.OpenRecordset(strRecipList)
        
        'move to first record
    rs.MoveFirst
        'loop through recipients and build message for each
    Do Until rs.EOF
    
            'pause processing every 300 records
        If intCounter = 300 Then
            sglStartTime = Timer
            Do While Timer < sglStartTime + sglPauseInterval
                'pause processing
            Loop
                'reset counter
            intCounter = 0
        End If
        
            'use message template
        If blUseTemplate = True Then
            Set olOutlookMsg = olOutlook.CreateItemFromTemplate(strTemplatePath _
                    & strTemplateName)
        ElseIf blUseStationary = True Then
                'create new message using Outlook defaults.  Outlook should be configured with a default
                'stationary for all new messages
            Set olOutlookMsg = olOutlook.CreateItem(olMailItem)
        End If
        
        Set olInspector = olOutlookMsg.GetInspector

        With olOutlookMsg
                'recipient
            Set olOutlookRecip = .Recipients.Add(rs!emlAddress)
            olOutlookRecip.Type = olTo

                'subject, body, importance
            .Subject = strSubject
                'include message body?
            If blBuildBody = True Then
                .Body = strMsgBody
            End If
            .Importance = olImportanceNormal

                'attachment?
            If blAttachment = True Then
                Set olOutlookAttach = .Attachments.Add(strAttachmentPath _
                        & strAttachmentName, , , strAttachmentName)
            End If

                'resolve recipient name
            'For Each olOutlookRecip In .Recipients
            '    olOutlookRecip.Resolve
            'Next

                'display before sending?
            If blDisplayMsg = True Then
                .Display
            Else
                .Save
                .Send
            End If

        End With
    
            'increment counter
        intCounter = intCounter + 1
        
            'move to next recipient record
        rs.MoveNext
    Loop
    
Exit_SendMessage:
        'clear object variables
    Set olOutlookAttach = Nothing
    Set olOutlookRecip = Nothing
    Set olOutlookMsg = Nothing
    Set olOutlook = Nothing
    If Not rs Is Nothing Then rs.Close
        Set rs = Nothing
    Set db = Nothing
    Exit Sub

End Sub

Open in new window

0
All Courses

From novice to tech pro — start learning today.