Automatically copy subject line(Outlook) to a cell in excel RRS feed

  • Question

  • Hello,

    Could someone provide me with a VBA script, which i can assign to a rule which will automatically copy the subject line of the email affected by the rule, and update a cell (A1) in a excel workbook?

    i understand it's probably quite simple however i'm not real savvy with VBA.

    would appreciate your help!

    Monday, August 24, 2015 5:51 AM


  • Probably something like the following. Test it by selecting a  message and running the TestScript macro.

    Option Explicit
    Sub TestScript()
    Dim olMsg As MailItem
        On Error Resume Next
        Set olMsg = ActiveExplorer.Selection.Item(1)
        SubjectToExcel olMsg
        Exit Sub
    End Sub
    Sub SubjectToExcel(olItem As Outlook.MailItem)
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Dim bStarted As Boolean
    'the path of the workbook which must exist:'
    Const strPath As String = "C:\Path\WorkbookName.xlsx"
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        If Not Err.Number = 0 Then
            Set xlApp = CreateObject("Excel.Application")
            bStarted = 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")
        xlSheet.Range("A1") = olItem.Subject
        xlWB.Close SaveChanges:=True
        If bStarted Then
        End If
        Set xlApp = Nothing
        Set xlWB = Nothing
        Set xlSheet = Nothing
        Exit Sub
    End Sub

    Graham Mayor - Word MVP

    • Proposed as answer by ryguy72 Monday, August 24, 2015 12:27 PM
    • Marked as answer by mctech2017 Wednesday, September 2, 2015 12:57 AM
    Monday, August 24, 2015 12:15 PM