How to Auto Print the Attendee List a Few Minutes before an Outlook Meeting Starts

Many users always need to print attendee list when an Outlook meeting is coming up. Thus, in this article, we’ll show a piece of VBA code, which can let Outlook auto accomplish this.

Most of time, before a meeting, the organizer must require the attendee list. It is generally used for attendees to sign in. My previous article “How to Extract & Print Meeting Attendee List in Outlook” has introduced 2 ways. But both of them are fairly troublesome. Therefore, here we will expose a much quicker and more effective method, which is applying VBA code. Now read on to get it in detail.

Auto Print the Attendee List a Few Minutes before an Outlook Meeting Starts

Auto Print the Attendee List a Few Minutes before an Meeting Starts

  1. In the first place, start your Outlook application as normal.
  2. Then in the emerging Outlook main window, press “Alt + F11” key shortcuts.
  3. Next you will get access to Outlook VBA editor window, in which you ought to enable Microsoft Excel Object.
  • Firstly, Click “Tools” and select “References” from its drop down list.
  • Then in the dialog box, you should find “Microsoft Excel Object Library” and check it.
  • Lastly, hit “OK” button.Enable Microsoft Excel Object
  1. Subsequently, you ought to locate and open the “ThisOutlookSession” project.
  2. Then copy the VBA code below into this project window.
Private Sub Application_Reminder(ByVal Item As Object)
    Dim objMeeting As Outlook.AppointmentItem
    Dim objAttendees As Outlook.Recipients
    Dim objAttendee As Outlook.recipient
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim strExcelFile As String
    Dim nLastRow As Integer
    Dim strTempFolder As String
    Dim objShell, objFileSystem As Object
    Dim objTempFolder, objTempFolderItem As Object
  
    On Error Resume Next
    'Create a new Excel file
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
    Set objExcelWorksheet = objExcelWorkbook.Sheets("Sheet1")
    objExcelWorksheet.Cells(1, 1) = "Name"
    objExcelWorksheet.Cells(1, 2) = "Type"
    objExcelWorksheet.Cells(1, 3) = "Email Address"
    objExcelWorksheet.Cells(1, 4) = "Response"

    If Item.Class = olAppointment Then
       Set objMeeting = Item
       Set objAttendees = objMeeting.Recipients
 
       If objAttendees.Count > 0 Then
          For Each objAttendee In objAttendees
              nLastRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
              'Input the attendee names
              objExcelWorksheet.Range("A" & nLastRow) = objAttendee.Name
 
              'Input the type of attendees
              Select Case objAttendee.Type
                     Case "1"
                          objExcelWorksheet.Range("B" & nLastRow) = "Required Attendee"
                     Case "2"
                          objExcelWorksheet.Range("B" & nLastRow) = "Optional Attendee"
              End Select
 
              'Input the email addresses of attendees
              objExcelWorksheet.Range("C" & nLastRow) = objAttendee.Address
 
              'Input the responses of attendees
              Select Case objAttendee.MeetingResponseStatus
                     Case olResponseAccepted
                          objExcelWorksheet.Range("D" & nLastRow) = "Accept"
                     Case olResponseDeclined
                          objExcelWorksheet.Range("D" & nLastRow) = "Decline"
                     Case olResponseNotResponded
                          objExcelWorksheet.Range("D" & nLastRow) = "Not Respond"
                     Case olResponseTentative
                          objExcelWorksheet.Range("D" & nLastRow) = "Tentative"
              End Select
           Next
        End If
    End If
 
    'Fit the columns from A to D
    objExcelWorksheet.Columns("A:D").AutoFit
 
    'Save the Excel file in a temp folder
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    strTempFolder = objFileSystem.GetSpecialFolder(2).Path & "\temp " & Format(Now, "yyyy-mm-dd hh-mm-ss")
    MkDir (strTempFolder)
    strExcelFile = strTempFolder & "\Attendee List for " & Item.Subject & " (" & Format(Now, "yyyy-mm-dd hh-mm-ss") & ").xlsx"
    objExcelWorkbook.Close True, strExcelFile
 
    'Print the Excel file
    Set objShell = CreateObject("Shell.Application")
    Set objTempFolder = objShell.NameSpace(0)
    Set objTempFolderItem = objTempFolder.ParseName(strExcelFile)
    objTempFolderItem.InvokeVerbEx ("print")
 
    'Delete the temp folder and temp Excel file
    objFileSystem.DeleteFolder (strTempFolder)
End Sub

VBA Code - Auto Print the Attendee List a Few Minutes before an Outlook Meeting Starts

  1. After that, you should sign this code.
  2. Later change your Outlook macro settings to permit the signed macros.
  3. Eventually, you need to set the reminders on your Outlook meetings based on the time for printing the attendee list.
  4. From now on, every time when the meeting reminder pops up, Outlook will automatically print the attendee list, look like the following screenshot:Printed Attendee List

Secure Your PST Data Effectively

As malicious email has been one of the most serious security threats, you should spend some time preventing such emails in your Outlook. It’s because this kind of emails can lead to PST corruption with ease. For instance, you have to install an antivirus application on your PC and set your junk email options to high. What’s more, you should get hold of a powerful PST repair tool, like 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 corrupt sql and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.