NEW Last updated 2020.12.24
SampleScenarios Mail
Send a reminder using Outlook email send / add appointment

This is a sample scenario using the WinActor(OR-5200) library (Outlook operation). In this sample scenario, you can check the date in Excel and send a reminder email or add a schedule when there is information about the deadline.
*Please see this precautions upon downloading this sample scenario.

Feature

This sample scenario executes following process.

  • Open the Excel file in which the schedule is registered and get the information from the columns specified in "ScheduledEndDateColumn", "CompletedDateColumn", and "NameColumn" in Variable List.
  • The information on the scheduled end date is compared with the date when this sample scenario will be executed, and the information whose scheduled end date is approaching is targeted for reminders.
  • If there is information for the reminder, send a reminder email or add an appointment in Outlook.

WinActor(OR-5200) Modules to use

In this sample scenario, the following WinActor(OR-5200) modules are used.

  • 20_Mailer/01_Outlook/Outlook_Launch.ums6
  • 20_Mailer/01_Outlook/Outlook_SendMail.ums6
  • 20_Mailer/01_Outlook/Outlook_Quit.ums6
  • 20_Mailer/01_Outlook/Outlook_AddAppointment.ums6
  • 08_Date/Date_GetFormmattedDate.ums6
  • 08_Date/Date_CountDaysBetweenDates.ums6
  • 18_Excel/GetValue2.ums6
  • 18_Excel/01_Book/Excel_OpenFile.ums6
  • 18_Excel/01_Book/Excel_CloseWithoutSaving.ums6
  • 18_Excel/03_Row_Column/Excel_CopyRow.ums6

* OR-5200 6.1 : 01_OR-5200_Control

Operating Environment

The operation of this sample scenario was confirmed in the following environment.

  • Windows 10
  • OR-5200 6.1.0, WinActor 7.1.1
  • Microsoft Outlook 2016

Restrictions

  • In order to execute this sample scenario, an internet environment that can send emails using Outlook 2016 is required.
  • When executing this sample scenario, a security warning may appear depending on the Outlook settings.
  • For details on Outlook settings and usage, refer to the Microsoft documentation.
  • The operation of this sample scenario with a large amount of data has not been verified. If you run into problems, try splitting the process by reducing the amount of data you run at one time.

How to use

The usage of this sample scenario is as follows.

STEP1 Outlook settings

Make sure that Outlook 2016 is ready to send emails to the "Destination" specified in Variable List.

STEP2 Preparation


Extracting the downloaded ZIP file and opening this sample scenario contained in the extracted folder.

STEP3 Setting variables required for operation

Update the values required to execute this sample scenario in Variable List. The variables that need to be set in advance when executing this sample scenario are as follows.

Variables for which setting confirmation is required

The following three variables need to be confirmed in advance when executing this sample scenario.

  • "NotifyByEmail"
    • When issuing a reminder, select whether to notify by email or add an appointment.
  • "Destination"
    • Specify the email address to send the reminder email to.
      Required when sending an email.
      Example) abcdefg@hijklmnopqrstu.co.jp
  • "InputFileName"
    • Specify only the file name of the Excel file to be entered.
      Place this file in the same directory as the sample scenario.
      Example) input_file.xlsx

Variables for changing behavior

In this sample scenario, there are variables to change the behavior.

If you use the attached input_file.xlsx as an input file, you can execute it with the default values. If you want to change the operation such as changing the format of the input file or changing the date when the reminder occurs, change the variables.

See here for details. 

Variable display screen

Variable display screen

STEP4 Executing the scenario

Start executing this sample scenario with WinActor(OR-5200). When the Excel check is completed and the sample scenario finds information that the scheduled end date is approaching, it sends an email or add a schedule.

Depending on the settings of security tools, etc., confirmation before sending an email may be required. In that case, please check manually.

STEP5 Confirming the status of the executed

Confirm that the reminder email is sent to the email address specified as the destination.
If Add Appointment is designated, make sure that the appointment has been added.

Trouble shooting

In this sample scenario, an error message will be displayed if a variable is specified incorrectly.

See the error message list for more information.

