Jump to content
Sign in to follow this  
sackjarrow

Help with formatting text in e-mail

Recommended Posts

sackjarrow

Hi!

I'm new to AutoIT and only know the very basics, barely. This is why I need some help here with writing a script which formats text in e-mails in Outlook 2010. I'd rather not use any UDF's or any other downloadable content since I'm new to this and would like to keep things relatively simple.

Problem:

I receive e-mails containing a form, where different users have filled in different data. Say there are 20 fields to fill in. Each field is on a seperate row and if there is no text put in in a field the form itself puts the text "ingen". It is this text and the whole row of which it is connected to I wish to remove. As of now I do this manually.

I guess it's easiest to illustrate with an example.

Unformatted version of e-mail:

Firstname_orderer :   John
Lastname_orderer :   Doe
Tel_orderer :   123456
Firstname_user :   Jim
Lastname_user :   Harper
E-mail_user :   jim_harper@example.com
Tel_user :   654321
Software :   MS Word 2010
Local :   on
Access to program 1 :   none
Access to program 2 :   none
Access to program 3 :   none
Access to program 4 :   none
Access to program 5 :   none
Application rights 1 : 
Application rights 2 : Read/Write
Application rights 3 : 
Other information 1 : on
Other information 1 : off
Other information 1 : off
Other information 1 : off
Other information 1 : on
Other information 1 : off
Other information 1 : off
Other information 1 : off
Other information 1 : off
Send                : Send
 
Formatted version of e-mail:
 
Firstname_orderer :   John
Lastname_orderer :   Doe
Tel_orderer :   123456
Firstname_user :   Jim
Lastname_user :   Harper
E-mail_user :   jim_harper@example.com
Tel_user :   654321
Software :   MS Word 2010
Local :   on
Application rights 2 : Read/Write
Other information 1 : on
Other information 1 : on

 

So as you can see I basically want a script that deletes all unnecessary info, e.g all lines that are either blank or ending in "off" or "none". Is there a way to format it this way? Any suggestions for functions that might be helpful? I'm using Outlook 2010 so as far I've come to understand I'm not able to interact directly with the program, but I can use send commands to e.g paste the contents of the e-mail into a notepad document and then start editing? Any help would be appreciated.

/Sack

Share this post


Link to post
Share on other sites
water

Forget using Send commands. That doesn't lead to reliable scripts.

Using UDFs is quite easy. With my Outlook UDF you can easily do what you want to do.

Just a few questions:

  • As you can't change a received mail, do you want to create anew mail or write the modified mail text to a file?
  • How do you select the mails to be processed? All mails that arrive? Only the mails you select in the inbox?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

:) Ok I guess I'll have to read up on UDF's then.

1. I forward the e-mails to other addresses, but edit them before I forward them.

2. Only the mails that I select in the inbox

Right now I'm using send scripts allover the place since that's the only way I can interact with e.g web/browser-based applications (or other non-windows applications for that matter).

Edited by sackjarrow

Share this post


Link to post
Share on other sites
water

How would you like to start processing the mails?

Start the AutoIt script, select all relevant mails and then hit a key combination?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

Well, I would like to bind it to an Outlook-specific hotkey so that when I have an e-mail I want to edit and forward in my inbox, I press the key/keycombination which then opens the selected e-mail and then edits it basically. I would like to have a chance to review the mail before I press send.

Edited by sackjarrow

Share this post


Link to post
Share on other sites
water

Will post an example as soon as I return to my office.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Which format is the mail you receive? HTML, RTF or Text only?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Queener

hey water, would it be easier to implement his request in iniread/iniwrite with your Outlook UDF? Reason I like to use iniread/iniwrite is because it much easier for me to assign a written value to each field. Other method can be used as well, but ini to me is easy. I'll just wait until he post his example of the fields and I'll come-up with a quick code.

Only thing I'm not sure as of now is when he recieved the email; does it comes in the body to be fill in or attachment.

Edited by asianqueen

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
water

IniRead/IniWrite is completely unrelated to Outlook mails.

How do you htink this could help?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

Maybe I was a bit unclear about the form I'm receiving :) :

The users are the ones filling out an online web-form and when they press send I receive a mail in my inbox. This mail just contains plain text just as if someone would've written me an ordinary e-mail. The format is Text only and it's exactly as if I would've pasted what I posted above in an e-mail and sent it. Hope this helps!

Share this post


Link to post
Share on other sites
Bert

ummm, maybe a better way to go is to use a different type of web form. Currently I use FormAssembly to do this. The reason I suggest this is for several reasons:

1 - you can get a free account. They have pay versions also if you want. The fee per year is very low.

2 - EASY to setup and use. I mean EASY. Form Creation is a simple drag and drop.

