Sending Emails Using Excel

[Tex]

Hello Robotaskers,

I have a complex task I need help creating. I'm a total newb, so forgive me if this isn't actually as complex as I think it is.

I want Robotask to send appointment reminders to patients that are going to meet with a doctor in the facility during the week. I have an excel/csv file that has all the information I need: Patient name, patient email, Doctor name the patient is meeting with, facility name the patient will be attending, facility location, appointment day, beginning and ending time of said patient's appointment. This csv file contains this information for every patient that is going to be meeting with us during the week.

I want Robotask to be able to basically loop throughout this entire csv file (one row contains all the info necessary for one patient), and send emails to the right patient based on the info in this csv file until there are no more patients to send to. Basically, I want RoboTask to go to the first row (let's say this is patient 1), find their email, and put it in the "Send to" field when sending the email. Then, I want to it to take a text that I've already written and insert the information from the csv file where needed. Basically, the body of the email should look something like this

--
Hello (Insert Patient 1 Name),
This is (Insert Facility Name) located at (Insert Facility Location) cordially reminding you that you have an appointment with (Insert Dr. Name) on (Insert Day) from (Insert Beginning Time) to (Insert End Time).

Thank you.
--

If you have any questions or something is unclear let me know. Thank you for the effort and help!

[Oleg]

Yes, of course you can solve your problem with RoboTask.
You can get the data from Excel file or directly from CSV file.
BTW: the CSV file is simple text file and RoboTask can process it directly.

Save the text of the tasks into a files and use menu Task->Import to import the tasks into RoboTask.
Examples of CSV and XLS files in the attached ZIP file too.

Look at the examples below

The task for Excel file:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|317955339
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task230"
Hide=INTEGER|0
ID=INTEGER|1418794388
LogOnAsUser=INTEGER|1
Name=STRING|"mass emails (from excel)"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""EXCELFILE"" with value ""D:\Temp\mass emails\List.xls"""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"EXCELFILE"
varvalue=STRING|"D:\Temp\mass emails\List.xls"

[Actions\Action10]
ActionID=STRING|"A_INET_SENDMAIL"
Enabled=INTEGER|0
Name=STRING|"Send Email"
Params=FOLDER

[Actions\Action10\Params]
attachcount=STRING|"0"
auth=STRING|"0"
charset=STRING|"iso-8859-1"
from=STRING|"user@host.com"
host=STRING|"smtp.myhost.com"
html=STRING|"0"
msg0=STRING|"{txt}"
msgcount=STRING|"1"
password=STRING|"2565724685254542468326736"
passwordmode=STRING|"0"
port=STRING|"25"
SSL=STRING|"0"
subject=STRING|"A cordial reminder"
to=STRING|"{email}"
xmailer=STRING|"RoboTask"

[Actions\Action11]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

[Actions\Action2]
ActionID=STRING|"A_EXCEL_OPEN"
Enabled=INTEGER|-1
Name=STRING|"Excel Open Document"
Params=FOLDER

[Actions\Action2\Params]
filename=STRING|"{ExcelFile}"
hide=STRING|"0"
mode=STRING|"0"

[Actions\Action3]
ActionID=STRING|"A_LOOP_SIMPLE"
Enabled=INTEGER|-1
Name=STRING|"Simple Loop"
Params=FOLDER

[Actions\Action3\Params]
begin=STRING|"2"
end=STRING|"100"
variable=STRING|"ROW"

[Actions\Action4]
ActionID=STRING|"A_EXCEL_GET"
Enabled=INTEGER|-1
Name=STRING|"Excel Get Cells"
Params=FOLDER

[Actions\Action4\Params]
cell00000000=STRING|"R{Row}C1"
cell00000001=STRING|"R{Row}C2"
cell00000002=STRING|"R{Row}C3"
cell00000003=STRING|"R{Row}C4"
cell00000004=STRING|"R{Row}C5"
cell00000005=STRING|"R{Row}C6"
cell00000006=STRING|"R{Row}C7"
cell00000007=STRING|"R{Row}C8"
count=STRING|"8"
var00000000=STRING|"email"
var00000001=STRING|"patient"
var00000002=STRING|"location"
var00000003=STRING|"dt"
var00000004=STRING|"btime"
var00000005=STRING|"etime"
var00000006=STRING|"dr"
var00000007=STRING|"facility"
worksheet=STRING|"1"

[Actions\Action5]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER

[Actions\Action5\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"7"
type=STRING|"0"
value1=STRING|"{email}"

[Actions\Action6]
ActionID=STRING|"A_LOOP_BREAK"
Enabled=INTEGER|-1
Name=STRING|"Break"

[Actions\Action7]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"

[Actions\Action8]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value ""Hello {Patient},{EOL}This is {Facility} located at..."""
Params=FOLDER

[Actions\Action8\Params]
expand=STRING|"1"
varname=STRING|"TXT"
varvalue=STRING|"Hello {Patient},{EOL}This is {Facility} located at {Location} sending you a cordial reminder of your appointment taking place on {dt} from {btime} to {etime} with {dr}."

[Actions\Action9]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""Email: {email}"""
Params=FOLDER

[Actions\Action9\Params]
icon=STRING|"1"
msg0=STRING|"Email: {email}"
msg2=STRING|"Message body:"
msg3=STRING|"***********"
msg4=STRING|"{txt}"
msg5=STRING|"***********"
msg6=STRING|"Setup next step and enable it"
msgcount=STRING|"7"
playsound=STRING|"0"
showmessage=STRING|"1"

The example for CSV file:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|317955339
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task231"
Hide=INTEGER|0
ID=INTEGER|-1966386721
LogOnAsUser=INTEGER|1
Name=STRING|"mass emails (from CSV)"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action13=FOLDER
Action14=FOLDER
Action15=FOLDER
Action16=FOLDER
Action17=FOLDER
Action18=FOLDER
Action19=FOLDER
Action2=FOLDER
Action20=FOLDER
Action21=FOLDER
Action22=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""CSVFILE"" with value ""D:\Temp\mass emails\List.csv"""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"CSVFILE"
varvalue=STRING|"D:\Temp\mass emails\List.csv"

[Actions\Action10]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|0
Name=STRING|"Show ""{line}"""
Params=FOLDER

[Actions\Action10\Params]
icon=STRING|"1"
msg0=STRING|"{line}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action11]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""EMAIL"" with value ""{line(0)}"""
Params=FOLDER

[Actions\Action11\Params]
expand=STRING|"1"
varname=STRING|"EMAIL"
varvalue=STRING|"{line(0)}"

[Actions\Action12]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""PATIENT"" with value ""{line(1)}"""
Params=FOLDER

[Actions\Action12\Params]
expand=STRING|"1"
varname=STRING|"PATIENT"
varvalue=STRING|"{line(1)}"

[Actions\Action13]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""LOCATION"" with value ""{line(2)}"""
Params=FOLDER

[Actions\Action13\Params]
expand=STRING|"1"
varname=STRING|"LOCATION"
varvalue=STRING|"{line(2)}"

[Actions\Action14]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""DT"" with value ""{line(3)}"""
Params=FOLDER

[Actions\Action14\Params]
expand=STRING|"1"
varname=STRING|"DT"
varvalue=STRING|"{line(3)}"

[Actions\Action15]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""BTIME"" with value ""{line(4)}"""
Params=FOLDER

[Actions\Action15\Params]
expand=STRING|"1"
varname=STRING|"BTIME"
varvalue=STRING|"{line(4)}"

[Actions\Action16]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""ETIME"" with value ""{line(5)}"""
Params=FOLDER

[Actions\Action16\Params]
expand=STRING|"1"
varname=STRING|"ETIME"
varvalue=STRING|"{line(5)}"

[Actions\Action17]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""DR"" with value ""{line(6)}"""
Params=FOLDER

[Actions\Action17\Params]
expand=STRING|"1"
varname=STRING|"DR"
varvalue=STRING|"{line(6)}"

[Actions\Action18]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""FACILITY"" with value ""{line(7)}"""
Params=FOLDER

[Actions\Action18\Params]
expand=STRING|"1"
varname=STRING|"FACILITY"
varvalue=STRING|"{line(7)}"

[Actions\Action19]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value ""Hello {Patient},{EOL}This is {Facility} located at..."""
Params=FOLDER

[Actions\Action19\Params]
expand=STRING|"1"
varname=STRING|"TXT"
varvalue=STRING|"Hello {Patient},{EOL}This is {Facility} located at {Location} sending you a cordial reminder of your appointment taking place on {dt} from {btime} to {etime} with {dr}."

[Actions\Action2]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""FIRST"" with value ""true"""
Params=FOLDER

[Actions\Action2\Params]
expand=STRING|"0"
varname=STRING|"FIRST"
varvalue=STRING|"true"

[Actions\Action20]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""Email: {email}"""
Params=FOLDER

[Actions\Action20\Params]
icon=STRING|"1"
msg0=STRING|"Email: {email}"
msg2=STRING|"Message body:"
msg3=STRING|"***********"
msg4=STRING|"{txt}"
msg5=STRING|"***********"
msg6=STRING|"Setup next step and enable it"
msgcount=STRING|"7"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action21]
ActionID=STRING|"A_INET_SENDMAIL"
Enabled=INTEGER|0
Name=STRING|"Send Email"
Params=FOLDER

