Table insertion in outlook

@jpap @Suniel I am trying to copy table from excel sheet and want to paste in mail using robin OutlookSendEmail command is it possible to do it ?
I tried using Clipboard.GetText commad its giving output as a text not as a table
Suggest a solution which does not include use of Appmask file

@amunje try the following script:

image

The body of the email is still going to be in text format.
If you want to paste it as a data table you are going to have to utilize UISpy to simulate a “natural”, human-like approach (copy from excel, go to email, paste in body).

Best regards,
J.

3 Likes

Here is another possible approach, @amunje, to inserting a table in an Outlook message.

Keyboard accelerators work very well in both Outlook and Excel, so we can take advantage of them. For this we don’t even need an appmask.

(EDIT: This may require further work as I haven’t yet been able to get the whole table to display once copied over. It appears in the spreadsheet, but disappears when formatting is activated.
I’ll post if I find a good remedy.)

Steps:

  • Open Excel and load our stock table
  • Copy the whole sheet to clipboard
  • Save clip to a variable
  • Write a debug console message
  • Open Outlook
  • Create a new message with “Ctrl-N”
  • Tab into the body (next step won’t work otherwise)
  • Send “Alt-N” to activate the ribbon accelerator keys
  • Send “T” for Table
  • Send “X” for Excel spreadsheet
  • Copy the saved table data to the new spreadsheet
  • Send “Esc” to activate the data transformation

As you can see, I haven’t made any effort to fill in the “To”, “CC” or “Subject” fields, which I’m sure you’ll have no trouble with.

Also, you’ll get better table results in Outlook if you format your stock table in Excel, and I know you can do that as well.

Here’s a screenshot of the code:

The code:

# Open our stock data file.
Excel.LaunchAndOpen \
    Path: "C:\work\RobinTests\USAStocksData.xlsx" \
    Visible:True \
    Password:'' \
    ReadOnly:False \
    LoadAddInsAndMacros:False \
    Instance=> ExcelInstance
wait 1  # Select the whole sheet.
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Control}({A})" DelayBetweenKeystrokes:10
wait 0.5 # Copy the whole sheet
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Control}({C})" DelayBetweenKeystrokes:10
wait 0.2 # Write the table to clipboard
Clipboard.GetText Text=> TableContents
wait 0.5 # Debug console write
Console.Write Message: TableContents
wait 0.2  # We're done with Excel
Excel.Close Instance: ExcelInstance
wait 0.2  # Launch Outlook
Outlook.Launch Instance=> OutlookInstance
wait 3  # Send keys to open a new message
MouseAndKeyboard.SendKeys \
    TextToSend: "{Control}({N})" DelayBetweenKeystrokes:10
wait 2  # Tab down to doc body (critical)
MouseAndKeyboard.SendKeys \
    TextToSend: "{Tab}{Tab}{Tab}" DelayBetweenKeystrokes:10
wait 1 # Activate the ribbon accelerator keys
MouseAndKeyboard.SendKeys \
    TextToSend: "{Alt}({N})" DelayBetweenKeystrokes:10
wait 1 # 'T' for Table
MouseAndKeyboard.SendKeys \
    TextToSend: "T" DelayBetweenKeystrokes:10
wait 1 # 'X' for Excel spreadsheet
MouseAndKeyboard.SendKeys \
    TextToSend: "X" DelayBetweenKeystrokes:10
wait 1 # Copy the table contents to the spreadsheet
MouseAndKeyboard.SendKeys \
    TextToSend: "{Control}({V})" DelayBetweenKeystrokes:10
wait 2 # Send "Esc" to activate the transformation.
MouseAndKeyboard.SendKeys \
    TextToSend: "{Escape}" DelayBetweenKeystrokes:10

and an animated GIF of part of the robot’s run.

Best regards,
burque505

2 Likes

If you don’t want to insert an excel object in an email but rather copy the values and display them in a tabular format, you could create your own HTML table and set the ‘IsBodyHtml’ attribute to ‘True’:

Folder.GetSpecialFolder SpecialFolder:Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> Desktop

Excel.LaunchAndOpen Path:Desktop+'\New Microsoft Excel Worksheet.xlsx'  Visible:True Password:'' ReadOnly:False LoadAddInsAndMacros:False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance:ExcelInstance  FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadCells Instance:ExcelInstance  StartColumn:1  StartRow: 1 EndColumn:FirstFreeColumn-1  EndRow:FirstFreeRow-1  ReadAsText:False FirstLineIsHeader:False Value=> Value

Excel.Close Instance: ExcelInstance

#create table and headers
set htmlTable to '<table><tr>'
loop i from 0 to FirstFreeColumn-2
    set htmlTable to htmlTable+'<th>'+Value[0][i]+'</th>'
end
set htmlTable to htmlTable+'</tr>'


#create table rows
loop j from 1 to FirstFreeRow-2
    set htmlTable to htmlTable+'<tr>'
        loop foreach cell in Value[j]
            set htmlTable to htmlTable+'<td>'+cell+'</td>'
        end
    set htmlTable to htmlTable+'</tr>'
end

set htmlTable to htmlTable+'</table>'


Outlook.Launch Instance=> OutlookInstance
Outlook.SendEmail Instance:OutlookInstance  Account:'Insert Outlook Account Here'  SendTo:'Inser Recipient Here'  CC:'' BCC:'' Subject:'table test' Body:htmlTable IsBodyHtml:True Attachments:''
Outlook.Close Instance: OutlookInstance
2 Likes

@Skip, nice one! I have also tried saving the Excel file as .htm, opening the resulting .htm file in the AutomationBrowser, selecting all, copying and pasting into Outlook. It works, but it is an utter kludge.

Best regards,
burque505

3 Likes