Explanation

This section describes the implementation technique used in this sample scenario.

Input file format

The input file (Excel) has the following format.

The first line is the header, and the second and subsequent lines are the input data. The item name in the header of the first line is not specifically referenced.

Column numberItem nameDisplay formatRemarks
A Name Normal string The name of the person who took it out.
In this sample scenario, this field is referred to as the "Name" column.
B Take-out start date Date format
yyyy/m/d
Not referenced in this sample scenario.
C Take-out end date (planned) Date format
yyyy/m/d
In this sample scenario, this field is referred to as the Expected End Date column.
Based on the comparison result of the difference between this item and the execution date, it is judged whether the completion date is near.
D Approval date Date format
yyyy/m/d
Not referenced in this sample scenario.
E Take-out end date (completed) Date format.
yyyy/m/d
Or empty
In this sample scenario, this field is referred to as the Completion Date column.
If the date is registered, it is considered completed.
If it is empty, it is considered not finished yet.
F Date confirmed Date format
yyyy/m/d
Not referenced in this sample scenario.


Input file sample (input_file.xlsx)

ABCDEF
1 Name Take-out start date Take-out end date (planned) Approval date Take-out end date (completion) Date confirmed
2 Aeams 2019/1/2 2019/1/25 2019/1/2 2019/1/25 2019/1/25
3 Beams 2019/2/1 2019/2/10 2019/2/1 2019/2/10 2019/2/10
4 Ceams 2019/2/25 2019/3/1 2019/2/25 2019/3/1
5 Deams 2019/3/1 2019/3/5 2019/3/1
6 Eeams 2019/3/5 2019/3/7 2019/3/5

In this sample scenario, the column number of the reference column ("NameColumn", "ScheduledEndDateColumn", "CompletionDateColumn") can be specified in Variable List. For this input file sample,

  • NameColumn: A
  • ScheduledEndDateColumn: C
  • CompletionDateColumn: E

are specified. When changing the format of the input file, change the reference column of Variable List according to the data to be referenced.

Precautions when creating your own input file

If you create your own input file, check that the character string on the display is correct. The display may be converted depending on the cell format of Excel. For the input file used in this sample scenario, format the date column as date type "yyyy / m / d".

Text creation procedure

The procedure for creating an email and a schedule text created by this sample scenario is as follows.

ClassificationOutput contentsHow to generate output contents
Top of text Some people are approaching the deadline for returning their equipment. The following people are close to the return deadline. Specified by the variable "BeginningOfOutgoingMessage"

Main text

Main text (Information on people whose deadline is approaching)
Ceams (scheduled end date: 2019/03/01) <Expired>
Deams (scheduled end date: 2019/03/05)
Created with this sample scenario.
"<Expired>" is specified by the variable "ExpiredWarning"
At the end of the text Please send a reminder today. Specified by the variable "EndOfOutgoingMessage"


Combine the beginning, center, and tail of the message shown above to generate an email or appointment warning message.

Information generation for people whose deadline is approaching

Information about people who are about to email or add messages to their appointments is created in the following part of the scenario. This process checks the Excel data line by line and executes it every time it finds information that is about to expire.

Text creation sequence

Text creation sequence

Creation example

Ceams (scheduled end date: 2019/03/01) <Expired> ← People who have passed the scheduled end date
Deams (scheduled end date: 2019/03/05) ← People whose scheduled end date is near

Combine text

After checking all the information in the Excel file, the entire text is combined immediately before sending an email or creating a schedule.

Full text creation sequence

Full text creation sequence

Creation example

Some people are approaching the deadline for returning their equipment. The following people are close to the return deadline. ← Send message beginning

Ceams (scheduled end date: 2019/03/01) <Expired> ← Information on people whose deadline is approaching: 1st line
Deams (scheduled end date: 2019/03/05) ← Information on people whose deadline is approaching: 2nd line

Please send a reminder today. ← End of sent message

Send email using Outlook

If the variable of "EmailNotification" is specified as "True" in Variable List, the email will be sent using Outlook operation of the WinActor(OR-5200) library in this sample scenario.

