How to Auto Log Each Printed Outlook Email in Excel Workbook

Some users hope to auto save logs of printed emails in their Outlook. However, there is not such a direct feature available. Hence, in this article, we’ll introduce a way to auto log each printed email in a specific Excel workbook.

For some reasons, you may need to record the information of an email when you print it. Obviously, Outlook doesn’t provide a native feature supporting this. Thus, in general, you have to manually accomplish this task. Nevertheless, with the help of VBA code, you still can let your Outlook undertake this mission with ease. Now, here we will uncover such a piece of VBA code and detailed steps to you.

Auto Log Each Printed Email in Excel Workbook

  1. For a start, launch Outlook application.
  2. Then, access VBA editor via clicking the “Visual Basic” button on “Developer” tab.
  3. Next, put the following VBA code into an unused module.
Sub RecordPrintedEmails()
    Dim objMail As Outlook.MailItem
    Dim objExcelApp As Excel.Application
    Dim strExcelFile As String
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim nNextEmptyRow As Integer
 
    'Get the mail
    Select Case Outlook.Application.ActiveWindow.Class
           Case olInspector
                Set objMail = ActiveInspector.CurrentItem
           Case olExplorer
                Set objMail = ActiveExplorer.Selection.Item(1)
    End Select
 
    objMail.PrintOut
 
    On Error Resume Next
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Visible = True
 
    'Change the path to the specific excel file
    strExcelFile = "E:\Emails\Printed Emails.xlsx"
    Set objExcelWorkbook = objExcelApp.Workbooks.Open(strExcelFile)
    Set objExcelWorksheet = objExcelWorkbook.Sheets(1)
    objExcelWorksheet.Activate
 
    nNextEmptyRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
 
    'Change the details as per your own case
    With objExcelWorksheet
         .Cells(nNextEmptyRow, 1) = Date
         .Cells(nNextEmptyRow, 2) = objMail.Subject
         .Cells(nNextEmptyRow, 3) = objMail.Sender
         .Cells(nNextEmptyRow, 4) = objMail.SentOn
         .Cells(nNextEmptyRow, 5) = objMail.Size
         .Cells(nNextEmptyRow, 6) = objMail.Attachments.Count
         .Columns("A:E").AutoFit
    End With
 
    objExcelWorkbook.Close True
    objExcelApp.Quit
End Sub

VBA Code - Auto Log Each Printed Email in Excel Workbook

  1. After that, exit the VBA editor.
  2. Subsequently, according to the “Optional Step” in the post “How to Run VBA Code in Your Outlook“, add this macro to the ribbon or Quick Access Toolbar.Add Macro Button to Ribbon
  3. Since then, every time when you want to print an email, remember to utilize the newly added macro button instead of the standard “Print”.Print Email
  4. In this way, Outlook will auto log the printed mail in the predefined Excel worksheet.Logs in Excel

Keep Outlook File Accessible

At times, after Outlook crashes, you may receive the error prompting that the PST file is inaccessible. At that time, you should calm down and then make use of the inbox repair tool to fix Outlook. However, the inbuilt utility is not versatile. It can fail, too. Hence, if your PST file is still unavailable after Scanpst repair, your last resort is a more effective and reliable external tool, such as DataNumen Outlook Repair.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf recovery and outlook repair software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published.