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

[HareKrishna]

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.

[gmayor]

How about


PhoneNumberString = Item.Subject
PhoneNumberString = Mid(PhoneNumberString, InStr(1, PhoneNumberString, "("))
PhoneNumberString = Left(PhoneNumberString, InStr(2, PhoneNumberString, "(") - 1)

[HareKrishna]

27373

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.

[gmayor]

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

[Paul_Hossler]

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

[gmayor]

Cross posted at https://social.msdn.microsoft.com/Forums/en-US/df8cddaf-e4d7-46db-97e5-0d46b3b81fd8/vba-string-in-microsoft-outlook-to-extract-phone-number-from-incoming-email-subject-field?forum=isvvba

[HeitaWoorb]

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.

[HeitaWoorb]

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 (https://quackr.io) to get the code of unique use.

[arnelgp]

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

[leciafosl]

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