Note: Depending on your Outlook settings, you may get a security warning before sending an email.
This type of confirmation messages are not supported in this scenario.
Please check the contents and send an email.

Example of email sent

In this sample script, when information near the end date is found, the following email will be sent to the destination.

ClassificationOutput contentsHow to generate output contents
destination (Destination email address) Specified by the variable "Destination"
Subject [Reminder] The scheduled return date for the equipment to be taken out is near!! Specified by the variable "OutgoingMessageSubject"
Text (Sentence created in Text creation procedure) Refer to Text creation procedure


Add appointments in Outlook

If you want to receive notifications on a PC running this sample scenario, you can add an appointment to Outlook instead of an email reminder.
If you specify "False" for the variable of "EmailNotification" in Variable List, an appointment will be added in Outlook instead of sending an email.

Examples of appointments to be added

If you execute this sample scenario and add an appointment, the following appointment will be added.

ClassificationOutput contentsHow to generate output contents
Subject [Reminder] The scheduled return date for the equipment to be taken out is near!! Specified by the variable "OutgoingMessageSubject"
Place (Blank) No output
Start time (Scenario implementation date) (Scenario implementation time)
OR
(Scenario implementation date) 0:00
"Scenario implementation date" "Scenario implementation time"
When the variable "Schedule category 1" (all day) is specified, "Scenario implementation time" will be 0:00
End time
End time (Scenario implementation date) 23:59
OR
(Scenario implementation date) 0:00
"Scenario implementation date" 23:59

When the variable "ScheduledDivision" is specified as 1 (all day), "Scenario implementation date" 00:00
All day (If the variable "ScheduledDivision" is 1, there is a check) Variable "ScheduledDivision"
Text (Sentence created in Text creation procedure) Refer to Text creation procedure


Change the content to be sent

You can change a part of the email to be sent or the text of the appointment in Variable List. See here for details.

Variables that change behavior

You can change the behavior of this sample scenario by changing the following variables. When using the attached input_file.xlsx as an input file, there is no problem even if you execute it with the default settings.

Variable display screen

Display screen for variables

  • DataStartRow

    • Specify the first row number of the rows in which the data are stored in the Excel file to be entered.
      This sample scenario starts data acquisition from the specified row.
      Example) 2

  • NameColumn

    • Specify which column in the input Excel file is the name column.
      Specify in half-width alphabet.
      Example) A

  • ScheduledEndDateColumn

    • Specify which column in the input Excel file is the expected end date column.
      Specify in half-width alphabet.
      Example) C

  • CompletionDateColumn

    • Specify which column in the input Excel file is the completion date column.
      Specify in half-width alphabet.
      Example) E

  • NumberOfDaysForNear-endInvaluation

    • Specify the number of days before the scheduled end date to send the reminder in half-width numbers.
      Example 1

  • ScheduledDivision

    • Specify the type of schedule to be added.
      Specify 1 for all-day appointments and 0 for timed appointments.
      Specify with half-width numbers.
      Example 1

  • SubjectOfOutgoingMessage

    • Specify the subject of the reminder.
      It is also used as the subject when adding an appointment.
      Example) [Reminder] The scheduled return date for the equipment to be taken out is near!!

  • BeginningOfOutgoingMessage

    • Specify the character string to be inserted at the beginning of the reminder email text.
      Example) Some people are approaching the deadline for returning the equipment they brought out. The following people are close to the return deadline.

  • EndOfOutgoingMessage

    • Specify the character string to be inserted at the end of the reminder email text.
      Information about the scheduled end date is stored between the beginning and end of the outgoing message.
      Example) Please send a reminder today.

  • OutlookCloseFlag

    • If you have specified notifications by email, specify whether to close Outlook after sending the email.
      If Outlook has been started before executing this sample scenario, or if you want to add a schedule, this sample scenario will end without closing Outlook regardless of the flag value.
      Example) True
  • ExpirationWarning

    • If you want to give a message indicating the expiration date to the data whose expected end date has already passed, specify a character string in this variable.
      Example) <Expired>

Track changes

VersionDateDetails
1.0 10/27/2020 First edition