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_employ_a_digital_cert.htm