Extract text from a certain part of the email subject line

[hooby1]

Is it possible to run a vba script/macro that will look at the current selected email and copy certain text within the subject to the clipboard.

For example, I have emails come in with the subject line:

QUERY ID #id1234567v1 is ready to be assigned to a member

This subject is always the same (apart from the id number) and the length will never change.

I would like to select an email and run the a vba/macro that will copy the id1234567v1 to the clipboard

Is this possible?

[gmayor]

In theory the following should work. I say 'in theory' because following this morning's update it does not seem to be copied to the clipboard :banghead:


Sub GetID()'Graham Mayor - https://www.gmayor.com - Last updated - 15 Apr 2021
Dim strID As String
Dim olMsg As MailItem
Dim oData As MSForms.DataObject
On Error Resume Next
Select Case Outlook.Application.ActiveWindow.Class
Case olInspector
Set olMsg = ActiveInspector.currentItem
Case olExplorer
Set olMsg = Application.ActiveExplorer.Selection.Item(1)
End Select
If InStr(1, olMsg.Subject, "#") > 0 Then
Set oData = New DataObject
strID = Split(olMsg.Subject, "#")(1)
MsgBox strID & vbCr & vbCr & "copied to clipboard", vbInformation
oData.SetText strID
oData.PutInClipboard
Set oData = Nothing
Else
MsgBox "ID not found", vbCritical
End If
lbl_Exit:
Exit Sub
End Sub

[hooby1]

Hi gmayor

Many thanks for the reply. I was about to test that when....... the person who has set the template up for emails has altered the wording in the subject emails. (I did ask for it to be changed back but someone above him has asked it to be changed).

So now the new subject reads

Your cheques for accident application id-12345678 has been processed successfully

Notice the id-12345678 has now moved to the middle of the subject instead of the start.

Can the code you supplied be altered so it can copy the id-12345678 (These numbers will obviously change) reflecting the subject change?

[gmayor]

You will need a different technique to process that subject. Assuming the ID is a whole number then:

Sub GetID()
'Graham Mayor - https://www.gmayor.com - Last updated - 21 Apr 2021
Dim strID As String
Dim olMsg As MailItem
Dim oData As MSForms.DataObject
On Error Resume Next
Select Case Outlook.Application.ActiveWindow.Class
Case olInspector
Set olMsg = ActiveInspector.currentItem
Case olExplorer
Set olMsg = Application.ActiveExplorer.Selection.Item(1)
End Select
strID = GetNum(olMsg.Subject)
If strID = "" Then
MsgBox "ID not found", vbCritical
Else
Set oData = New DataObject
strID = "id-" & strID
MsgBox strID & vbCr & vbCr & "copied to clipboard", vbInformation
oData.SetText strID
oData.PutInClipboard
Set oData = Nothing
End If
lbl_Exit:
Exit Sub
End Sub


Private Function GetNum(sText As String) As String
Dim i As Integer
For i = 1 To Len(sText)
If Mid(sText, i, 1) >= "0" And Mid(sText, i, 1) <= "9" Or Mid(sText, i, 1) = "." Then
GetNum = GetNum + Mid(sText, i, 1)
End If
Next
lbl_Exit:
Exit Function
End Function

[hooby1]

Hi gmayor


I copied the code saved it within Outlook.


However, when I click to run the code it shows an error message:


Compile error:
User-defined type not defined.


Then when I click OK it highlights Dim oData As MSForms.DataObject in yellow

[gmayor]

Ensure that you have MS Forms 2. 0 Object Library checked in the VBA editor > Tools > References

28345

[hooby1]

Ensure that you have MS Forms 2. 0 Object Library checked in the VBA editor > Tools > References

28345

Brilliant that's worked a treat. Thanks so much for the code.