HELP--Extract Data from 2003 outlook transfer to excel spreadsheet

Status
Not open for further replies.

mizsporty

Member
Outlook version
Email Account
POP3
We are using outlook 2003/excel 2003. We have never done this, nor are we educated in code or understand this concept. Please explain our as if you are speaking someone grade school. We need a step by step instruction of how to extract specific data from outlook 2003 and have it go to excel 2003 spread sheet. We do not want all of our email used. Only those from named folder.

First Name

Last name

email

phone

age

sex

date of email

Please, we are spinning our wheels and do not know what to do.
 
This code will get the fields if the patterns (including case) are exactly - select one or more messages in any folder and run the macro. See How to use VB Editor if you don't know how to use VBA.

First Name
Last name
email
phone
age
sex
date of email

Code:
Option Explicit
Sub CopyToExcel()
Dim olItem As Outlook.MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim strFirst, strLast, strEmail, strPhone, strAge, strSex, strDate As String
Dim sText As String
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim Reg1 As Object
Dim M1 As Object
Dim M As Object
Dim i As Variant
    Dim strResult(7) As String
   Dim strTest(7) As String
    Dim obj As Object
   Dim Selection As Selection
      
            
 
enviro = CStr(Environ("USERPROFILE")) 
 
'the path of the workbook
strPath = enviro & "\Documents\test.xlsx"
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        bXStarted = True
    End If
    On Error GoTo 0
    'Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Open(strPath)
    Set xlSheet = xlWB.Sheets("Sheet1")
   ' Process the message record
   Set olItem = Application.ActiveExplorer().Selection(1)
  
   'Find the next empty line of the worksheet
    rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
    rCount = rCount + 1
     Debug.Print olItem.Body
    Set Reg1 = CreateObject("VBScript.RegExp")
   ' \s* = invisible spaces
   ' \d* = match digits
   ' \w* = match alphanumeric
    
 
Set Selection = Application.ActiveExplorer.Selection
 For Each obj In Selection
   Set olItem = obj
For i = 1 To 7 
 
With Reg1
   Select Case i
   Case 1
       .Pattern = "(First Name\s*(--)\s*(.*))\n\s*"
       .Global = False
      
   Case 2
       .Pattern = "(Last Name\s*(--)\s*(.*))\n\s*"
      .Global = False
        
   Case 3
       .Pattern = "(Email\s*(--)\s*(.*))\n\s*"
       .Global = False
   Case 4
       .Pattern = "(phone\s*(--)\s*(.*))\n\s*"
       .Global = False
   Case 5
       .Pattern = "(age\s*(--)\s*(.*))\n\s*"
       .Global = False
   Case 6
       .Pattern = "(sex\s*(--)\s*(.*))\n\s*"
       .Global = False
   Case 7
       .Pattern = "(date of email \s*(--)\s*(.*))\n\s*"
       .Global = False
   End Select
   
 
End With
 
  
    If Reg1.Test(olItem.Body) Then
   
       Set M1 = Reg1.Execute(olItem.Body)
       For Each M In M1
     
           strResult(i) = M.SubMatches(1)
           strTest(i) = M.SubMatches(2)
        strFirst = strTest(1) ' Trim(strResult(1))
        strLast = strTest(2) ' Trim(strTest(1))
        strEmail = strTest(3) ' Trim(strTest(2))
        strPhone = strTest(4) ' Trim(strResult(3))
        strAge = strTest(5) ' Trim(strResult(4))
        strSex = strTest(6) ' Trim(strResult(4))
        strDate = strTest(7) ' Trim(strResult(4))
 
          Next
   End If
 
   Next i
         
 
Debug.Print strFirst
   Debug.Print strLast
   Debug.Print strEmail
   Debug.Print strPhone
   Debug.Print strAge
   Debug.Print strSex
   Debug.Print strDate
 xlSheet.Range("B" & rCount) = strFirst
 xlSheet.Range("c" & rCount) = strLast
 xlSheet.Range("d" & rCount) = strEmail
 xlSheet.Range("e" & rCount) = strPhone
 xlSheet.Range("f" & rCount) = strAge
 xlSheet.Range("g" & rCount) = strSex
 xlSheet.Range("h" & rCount) = strDate
Next
    xlWB.Close 1
    If bXStarted Then
        xlApp.Quit
    End If
    Set M = Nothing
    Set M1 = Nothing
    Set Reg1 = Nothing
    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
