Home > OS >  Error 440 array index out of bounds - Outook 365 - VBA - Please Hep
Error 440 array index out of bounds - Outook 365 - VBA - Please Hep

Time:05-12

I have a VBA module in Outlook that Looks for a condition to match the Exact Subject and Exact Email address in one Mailbox and then send a reply back (Template) to the recipient of that email. The script was working fine lately getting Error 440 for array out of bounds. When I debug it highlights the line :

Set pa = recips(1).PropertyAccessor"

The code is below.

Option Explicit

Private objNS As Outlook.NameSpace
Private WithEvents objNewMailItems As Outlook.Items

'Update the AWS and Azure auto reply template path
Private Const AWS_AUTO_REPLY = "C:\Users\Documents\AWS_New_Account.oft"
Private Const AZURE_AUTO_REPLY = "C:\Users\Documents\Azure_New_Account.oft"


Private Sub Application_Startup()

Dim objMyInbox As Outlook.MAPIFolder
Dim oAccount As Account
Dim Store As Outlook.Store

'Set objNS = Application.GetNamespace("MAPI")

'For Each oAccount In Session.Accounts
'    Set Store = oAccount.DeliveryStore
'    Set objMyInbox = Store.GetDefaultFolder(olFolderInbox)
'    Set objNewMailItems = objMyInbox.Items
'    Set objMyInbox = Nothing
'   MsgBox "Application_Startup"
'Next

Set objNS = Application.GetNamespace("MAPI")
Set objMyInbox = objNS.Folders("[email protected]").Folders("Inbox")
Set objNewMailItems = objMyInbox.Items

Set objMyInbox = Nothing
MsgBox "Script Starting"
  
End Sub


Private Sub objNewMailItems_ItemAdd(ByVal Item As Object)

Dim subjectString As String
Dim senderEmailString As String
Dim recipientEmailString As String
Dim oRespond As Outlook.MailItem

Dim recips As Outlook.Recipients
Dim recip As Outlook.Recipient
Dim pa As Outlook.PropertyAccessor
Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
Set recips = Item.Recipients
'MsgBox "objNewMailItems_ItemAdd function call"
'Ensure we are only working with e-mail itemshe
If Item.Class <> olMail Then Exit Sub
        
    subjectString = ""   Item.Subject
    senderEmailString = ""   Item.SenderEmailAddress
    
    'GetSMTPAddressForRecipients (Item)
    recipientEmailString = ""
    Set recips = Item.Recipients
    'For Each recip In recips
        Set pa = recips(1).PropertyAccessor
        recipientEmailString = pa.GetProperty(PR_SMTP_ADDRESS) & ";" & recipientEmailString
           
    'Next
        
        If (InStr(recipientEmailString, "naws") > 0) Or (InStr(recipientEmailString, "xaws") > 0) Or (InStr(recipientEmailString, "saws") > 0) Or (InStr(recipientEmailString, "vcaws") > 0) Or (InStr(recipientEmailString, "daws") > 0) Or (InStr(recipientEmailString, "vaws") > 0) Or (InStr(recipientEmailString, "rovisioningteam") > 0) Then
            'MsgBox "D ACCOUNT - DO NOT SEND"
            GoTo ENDOFCODE
        End If

    If InStr(subjectString, "Welcome to your Azure free account") > 0 Then
        If InStr(senderEmailString, "[email protected]") > 0 Then
                
                ' This sends a response back using a template
                               
                ' Enter the actual path for
                Set oRespond = Application.CreateItemFromTemplate(AZURE_AUTO_REPLY)
                
                With oRespond
                    '.Recipients.Add Item.To
                    
                    .Recipients.Add pa.GetProperty(PR_SMTP_ADDRESS)
                    .Recipients.Add("[email protected]").Type = (olCC)
                    
                    ' includes the original message as an attachment
                    .Attachments.Add Item

                    ' use this for testing, change to .send once you have it working as desired
                    '.Display
                    '.Send
                End With
        End If
   End If
    
   If InStr(subjectString, "[EXT] Welcome to Amazon Web Services") > 0 Then
        If InStr(senderEmailString, "[email protected]") > 0 Then
         
                
                ' This sends a response back using a template
                'MsgBox "AWS CONDITION"
                               
                Set oRespond = Application.CreateItemFromTemplate(AWS_AUTO_REPLY)
                
                With oRespond
                    '.Recipients.Add Item.To
                    
                    .Recipients.Add pa.GetProperty(PR_SMTP_ADDRESS)
                    .Recipients.Add("[email protected]").Type = (olCC)
                                      
          
                    ' includes the original message as an attachment
                    .Attachments.Add Item
                    
                    'MsgBox "AWS CONDITION 2"

                    ' use this for testing, change to .send once you have it working as desired
                    .Display
                    .Send
                End With
        End If
    End If