I can’t seem to let this one go. :grinning:

After looking through plenty of ways found on the web to export .xlsx to HTML, I decided (for some reason) to try LibreOffice. (Excel’s export of sheets to HTML is hideous.)

With very little effort, you can massage the “USAStocksData.xlsx” we use as a guinea pig into something that looks like this:

LibreOffice Calc also lets you preview the file in the browser without having to export anything.
So I just copy and paste from there into Outlook.

Looks like this:

Not bad!

Here’s the code, which will take some explaining at the end:

# Run LibreOffice Calc
System.RunApplication \
    ApplicationPath: "C:\Program Files\LibreOffice\program\scalc.exe" \
    CommandLineArguments: "C:\work\RobinTests\USAStocksData.xlsx" \
    WorkingDirectory:'' WindowStyle:System.ProcessWindowStyle.Maximized \
    ProcessId=> LoId 
wait 5
# Focus sheet without needing a UI element
MouseAndKeyboard.ClickAt \
    ClickType:MouseAndKeyboard.MouseClickType.LeftClick \
    MillisecondsDelay:0 X: 450 Y: 500 \
    RelativeTo:MouseAndKeyboard.PositionRelativeTo.Screen \
    MovementStyle:MouseAndKeyboard.MovementStyle.Instant
wait 1
#Select All
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Ctrl}{A}" \
    DelayBetweenKeystrokes:10
wait 0.5
# Send keystrokes to autoformat sheet
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Alt}{O}{F}{Enter}" \
    DelayBetweenKeystrokes:10
wait 0.2
# Non-robust column selection
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Up}{Up}{Up}{Left}{Ctrl}{Space}" \
    DelayBetweenKeystrokes:10
wait 0.2
# Open format cells dialog
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Ctrl}{NumPad1}" \
    DelayBetweenKeystrokes:10
wait 3
# Format column G as Time
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Down}{Down}{Down}{Down}{Enter}" \
    DelayBetweenKeystrokes:10
wait 2
# Preview in browser
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Alt}{F}{B}" \
    DelayBetweenKeystrokes:10
wait 2
# Focus without needing UI element
MouseAndKeyboard.ClickAt \
    ClickType:MouseAndKeyboard.MouseClickType.LeftClick \
    MillisecondsDelay:0 X: 450 Y: 500 \
    RelativeTo:MouseAndKeyboard.PositionRelativeTo.Screen \
    MovementStyle:MouseAndKeyboard.MovementStyle.Instant
# Select all and copy
MouseAndKeyboard.SendKeys \
    TextToSend:  "{Ctrl}{A}{Ctrl}{C}" \
    DelayBetweenKeystrokes:10
wait 0.5
# Save the clipboard
Clipboard.GetText Text=> ClipboardText
wait 0.1
# Debug - write the table to console
Console.Write Message: ClipboardText
# Open Outlook
Outlook.Launch Instance=> OutlookInstance
wait 3  # Send keys to open a new message
MouseAndKeyboard.SendKeys \
    TextToSend: "{Control}({N})" DelayBetweenKeystrokes:10
wait 2  # Tab down to doc body (critical)
MouseAndKeyboard.SendKeys \
    TextToSend: "{Tab}{Tab}{Tab}" DelayBetweenKeystrokes:10
wait 0.2  # Tab down to doc body (critical)
MouseAndKeyboard.SendKeys \
    TextToSend: "{Control}{V}" DelayBetweenKeystrokes:10
wait 5
# 
Outlook.Close Instance: OutlookInstance

MouseAndKeyboard.SendKeys TextToSend:  "{Tab}{Enter}" DelayBetweenKeystrokes:10
# Get rid of processes I couldn't kill
System.RunApplication \
    ApplicationPath:  "AutoHotkey.exe" \
    CommandLineArguments:'"C:\work\RobinTests\KillLoFo.ahk"' \
    WorkingDirectory:'' \
    WindowStyle:System.ProcessWindowStyle.Normal \
    ProcessId=> ProcessId

Now for the apology/explanation:

First, I didn’t investigate very thoroughly on how to select a column programmatically, so the “Time (EST)” was selected by keystrokes that may well be unique to my system (nowhere else to test it). If you don’t need to format any column’s cells, you can easily remove this step. (This code could certainly benefit from an appmask, but the OP specifically asked for a non-appmask solution.)

Second, I just couldn’t kill Firefox and LO! So I resorted to AutoHotkey, which works great for that kind of thing. Here’s the AutoHotkey script referenced as “KillLoFo.ahk” in the code:

; Run this script as an administrator
full_command_line := DllCall("GetCommandLine", "str")

if not (A_IsAdmin or RegExMatch(full_command_line, " /restart(?!\S)"))
{
    try
    {
        if A_IsCompiled
            Run *RunAs "%A_ScriptFullPath%" /restart
        else
            Run *RunAs "%A_AhkPath%" /restart "%A_ScriptFullPath%"
    }
    ExitApp
}

; If the calc window exists, kill it.
If (WinExist("USAStocksData.xlsx - LibreOffice Calc")) {
	WinKill, USAStocksData.xlsx - LibreOffice Calc
    sleep 1000
    Send, {Tab}{Enter}  ; Get rid of save changes dialog
}

; If firefox exists, kill it
If (WinExist("ahk_exe firefox.exe")) {
	WinKill, ahk_exe firefox.exe
}

ExitApp

EDIT: I was able to dispense with the AutoHotkey code, after discovering the proper way to terminate processes (Firefox too, just not shown).

system_bin

Regards,
burque505

2 Likes

@jpap @Skip I want to insert that table in this format and kindly tell me how to insert text in body part of Outlook.SendEmail command.

Take a look at the very nice solution @burque505 provided, just some answers above.

Best Regards,
J.

1 Like