Consulting

Results 1 to 10 of 10

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 Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    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

  7. #7
    You can use VBA to create a macro that automates the current instance of Outlook instead of generating and automating a separate instance of Outlook.

  8. #8
    You can use VBA to create a macro that automates the current instance of Outlook instead of generating and automating a separate instance of Outlook. VBA functions InStr and InStrRev are used to search for substrings in strings. If the search string is discovered, the position of the search string (as measured from the start of the check string) is returned. If the search string isn't discovered, the result is zero. If it will ask to pass the verification, you can use a temp phone number UK to get the code of unique use.

  9. #9
    you can use Regular expression to extract the phone number:
    Public Function getPhone(ByVal strText As String) As String
    Dim var As Variant
    With CreateObject("vbscript.regexp")
        .Global = False
        .ignorecase = True
        .pattern = "((\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}|\d{3}[\s.-]\d{4})"
        For Each var In .Execute(strText)
            Debug.Print var.Value
            Exit For
        Next
    End With
    End Function
    Debug.Print getPhone("Voicemail received from (647) 906-8121 (0:04s) on ext 500")
    Result: (647) 906-8121


    Debug.Print getPhone("Voicemail received from 906-8121 (0:04s) on ext 500")
    Result: 906-8121

  10. #10
    I have a problem of a similar nature since I am also developing an application. There should be a field in which the user enters his phone number and the address of his house, but for some reason, it does not display the country code when entering. I know that services like receive sms online, but I want users to enter their real numbers in my application. If someone here can help me figure out my code and find a bug, I would be incredibly grateful and could even pay you for this work. I hope that someone will answer me, thank you in advance

Posting Permissions

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