ENDOFCODE:
    Set oRespond = Nothing

End Sub
Sub GetSMTPAddressForRecipients(mail As Outlook.MailItem)
    Dim recips As Outlook.Recipients
    Dim recip As Outlook.Recipient
    Dim pa As Outlook.PropertyAccessor
    Const PR_SMTP_ADDRESS As String = _
        "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
    Set recips = mail.Recipients
    For Each recip In recips
        Set pa = recip.PropertyAccessor
        Debug.Print recip.Name & " SMTP=" _
           & pa.GetProperty(PR_SMTP_ADDRESS)
    Next
End Sub
Function ResolveDisplayNameToSMTP(sFromName) As String
    
    Dim OLApp As Object 'Outlook.Application
    Dim oRecip As Object 'Outlook.Recipient
    Dim oEU As Object 'Outlook.ExchangeUser
    Dim oEDL As Object 'Outlook.ExchangeDistributionList

    Set OLApp = CreateObject("Outlook.Application")
    Set oRecip = OLApp.Session.CreateRecipient(sFromName)
    oRecip.Resolve
    If oRecip.Resolved Then
        Select Case oRecip.AddressEntry.AddressEntryUserType
            Case 0, 5 'olExchangeUserAddressEntry & olExchangeRemoteUserAddressEntry
                Set oEU = oRecip.AddressEntry.GetExchangeUser
                If Not (oEU Is Nothing) Then
                    ResolveDisplayNameToSMTP = oEU.PrimarySmtpAddress
                End If
            Case 10, 30 'olOutlookContactAddressEntry & 'olSmtpAddressEntry
                    ResolveDisplayNameToSMTP = oRecip.AddressEntry.Address
        End Select
    End If
End Function

    enter code here

Sub Project1()

End Sub

CodePudding user response:

The ItemAdd event can be fired for items that are moved to the folder manually (or created from the ground and saved there). So, there is a chance the Recipients collection will be empty. In that case I'd recommend checking the Recipients.Count property first which returns a long indicating the count of objects in the specified collection.

Also you could use a low-level property which can help with distinguishing between read-only items - the PR_MESSAGE_FLAGS property contains a bitmask of flags that indicate the origin and current state of a message.

Finally, I'd suggest using the GetDefaultFolder method of the Namespace or Store class to retrieve the required folder instead of cryptic names, for example:

objNS.Folders("[email protected]").Folders("Inbox")

If it is the default store you could use the NameSpace.GetDefaultFolder method which returns a Folder object that represents the default folder of the requested type for the current profile; for example, obtains the default Inbox folder for the user who is currently logged on. The Store.GetDefaultFolder method is similar to the GetDefaultFolder method of the NameSpace object. The difference is that this method gets the default folder on the delivery store that is associated with the account, whereas NameSpace.GetDefaultFolder returns the default folder on the default store for the current profile.

CodePudding user response:

You run into a message with no recipients, hence the line accessing the very first recipient fails.

   recipientEmailString = ""
   For Each recip In recips
        Set pa = recip.PropertyAccessor
        recipientEmailString = pa.GetProperty(PR_SMTP_ADDRESS) & ";" & recipientEmailString
    Next
  • Related