End Sub
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
M HELP - Can't open outlook... How can I extract my Emails that I had in folders Using Outlook 3
S Custom Contact card - need help creating one Outlook VBA and Custom Forms 1
D Lifelong Windows user - new to Mac - Help!!! Using Outlook 3
L Help: set flag for sent mail to check if received an answer Outlook VBA and Custom Forms 2
Nufc1980 Outlook "Please treat this as private label" auto added to some emails - Help. Using Outlook 5
I Help with Smart Folder + Query Builder on IMAP Using Outlook 0
S Outlook 2002- "Send" button has disappeared. Help please. Using Outlook 1
A Outlook 2019 Help with forwarding email without mentioning the previous email sender. Outlook VBA and Custom Forms 0
Witzker Outlook 2019 HELP to get Template Path in a Function Outlook VBA and Custom Forms 2
CWM330 Outlook 365 HELP! Calendar Craziness! Using Outlook 5
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 1
e_a_g_l_e_p_i Has nobody used Office 2021 enough to help me or have you given up on me.......lol Using Outlook 1
X Open Hyperlinks in an Outlook Email Message (Help with Diane's solution) Outlook VBA and Custom Forms 3
L Help connecting to hosted exchange server 2016 Using Outlook 0
B Seeking help with Outlook rule Using Outlook 2
D Need help with MS Authenticator Using Outlook 4
I Outlook for Mac 2019 using on desktop and laptop IMAP on both need help with folders Using Outlook 1
FryW Need help modifying a VBA script for in coming emails to auto set custom reminder time Outlook VBA and Custom Forms 0
S.Champ Please help? I've imported a random workcalendar I dont even know who's. Can I undo it? and then I need to re-sync the google one again. Its a mess:( Using Outlook 2
S HTML to Plain Text Macro - Help Outlook VBA and Custom Forms 1
e_a_g_l_e_p_i Outlook 2010 Help setting up Gmail account in Outlook 2010 Using Outlook 3
N Help creating a VBA macro with conditional formatting to change the font color of all external emails to red Outlook VBA and Custom Forms 5
Y Filter unread emails in a search folder vba help Outlook VBA and Custom Forms 0
L Need help modifying a VBA script for emails stuck in Outbox Outlook VBA and Custom Forms 6
F Microsoft Outlook Connector 14.0.6123.5001 - Help! Using Outlook 6
Witzker Pls help to change the code for inserting date in Ol contact body Outlook VBA and Custom Forms 5
R Disable conversation thread from replying of recipients in the same subject. Please help Using Outlook 0
R seperate read layout to design in outlook 2016..Help!! Outlook VBA and Custom Forms 3
O Help .. got lost ... installing Office like 2016 Using Outlook 5
A Arthur needs help with 2007 Outlook e-mail Using Outlook.com accounts in Outlook 3
R Help Revising VBA macro to delete email over different time span Outlook VBA and Custom Forms 0
Marc2019 Need help please! Cannot Setup my outlook email account on my Mac Outlook 2011 Using Outlook.com accounts in Outlook 2
L Attachment saving and tracking - PLEASE help! Outlook VBA and Custom Forms 5
I Help with dates in task list. Using Outlook 5
C need help setting up outlook first time Using Outlook 1
K To do bar help Using Outlook 8
M Help sending email but removing signature via VBA Outlook VBA and Custom Forms 5
S help with outlook scripting Outlook VBA and Custom Forms 4
J Help Please!!! Outlook 2016 - VBA Macro for replying with attachment in meeting invite Outlook VBA and Custom Forms 9
EmelineGueguen Help to understand the problem of work Using Outlook 1
N Outlook Forms Help Outlook VBA and Custom Forms 2
N Need help syncing contacts to iPhone X Using Outlook 8
S VBA Macro - Run-time error '424': object required - Help Please Outlook VBA and Custom Forms 3
broadbander Needing help with reply/reply all while keeping attachments and adding a new CC recipient. Outlook VBA and Custom Forms 5
J Help! My contacts have disappeared. Using Outlook 5
J HELP- Rule to auto strip prepend from external emails Using Outlook 0
J Help Needed With Multi-Step Login Email Address Using Outlook.com accounts in Outlook 1
G Bcc help - Preventing multiple forwards from a bcc'd distribution group Using Outlook 1
G [Help] Converting array to destination folder path Outlook VBA and Custom Forms 1
K Help.... Office Outlook 2016 Using Outlook 1

Similar threads

Back
Top