3 - You can make dependent fields. In other words you have a base question and if the user answers it by picking certain values then a dependent question appears. By using this you can remove all the fields that the user does not answer.

4 - Make fields required. You can pick and choose what fields you want the users to answer before the submit the form for review by you.

The end result is you get an email that only has what you want and nothing else. If you need to forward the email or have other people included you would not need to run a script to get what you need.

Here is a video on Youtube showing how easy it is to use.

http://www.youtube.com/watch?v=a9gKodb3IeI

Share this post


Link to post
Share on other sites
water

First try. Run the script, select item(s) to process, press shift+alt+s to start processing the selected items.

Press shift+alt+e to end the script.

#include <OutlookEX.au3>
#include <Array.au3>

HotKeySet("+!s", "_ProcessSelected") ;Shift-Alt-s to start processing the selected mails
HotKeySet("+!e", "_Exit") ;Shift-Alt-e to end the script

$oOL = _OL_Open() ; Open connection to Outlook
While 1
    Sleep(50)
WEnd

; Process all selected items if  shift+alt+s is pressed
Func _ProcessSelected()
    Local $aSelection = _OL_FolderSelectionGet($oOL)
    For $i = 1 To $aSelection[0][0]
        _ProcessItem($aSelection[$i][0])
    Next
EndFunc   ;==>_ProcessSelected

; Process a single item
Func _ProcessItem($oItem)
    Local $aLine
    Local $aResult = _OL_ItemGet($oOL, $oItem, Default, "Body") ; get the body from the item
    Local $aBody = StringSplit($aResult[1][1], @CRLF, 1) ; Split the body into an array
    For $i = $aBody[0] To 1 Step -1 ; Process the array from end to start (needed because I delete array entries)
        $aLine = StringSplit($aBody[$i], ":", 1) ; split at ":"
        If @error = 1 Or $aLine[0] < 2 Then ; @error = 1 means: There is no ":" in the line => remove
                    _ArrayDelete($aBody, $i)
        Else
            $aLine[2] = StringReplace($aLine[2], Chr(160), " ") ; remove unreadable characters
            $aLine[2] = StringStripWS($aLine[2], 3) ; Strip off spaces
            If $aLine[2] = "" Or $aLine[2] = "none" Or $aLine[2] = "off" Then _ArrayDelete($aBody, $i) ; Remove unwanted records
        EndIf
    Next
    $aBody[0] = UBound($aBody, 1) - 1 Set element 0 to the new number of rowse in the array
    ; Forward the mail
    Local $oForward = _OL_ItemForward($oOL, $oItem, Default, 0) ; create a new mail to forward
    _OL_ItemModify($oOl, $oForward, Default, "Body=" & _ArrayToString($aBody, @CRLF, 1)) ; Modify the new mail and set the Body property
    ; If needed the recipient can be added here
    $oForward.Display ; Display the new mail
EndFunc   ;==>_ProcessItem

; Exit script when shift+alt+e is pressed
Func _Exit()
    _OL_Close($oOL) ; Close connection to Outlook
    Exit
EndFunc   ;==>_Exit
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

O dear god what have you done, not even sure I dare press the button on this, my knowledge in AutoIT is more or less limited to send, winactivate, winwaitactive and more send. No I'm just kidding (not about my knowledge), I'll let you know when BSOD appears  :sweating:

Share this post


Link to post
Share on other sites
water

I've added a lot of comments for easier understanding.

The script still misses some error checking but you can't do anything wrong. It doesn't modify an existing mail or send the new mail.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

Alright, cool. This is awesome.

I've managed to install your UDF OutlookEX. I did have to move OutlookExConstants.au3 to %autoit%include for it to work which did not say in the readme file, or else I got an error saying the file was missing/unable to open.

The script seems to be working as intended :) There are however a few customizations I would like to add which I believe would belong in the  _ProcessItem section.

Firstly as you can see below I added some more criteria for when a row should be deleted. However this "if" only seems to be checking text after the ":" (colon), how do I get another set of textchecking for text before the ":"?

If $aLine[2] = "" Or $aLine[2] = "none" Or $aLine[2] = "off" or $aLine[2] = "Les/søk" or $aLine[2] = "Ingen" Then _ArrayDelete($aBody, $i)

Say I e.g wanted to remove this line "FileDirectories : on", because it says "FileDirectories" before the colon, not because it says "on" after the colon, since there are other lines which i wish to keep that also says "on" after the colon.

Secondly, how would I add a recipient for the mail to be forwarded to? Say I wanted to forward the mail to jim.harper@test.com, how would the line below look?

_OL_ItemModify($oOl, $oForward, Default, "Body=" & _ArrayToString($aBody, @CRLF, 1)) ; Modify the new mail and set the Body property
    ; If needed the recipient can be added here 

Thank you for all your work this is immensely helpful!

