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 Meeting Starts
- In the first place, start your Outlook application as normal.
- Then in the emerging Outlook main window, press “Alt + F11” key shortcuts.
- 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.
- Subsequently, you ought to locate and open the “ThisOutlookSession” project.
- 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
- After that, you should sign this code.
- Later change your Outlook macro settings to permit the signed macros.
- Eventually, you need to set the reminders on your Outlook meetings based on the time for printing the attendee list.
- From now on, every time when the meeting reminder pops up, Outlook will automatically print the attendee list, look like the following screenshot:
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.
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