Consulting

Results 1 to 6 of 6

Thread: VBA string in Microsoft Outlook - to extract phone number from email subject field?

  1. #1

    VBA string in Microsoft Outlook - to extract phone number from email subject field?

    Here is a VBA module string I have been using successfully with Microsoft Outlook desktop client. It extracts the phone number from the BODY of an incoming email message.

    PhoneNumberString = 1 & NumericOnly(Strings.Mid(Item.Body, InStr(Item.Body, "From") + 5, InStr(Item.Body, "To") - InStr(Item.Body, "From")))


    Well, now my email provider is placing the phone number instead in the SUBJECT field of incoming messages !!! Ugh.

    Here is a typical actual typical NEW email subject line:

    Voicemail received from (647) 906-8121 (0:04s) on ext 500



    How can I extract the phone number from its new location in the SUBJECT field?

    . . . in the above example, it would extract . . . (647) 906-8121



    Thanks for anyone’s help.

  2. #2
    How about
    PhoneNumberString = Item.Subject
    PhoneNumberString = Mid(PhoneNumberString, InStr(1, PhoneNumberString, "("))
    PhoneNumberString = Left(PhoneNumberString, InStr(2, PhoneNumberString, "(") - 1)
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    outlook error dialogue.jpg

    Thanks for your time and reply!

    After inserting the new code, the result is a Outlook error message pops up (screenshot attached).

    I tried to paste the entire module here to this message, but I got error message that it was not allowed to be posted? Something about too many URLs or some other violation.

  4. #4
    Without seeing your code it is impossible to guess what the problem is, however adding some error handling should indicate the problem. Select a message and run the following

    Sub Test()
    'Graham Mayor - https://www.gmayor.com - Last updated - 31 Oct 2020 
    Dim oItem As Object
    Dim PhoneNumberString As String
    
        On Error Resume Next
        Select Case Outlook.Application.ActiveWindow.Class
            Case olInspector
                Set oItem = ActiveInspector.currentItem
            Case olExplorer
                Set oItem = Application.ActiveExplorer.Selection.Item(1)
        End Select
    
        If TypeName(oItem) = "MailItem" Then
            If InStr(1, oItem.Subject, "Voicemail received from") > 0 Then
                PhoneNumberString = oItem.Subject
                PhoneNumberString = Mid(PhoneNumberString, InStr(1, PhoneNumberString, "("))
                PhoneNumberString = Left(PhoneNumberString, InStr(2, PhoneNumberString, "(") - 1)
                Beep
                MsgBox PhoneNumberString
            Else
                MsgBox oItem.Subject & vbCr & "is not a voicemail message", vbCritical
            End If
        Else
            MsgBox "Not a mail item!", vbCritical
        End If
    
    lbl_Exit:
        Set oItem = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,391
    Location
    FWIW, Graham's code will work IF the Subject line is 'close enough' to your pattern

    Maybe you need to add error checks to make sure that you're dealing with a voice mail message, that there is a phone number, etc. ??


    Option Explicit
    
    
    Sub test()
        Dim PhoneNumberString As String
    
    
    '    PhoneNumberString = Item.Subject
        PhoneNumberString = "Voicemail received from (647) 906-8121 (0:04s) on ext 500" '   <<<<<<<<<<<<< just for test
        
        PhoneNumberString = Mid(PhoneNumberString, InStr(1, PhoneNumberString, "("))
        PhoneNumberString = Left(PhoneNumberString, InStr(2, PhoneNumberString, "(") - 1)
        
        MsgBox PhoneNumberString
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •