Outlook as a microservices server

Outlook as a microservices server

Have a good day.

You are our only programmer!
A small introduction. I download data from MS SQL Server for friends, at their request. Friends provide raw data that needs to be downloaded in .csv files. Output data (ID) can be from 1 to … lines. I loaded data into a SQL table using a task or Task in the English version in SQL Server Management Studio (SSMS). Historically, all SQL script files are stored on my PC. I opened the files in SSMS and ran the execution. I wrote the results in a file and sent a message that the processing was completed. Friends took the files with the results.

But one creative day, the idea came to automate this process so that the Friends would do everything themselves, with minimal involvement from me.

A C# + VBA program was written for Outlook that does all this. I will briefly describe them below. The program is written as a console program, no dialogue with the user is expected.

Below is a description of the main points of the program.

Active faces
Friends:
Ivanov Ivan Ivanovych, his email
[email protected]
Petrov Petro Petrovych, his email
[email protected]
Program:
SuperPuperProgram spp.exe
I:
Vasyliev Vasyl Vasyliovych

1. Since the selection must be made for several friends (theoretically, 1 is actually launched), it is necessary to determine for whom the program is launched. Why the friend is passed as an argument in the command line will be explained a little later.

switch (args[0]) 

case "Иванов": 
   strEmail = [email protected]
    break;     case "Петров": 
    strEmail = [email protected]
    break; 

2. Since a single table is used to enter the ID, it is necessary to exclude the launch of several copies of the program. (It is possible to create a unique sql table for each processing to enter the VD, but considering that this unloading is required several times, maybe a day, maybe a week, and 1 person deals with it, I decided to use a Mutex (as previously unknown).

We declare the variable isNew and by the value returned from the Mutex function we determine whether the instance of the program is already running. If the instance is already running, release Mutex resources, form a response line and send an email to whom the program was launched, exit the program.

bool isNew; 
var mutex = new System.Threading.Mutex(true, "SuperPuperProgram", out isNew); 
if (isNew) 

    InstanceCheckMutex = mutex; 

else 

    mutex.Dispose(); 
    strTextOut += "Программа уже запущена !!!<br />Одновременный запуск нескольких экземпляров программы невозможен !!!<br />Запуск отменен<br />"; 
    sendEMailThroughOUTLOOK(ref strTextOut, strEmail); 
    return; 

There are 2 folders in the folder with the program, let’s call them the following: the folder of output data (PID) and the folder of results (PR). In the PID folder there are csv files from the VD, in the PR folder files with the results will be written.

We get a list of csv files from the PID and for each file from the list

foreach (string s1 in csv_In) 

    using (System.IO.FileStream fs = System.IO.File.OpenRead(s1)) 
    { 

We define the encoding of the file. I used Ude.NET to determine the encoding of the file.

Encoding e1 = DetectFileEncoding(s1);

Import data will help make bcp.exe. Using the scientific tick method, a working version of the bcp.exe execution process was found.

System.Diagnostics.ProcessStartInfo processStartInfo = new System.Diagnostics.ProcessStartInfo(); 
        processStartInfo.FileName = @"C:\Windows\system32\cmd.exe"; 
        if (e1.EncodingName.Contains("UTF")) 
        { 
            processStartInfo.Arguments = "/c bcp.exe " + "[sqlTable] in \"" + s1 + "\" -S sqlServer -T -c -C " + e1.CodePage + " /t"; 
        } 
        else 
        { 
            processStartInfo.Arguments = "/c bcp.exe " + "[sqlTable] in \"" + s1 + "\" -S sqlServer -T -c -C " + e1.WindowsCodePage + " /t"; 
        } 
        processStartInfo.CreateNoWindow = true; 
        processStartInfo.UseShellExecute = false; 
        processStartInfo.RedirectStandardOutput = true; 
        System.Diagnostics.Process process = new System.Diagnostics.Process(); 
        process.StartInfo = processStartInfo; 
        process.Start(); 
        string output = process.StandardOutput.ReadToEnd(); 
        process.WaitForExit(); 

VD in sqlTable entered.

Now we connect to SQL Server and execute the SQL script.

strSQLfile – file with sql script.

const string strSQLfile = "path\\file.sql"; 
using (System.IO.StreamReader fr = System.IO.File.OpenText(strSQLfile)) 
{

Enter data from strSQLfile into the queryString variable. queryString is a string that contains the query text. All comments starting with “–” are deleted until the end of the line. If not, they will comment out the rest of the queryString).

We leave the comments /*…*/, they comment part of the code and do not affect the rest of the queryString line, they are not marked during execution. (I rarely use comments like this, so the size of the queryString won’t increase much.)

string str1 = ""; 
while ((str1 = fr.ReadLine()) != null) 

str1 = str1.Replace("\t", " "); 
if (str1.Length > 2) 

//не берем закомментированные строки целиком 
if (str1[0] != '-' && str1[1] != '-') 

if (str1.Contains("--"))        //убираем комментарии в строке до конца строки, внутренние комментарии /*...*/ остаются 

str1 = str1.Substring(0, str1.IndexOf("--")); 

queryString += str1 + " "; 


else 

if (str1.Contains("--"))        //убираем комментарии в строке до конца строки, внутренние комментарии /*...*/ остаются 

str1 = str1.Substring(0, str1.IndexOf("--")); 

queryString += str1 + " "; 


Now we have in queryString the complete text of the query that can be executed.
We create a connection to sqlServer. Our connection is Trusted_Connection=Yes, other types of authorization are rejected by the server. The option to connect to the server using a login and password is no longer available. Friends are not registered on the server and the option of registering them on the server is not even considered. If Friends run the program on their own, then nothing will happen. Running the program on behalf of another user is also not the best option. So the only option is to run on my behalf on my PC.

Below, my view is nothing interesting: we create a connection, set parameters, set Connection Timeout=3000, because the execution time is unknown. As I said, the input can have 10 lines and 99999999.

using (var connection = new QC.SqlConnection("Data Source = sqlServer;" + "Initial Catalog=sqlDB;" + "Trusted_Connection=Yes;" + "Connection Timeout=3000;")) 

QC.SqlCommand command = new QC.SqlCommand(queryString, connection); 

We determine where we will write the result and file encoding.

System.IO.StreamWriter sw = new System.IO.StreamWriter(fileOut.csv, false, Encoding.GetEncoding(1251)); 

We open the connection.

connection.Open(); 
if (connection.State == DT.ConnectionState.Open) 

If the connection is opened successfully, execute queryString.

QC.SqlDataReader reader = command.ExecuteReader(); 

We write a hat in the conclusion.

sw.WriteLine("..."); 
while (reader.Read()) 

We write the data in the conclusion.

sw.WriteLine("{0};...", reader[0],...); 
iRet += 1; 

We close everyone.

reader.Close(); 

sw.Close(); 
connection.Close(); 

We take the next file into work (go to foreach (string s1 in csv_In)).
After all files are processed, we send a letter with the results. I do not attach the files with the results to the letter, as their size may exceed the attachment limit.

sendEMailThroughOUTLOOK(ref strTextOut, strEmail);

The program is configured, works, scripts are executed, letters are sent. All that remains is to make the program run on the computer by Friends. A creative idea appeared – to use Outlook for this. IMPORTANT – “Enable all macros” must be enabled in Outlook’s “Macros Options”. The scheme looked like this – a colleague sends me a letter with a code word in the subject of the letter, Outlook analyzes the sender, the subject of the letter and, if the conditions match, starts the program. All that remains is to implement what was planned.

Let the code word be SuperPuperProgram.

In Outlook, insert into ThisOutlookSession

Private WithEvents myOlItems  As Outlook.Items 
Private Sub Application_Startup() 
Dim olApp As Outlook.Application 
Dim objNS As Outlook.NameSpace 
Set olApp = Outlook.Application 
Set objNS = olApp.GetNamespace("MAPI") 
Set myOlItems = objNS.GetDefaultFolder(olFolderInbox).Items 
End Sub 
 
Private Sub myOlItems_ItemAdd(ByVal Item As Object) 
On Error GoTo ErrorHandler 
  Dim Msg As Outlook.MailItem 
  Dim Ret_Val 
  If TypeName(Item) = "MailItem" Then 
    Set Msg = Item 

We check the subject of the letter

If Msg.Subject = "SuperPuperProgram" Then 

We check the recipient

If Msg.To = "Васильев Василий Васильевич" Then 

If the sender passed the check, run the program and pass Msg.Sender.Name as a parameter. This parameter will determine the email, to whom to send letters and who launched the program.

If Msg.Sender.Name = "Иванов Иван Иванович" Then 
                Ret_Val = Shell("spp.exe " + Msg.Sender.Name) 
            End If 
            If Msg.Sender.Name = "Петров Петр Петрович" Then 
                Ret_Val = Shell("spp.exe " + Msg.Sender.Name) 
            End If 
        End If 
    End If 
ProgramExit: 
  Exit Sub 
ErrorHandler: 
  MsgBox Err.Number & " - " & Err.Description 
  Resume ProgramExit 
End Sub 

It turns out that Outlook can be used as a microservices server. Requests are letters to Friends, work results are in the results folder (PR), interaction (informing) Friends by means of emails.

I think that if you approach the idea of ​​Outlook as a server of micro services creatively, you can reward something like that!!!

As an option, I have another program that parses the data and starts it according to the letter (request).
Thank you for your attention, I will be glad if my work was not in vain and will be useful to someone.

Related posts