Outlook Macro - Find Text, Copy value after to clipboard?

[NewYears1978]

I started a new job this week and we do a ton of copy pasting from emails we receive. Basically I get order confirmations via email in Outlook (2013 I believe) which have order numbers in them, I manually select and copy the number to paste into another program.

I simply want a macro that will find the number, and copy to the clipboard.

The string I want to find is like this "Customer #: 123456"

The value I want to copy is just the number after "Customer #""

I've made macros in VBA before in Excel, but I am not a coder so I usually have to get loads of help form people. I've attempted to search this on my own but had no luck. Thanks in advance.

[gmayor]

Assuming that the customer number is in the body of the selected message, and is exactly in the format described i.e. all numeric, the following will locate it and copy it to the clipboard:

Option Explicit

Sub GetCustomer()
Dim olItem As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim dCust As DataObject
Dim wdDoc As Object
Dim oRng As Object
Dim sCustomer As String
Dim bFound As Boolean
    On Error GoTo lbl_Exit
    Set olItem = ActiveExplorer.Selection.Item(1)
    With olItem
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(findText:="Customer #:[ 0-9]{2,}", MatchWildcards:=True)
                sCustomer = Trim(Split(oRng.Text, Chr(58))(1))
                bFound = True
                Set dCust = New DataObject
                dCust.SetText sCustomer
                dCust.PutInClipboard
                MsgBox "Customer number '" & sCustomer & "' copied to clipboard"
                Exit Do
            Loop
        End With
        If Not bFound Then MsgBox "Customer number not found"
    End With
lbl_Exit:
    Set olItem = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Set dCust = Nothing
    Exit Sub
End Sub

[NewYears1978]

Assuming that the customer number is in the body of the selected message, and is exactly in the format described i.e. all numeric, the following will locate it and copy it to the clipboard:

Option Explicit

Sub GetCustomer()
Dim olItem As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim dCust As DataObject
Dim wdDoc As Object
Dim oRng As Object
Dim sCustomer As String
Dim bFound As Boolean
    On Error GoTo lbl_Exit
    Set olItem = ActiveExplorer.Selection.Item(1)
    With olItem
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(findText:="Customer #:[ 0-9]{2,}", MatchWildcards:=True)
                sCustomer = Trim(Split(oRng.Text, Chr(58))(1))
                bFound = True
                Set dCust = New DataObject
                dCust.SetText sCustomer
                dCust.PutInClipboard
                MsgBox "Customer number '" & sCustomer & "' copied to clipboard"
                Exit Do
            Loop
        End With
        If Not bFound Then MsgBox "Customer number not found"
    End With
lbl_Exit:
    Set olItem = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Set dCust = Nothing
    Exit Sub
End Sub

Thank you kindly, I am headed to work I will try it. I haven't worked with outlook in YEARS so I assume the macro system is the same as Excel. Will checkit out and report back.

[NewYears1978]

Okay I ttied it out this morning, first of all we have 2010 not 2013, not sure if that makes a difference.

I am throwing this error:

Compile Error:
User-defined type not defined

It puts arrow on line for the "Sub GetCustomer" and it highlights "dCust As DataObject"

[gmayor]

Hmmm - it's a question of reference to the Microsoft Forms 2.0 Object Library. You can either enable that reference in the VBA editor of change the line

Dim dCust As DataObject
to
Dim dCust As Object

and the line
Set dCust = New DataObject
to
Set dCust = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

which should fix it.

This should work in Word 2010/13/16 and probably earlier versions only.

[NewYears1978]

This worked! Thanks!

[NewYears1978]

Over the weekend and back to work now suddenly it doesn't work..it appears like the macro doesn't even run when I run it..maybe some form of security or something? (No errors just no response)

[gmayor]

You will probably have to self certify the project - http://www.gmayor.com/create_and_emp...gital_cert.htm