Share this post


Link to post
Share on other sites
water

It is not necessary to copy OutlookEXConstants.au3 to %autoit%include. AutoIt searches in three directories for include files:

  • %autoit%include
  • the user defined include directory
  • the directory where the script resides

Copy OutlookEX.au3 and OutlookEXConstants.au3 to the user defined include directory or the script directory and the script should run fine.

The text before ":" is in $aLine[1], the whole line in $aBody[$i].

To set a recipient use

_OL_ItemRecipientAdd($oOl, $oForward, Default, $olTo, "jim.harper@test.com")

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

Working like a charm. I noticed that the mail forwarded by the script doesn't include time-/datestamp for when the original message was received, which a forward issued by me manually does (Norweigan):

"

-----Opprinnelig melding-----

Fra: Harper Jim

Sendt: 2. juli 2013 11:04

Til: Smith Jack

Emne: VS: Bestilling av ny/endret brukertilgang

Firstname_orderer :   John
Lastname_orderer :   Doe
Tel_orderer :   123456
Firstname_user :   Jim
Lastname_user :   Harper
E-mail_user :   jim_harper@example.com
Tel_user :   654321
Software :   MS Word 2010

"

The date-/timestamp is just plain text.

Is there any way to add this in the script aswell?

Edited by sackjarrow

Share this post


Link to post
Share on other sites
water

Sure. Will post an example as soon as I find some spare time.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

The script - as we have it now - overwrites the body so the original body is lost. You need to extract the date/timestamp from the body of the forwarded mail.
Something like:

; Process a single item
Func _ProcessItem($oItem)
    Local $aLine
    Local $aResult = _OL_ItemGet($oOL, $oItem, Default, "Body") ; get the body from the item
    Local $aBody = StringSplit($aResult[1][1], @CRLF, 1) ; Split the body into an array
    For $i = $aBody[0] To 1 Step -1 ; Process the array from end to start (needed because I delete array entries)
        $aLine = StringSplit($aBody[$i], ":", 1) ; split at ":"
        If @error = 1 Or $aLine[0] < 2 Then ; @error = 1 means: There is no ":" in the line => remove
                    _ArrayDelete($aBody, $i)
        Else
            $aLine[2] = StringReplace($aLine[2], Chr(160), " ") ; remove unreadable characters
            $aLine[2] = StringStripWS($aLine[2], 3) ; Strip off spaces
            If $aLine[2] = "" Or $aLine[2] = "none" Or $aLine[2] = "off" Then _ArrayDelete($aBody, $i) ; Remove unwanted records
        EndIf
    Next
    $aBody[0] = UBound($aBody, 1) - 1 Set element 0 to the new number of rowse in the array
    ; Forward the mail
    Local $oForward = _OL_ItemForward($oOL, $oItem, Default, 0) ; create a new mail to forward
    ; Get the body from the forwarded mail
    Local $aBodfyForwarded = _OL_ItemGet($oOL, $oForward, Default, "Body")
    ; >>> Here you have to extract the needed information from $aBodyForwarded and add it to $aBody <<<<
    _OL_ItemModify($oOl, $oForward, Default, "Body=" & _ArrayToString($aBody, @CRLF, 1)) ; Modify the new mail and set the Body property
    ; If needed the recipient can be added here
    $oForward.Display ; Display the new mail
EndFunc   ;==>_ProcessItem

You need to add code to extract the needed information from the forwarded mail at the lines marked with ">>>".
 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sackjarrow

Wohoo I extracted the text all by my self, went and had a read in the Array.au3, didn't know all these .au3-files existed with UDF's!

Here's how it looks now:

; Forward the mail
    Local $oForward = _OL_ItemForward($oOL, $oItem, Default, 0) ; create a new mail to forward
    Local $aBodfyForwarded = _OL_ItemGet($oOL, $oForward, Default, "Body")
    Local $aNewBody = Stringsplit($aBodfyForwarded[1][1], @CRLF, 1)
    _ArrayDisplay($aBody)
    For $b = 8 to 2 step -1
       _ArrayInsert($aBody, 1, $aNewBody[$b])
    Next
    _ArrayDisplay($aBody)
    ; >>> Here you have to extract the needed information from $aBodyForwarded and add it to $aBody <<<<
    _OL_ItemModify($oOl, $oForward, Default, "Body=" & _ArrayToString($aBody, @CRLF, 1)) ; Modify the new mail and set the body properly
    _OL_ItemRecipientAdd($oOl, $oForward, Default, $olTo, "jim.harper@test.com")
    _Arraydisplay($aBody)
    $oForward.Display
EndFunc   ;==>_ProcessItem

So that about does it! Thanks for all your help water, this has been incredibly helpful and I've learned tons  :sorcerer:  (I might post again if I get stuck some-where/how)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×