none
Outlook VBA macro gets incorrect results when run from toolbar RRS feed

  • Question

  • I have a piece of code that compares text selected in an email with array elements and adds a cc based on the outcome of the comparison.  If I run the code from VBA editor, it works perfectly.  When I run the code from the toolbar, it does not recognize the selection as text and takes the On Error GoTo path.

    Possible causes?

    Friday, July 28, 2017 9:26 PM

All replies

  • Please post the relevant snippet of your code.

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Friday, July 28, 2017 9:30 PM
  • Sub CCBillZach()
    
    Dim strArea As String
    
    Dim varBillArea As Variant
    Dim varZachArea As Variant
    
    Dim intBillLen As Integer
    Dim intZachLen As Integer
    Dim intArrayIndex As Integer
    
    Dim objRecip As Recipient
    Dim objItem As Object
    Dim objInsp As Outlook.Inspector
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
    
    varBillArea = Array("205", "251", "256", "334", "938", "203", "475", "860", "959", "202", "302", "270", "364", "502", "606", "859", "239", "305", "321", "352", _
                    "386", "407", "561", "689", "727", "754", "772", "786", "813", "850", "863", "904", "941", "954", "229", "404", "470", "478", "678", "706", _
                    "762", "770", "901", "207", "227", "240", "301", "410", "443", "667", "339", "351", "413", "508", "617", "774", "781", "857", "978", "231", _
                    "248", "269", "313", "517", "586", "616", "734", "810", "906", "947", "989", "228", "601", "662", "769", "603", "201", "551", "609", "640", _
                    "732", "848", "856", "862", "908", "973", "212", "315", "332", "347", "516", "518", "585", "607", "631", "646", "680", "716", "718", "838", _
                    "845", "914", "917", "929", "934", "252", "336", "704", "743", "828", "910", "919", "980", "984", "216", "220", "234", "330", "380", "419", _
                    "440", "513", "567", "614", "740", "937", "215", "223", "267", "272", "412", "445", "484", "570", "310", "717", "724", "814", "878", "401", _
                    "803", "843", "854", "864", "423", "615", "629", "731", "865", "901", "931", "802", "276", "434", "540", "571", "703", "757", "804", "304", "681")
                    
    varZachArea = Array("327", "479", "501", "870", "217", "224", "309", "312", "331", "447", "464", "618", "630", "708", "730", "773", "779", "815", "847", "872", _
                    "219", "260", "317", "463", "574", "765", "812", "930", "319", "515", "563", "641", "712", "316", "620", "785", "913", "225", "318", "337", _
                    "504", "985", "218", "320", "507", "612", "651", "763", "952", "308", "402", "531", "701", "405", "539", "580", "918", "605", "210", "214", _
                    "254", "281", "325", "346", "361", "409", "430", "432", "469", "512", "682", "713", "726", "737", "806", "817", "830", "832", "903", "915", _
                    "936", "940", "972", "979", "262", "274", "414", "534", "608", "715", "920", "314", "417", "573", "636", "660", "816")
                    
    intBillLen = UBound(varBillArea) - LBound(varBillArea) + 1
    intZachLen = UBound(varZachArea) - LBound(varZachArea) + 1
    
    On Error Resume Next
    
    ' Reference the current Outlook item
    Set objItem = Application.ActiveInspector.CurrentItem
    If Not objItem Is Nothing Then
        If objItem.Class = olMail Then
            Set objInsp = objItem.GetInspector
            If objInsp.EditorType = olEditorWord Then
                Set objDoc = objInsp.WordEditor
                Set objWord = objDoc.Application
                Set objSel = objWord.Selection
    
                On Error GoTo NotText
                    
                strArea = objSel.Text
    
                For intArrayIndex = 0 To intBillLen - 1
                    If varBillArea(intArrayIndex) = strArea Then
                        With objItem
                            Set objRecip = objItem.Recipients.Add("username@domain")
                                objRecip.Type = olCC
                                objRecip.Resolve
                        End With
                        Exit For
                    Else
                        If intArrayIndex <= intZachLen And varZachArea(intArrayIndex) = strArea Then
                            With objItem
                                Set objRecip = objItem.Recipients.Add("username@domain")
                                    objRecip.Type = olCC
                                    objRecip.Resolve
                            End With
                            Exit For
                        End If
                    End If
                Next intArrayIndex
                        
                        
                With New MSForms.DataObject 'don't really need the next 4 lines inclusive
                    .SetText objSel.Text
                    .PutInClipboard
                End With
                On Error Resume Next
    
            End If
        End If
    End If
    
    Set objItem = Nothing
    Set objWord = Nothing
    Set objSel = Nothing
    Set objInsp = Nothing
    
    NotText:
        If Err <> 0 Then
            MsgBox "Data on clipboard is not text."
        End If
    
    End Sub

    Friday, July 28, 2017 9:42 PM
  • Hi SpurzFan,

    What do you mean running the code from the toolbar? I have tried to add the macro into quick access toolbar and it still works for me. What's the version of your outlook? Could you please provides details about the toolbar so we could try to reproduce your issue.

    Best Regards,

    Terry

    Saturday, July 29, 2017 3:30 AM
  • Did you try to debug the code? What line of code exactly generates an error?

    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Saturday, July 29, 2017 8:43 PM
  • Hello,

    >>it does not recognize the selection as text and takes the On Error GoTo path.

    Please remove all error handlers and run the macro again, what error would you get and which line casues the error?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 31, 2017 9:01 AM
    Moderator