How to download mobile no in excel from mail

You can download mobile numbers from an email in Excel using a combination of VBA scripting and the Outlook object library. Here's a step-by-step guide to help you achieve this:

Prerequisites:

1. You need to have Outlook installed on your computer.
2. You need to have Excel installed on your computer.
3. You need to have the Outlook object library installed and referenced in your Excel VBA project.

Step 1: Create a new Excel workbook and enable the Outlook object library

1. Open a new Excel workbook.
2. Press Alt + F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, click Tools > References in the menu.
4. Check if Microsoft Outlook XX.X Object Library is listed in the References window. If not, click Browse and navigate to the Outlook installation directory (e.g., C:\Program Files\Microsoft Office\Root\Office16\OUTLOOK.DLL) and select it.
5. Click OK to close the References window.

Step 2: Create a VBA script to download mobile numbers from an email

1. In the Visual Basic Editor, insert a new module by clicking Insert > Module in the menu.

2. Paste the following code into the module:

   Sub DownloadMobileNumbersFromEmail()
    Dim olApp As New Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As MAPIFolder
    Dim olItems As Items
    Dim olItem As Object
    Dim mobileNumbers As String
   
    ' Set the email folder and search criteria
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.GetDefaultFolder(olFolderInbox)
    Set olItems = olFolder.Items
    olItems.Sort "ReceivedTime", olDescending
    olItems.Find "&subject='Your Email Subject'", olSearchScopeCurrentItems
   
    ' Loop through each email and extract mobile numbers
    For Each olItem In olItems
        If olItem.Class = olMail Then
            mobileNumbers = ""
            For Each attachment In olItem.Attachments
                If attachment.FileName Like "*.vcf" Then
                    mobileNumbers = mobileNumbers & attachment.OpenAsTextStream(1).ReadAll
                End If
            Next attachment
            If mobileNumbers <> "" Then
                ' Write the mobile numbers to a new Excel worksheet
                Dim ws As Worksheet
                Set ws = ThisWorkbook.Worksheets.Add
                ws.Name = "Mobile Numbers"
                ws.Range("A1").Value = mobileNumbers
            End If
        End If
    Next olItem
   
    ' Clean up
    Set olItem = Nothing
    Set olItems = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
   End Sub

   Step 3: Run the VBA script

3. Save the workbook.

4. Press Alt + F8 to open the Macro dialog box.

5. Select the DownloadMobileNumbersFromEmail macro and click Run.

The script will download the mobile numbers from the specified email subject and write them to a new Excel worksheet named "Mobile Numbers".

Note: This script assumes that the mobile numbers are stored in VCF (vCard) files attached to the emails. If your mobile numbers are stored in a different format, you may need to modify the script accordingly. Additionally, this script only downloads mobile numbers from the specified email subject and may not work if the mobile numbers are stored in a different format or location.