[Actions\Action21\Params]
attachcount=STRING|"0"
auth=STRING|"0"
charset=STRING|"iso-8859-1"
from=STRING|"user@host.com"
host=STRING|"smtp.myhost.com"
html=STRING|"0"
msg0=STRING|"{txt}"
msgcount=STRING|"1"
password=STRING|"2565724685254542468326736"
passwordmode=STRING|"0"
port=STRING|"25"
SSL=STRING|"0"
subject=STRING|"A cordial reminder"
to=STRING|"{email}"
xmailer=STRING|"RoboTask"

[Actions\Action22]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

[Actions\Action3]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER

[Actions\Action3\Params]
destvar=STRING|"LINE"
file=STRING|"{CSVFile}"
linecount=STRING|"0"
sourcetext=STRING|"0"

[Actions\Action4]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER

[Actions\Action4\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"0"
type=STRING|"3"
value1=STRING|"{first}"

[Actions\Action5]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//First line omited. This is a headers only"
Params=FOLDER

[Actions\Action5\Params]
comment=STRING|"First line omited. This is a headers only"

[Actions\Action6]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""FIRST"" with value ""false"""
Params=FOLDER

[Actions\Action6\Params]
expand=STRING|"0"
varname=STRING|"FIRST"
varvalue=STRING|"false"

[Actions\Action7]
ActionID=STRING|"A_LOOP_CONTINUE"
Enabled=INTEGER|-1
Name=STRING|"Continue"

[Actions\Action8]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"

[Actions\Action9]
ActionID=STRING|"A_VARIABLES_DELIMITED"
Enabled=INTEGER|-1
Name=STRING|"Delimit Variable"
Params=FOLDER

[Actions\Action9\Params]
colon=STRING|"0"
comma=STRING|"0"
other=STRING|"0"
quote=STRING|"0"
semocolon=STRING|"1"
space=STRING|"0"
spaceasone=STRING|"0"
string=STRING|"{line}"
tab=STRING|"0"
variable=STRING|"line"

Full archive of examples (with CSV and XLS files) here:

mass emails.zip

(8.49 KiB) Downloaded 1218 times