Consulting

Results 1 to 7 of 7

Thread: Extract text from a certain part of the email subject line

  1. #1
    VBAX Newbie
    Joined
    Apr 2021
    Posts
    4
    Location

    Extract text from a certain part of the email subject line

    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?

  2. #2
    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

    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Apr 2021
    Posts
    4
    Location
    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?

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Newbie
    Joined
    Apr 2021
    Posts
    4
    Location
    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

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

    2021-04-22_14-22-34.jpg
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Newbie
    Joined
    Apr 2021
    Posts
    4
    Location
    Quote Originally Posted by gmayor View Post
    Ensure that you have MS Forms 2. 0 Object Library checked in the VBA editor > Tools > References

    2021-04-22_14-22-34.jpg
    Brilliant that's worked a treat. Thanks so much for the code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •