Revelation343

Mailing from Excel/variable storing

8 posts in this topic

#1 ·  Posted (edited)

Purpose of script: To send emails in Outlook based on data in an excel spreadsheet. From: fields are entered for purposes of sending on behalf of (delegate), copies A2 cell from excel for To: field, Subject field is a static value and entered, returns to excel spreadsheet to read A1 and copy the first name and insert into the body of a template at an insertion point. The From, and the body of the email change based on region, so currently I have 6 different scripts that do essentially the same thing with some minor changes and want to consolidate into one script to save time.

Question:
To expedite the process of this and cut down on the amount of scripts, 6 in total I use daily, is it possible for me to somehow add the region to column C in excel, have autoit read column C values per row, and then decide which function, within a master script, to execute and loop this until there is no value in column C field?

Example spreadsheet:

Rob | rob@annuity.com |Midwest
Annie | annie@agency.com | Midwest
Kyle | kyle@agency.com | MidAtlantic
Rick | rick@megasales.com | MidAtlantic
Blank | Blank | Blank |

Example execution:
Run Birthday.au3, execute loop part through hotkey
Reads row 1, C1, value is Midwest, calls Midwest(), script runs as Midwest Birthday.au3 does currently
Reads row 2, C2, value is Midwest, calls Midwest(), script runs as Midwest Birthday.au3 does currently
Reads row 3, C3, value is MidAtlantic, calls MidAtlantic(), script runs as MidAtlantic Birthday.au3 currently
Reads row 4, C4, value is MidAtlantic, calls MidAtlantic(), script runs as MidAtlantic Birthday.au3 currently
Reads row 5, C5, value is null or blank, ends script through Exit

 

Everything I have coded in my time in AutoIt has been based mostly on mouse based movements and I don't have variable programming knowledge so I feel like I'm close to understanding how to do this, but the reading/storing variables part is beyond my current skill set. Help is appreciated. :)

Mail Merges don't work as delegated in Outlook 07, for those that might be questioning why I just don't do that.
 

MidAtlantic Birthday.au3

Midwest Birthday.au3

Edited by Revelation343

Share this post


Link to post
Share on other sites

#2 ·  Posted (edited)

I suggest to use the Excel UDF that comes with AutoIt plus the OutlookEX UDF which you can download from my signature.
First use _Excel_RangeRead to read the whole worksheet into an array, then loop through the array and call _OL_Wrapper_SendMail to create the mail and send it.

If needed I can provide an example script.

 

 

Edited by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

#include <Excel.au3>
#include "OutlookEX.au3"

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test_Mail.xlsx", True) ; Open Excel workbook read-only
Global $aData = _Excel_RangeRead($oWorkbook)
_Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel, False)

$oOL = _OL_Open()

; Retrieve defined signatures
Global $aSignatures = _OL_MailSignatureGet()
; Deactivate signature for new emails
_OL_MailSignatureSet("", Default)

; Create, Modify and send mails
For $i = 0 To UBound($aData, 1) - 1
    $oItem = _OL_ItemCreate($oOL, $olMailItem, "", @ScriptDir & "\" & $aData[$i][2] & ".msg", "Subject=Test")
    ConsoleWrite("IC: " & @error & "-" & @extended & @CRLF)
    $aBody = _OL_ItemGet($oOL, $oItem, Default, "HTMLBody")
    ConsoleWrite("IG: " & @error & "-" & @extended & @CRLF)
    $sBody = StringReplace($aBody[1][1], "%Firstname%", $aData[$i][0])
    _OL_ItemModify($oOL, $oItem, Default, "HTMLBody=" & $sBody)
    ConsoleWrite("IM: " & @error & "-" & @extended & @CRLF)
    _OL_ItemRecipientAdd($oOL, $oItem, Default, $olTo, $aData[$i][1])
    ConsoleWrite("RA: " & @error & "-" & @extended & @CRLF)
    $oItem.Display()
Next

; Set signature for new emails to previous value
For $i = 1 To $aSignatures[0][0]
    If $aSignatures[$i][1] = True Then
        _OL_MailSignatureSet($aSignatures[$i][0], Default)
        ExitLoop
    EndIf
Next

_OL_Close($oOL)

First (quick & dirty) example. Reads all records from Test_mail.xlsx with the structure as defined by you.
Depending on the value in column C (MidWest, MidAtlantic) an email is created based on template MidWest.msg or MidAtlantic.msg.
Placeholder "%Firstname%" is replaced with the recipients name.
Then the mail is being displayed (parameter SentOnBehalf has not been set yet).

What you need to do:

  • Place above script in a directory
  • Create MidWest.Msg and MidAtlantic.msg in the same directory
  • Create Test_mail.xlsx in the same directory

Example MidAtlantic.msg:

Quote

Dear %FirstName%,

Happy Birthday from your friends at x y.

Signature MidAtlantic

 

Edited by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Ok this makes sense. Is there a way to input the From field based on column C? Or I could just make it to where the From email is in Column D if need be if that would be easier.

Edit: Nevermind, I think I got it to work here, I just added the values for the From address to the D column. :) Take a look at it and let me know if there's anything obvious I'm missing but based on my testing it looks like this is exactly the functionality I need and I have a much better understanding of how this works now.

#include <Excel.au3>
#include "OutlookEX.au3"

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\" & "Birthday.xlsx", True) ; Open Excel workbook read-only
Global $aData = _Excel_RangeRead($oWorkbook)
_Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel, False)

$oOL = _OL_Open()

; Retrieve defined signatures
Global $aSignatures = _OL_MailSignatureGet()
; Deactivate signature for new emails
_OL_MailSignatureSet("", Default)

; Create, Modify and send mails
For $i = 0 To UBound($aData, 1) - 1
    $oItem = _OL_ItemCreate($oOL, $olMailItem, "", @ScriptDir & "\" & $aData[$i][2] & ".msg", "Subject=Test", "SentOnBehalfOfName="& $aData[$i][3])
    ConsoleWrite("IC: " & @error & "-" & @extended & @CRLF)
    $aBody = _OL_ItemGet($oOL, $oItem, Default, "HTMLBody")
    ConsoleWrite("IG: " & @error & "-" & @extended & @CRLF)
    $sBody = StringReplace($aBody[1][1], "%Firstname%", $aData[$i][0])
    _OL_ItemModify($oOL, $oItem, Default, "HTMLBody=" & $sBody)
    ConsoleWrite("IM: " & @error & "-" & @extended & @CRLF)
    _OL_ItemRecipientAdd($oOL, $oItem, Default, $olTo, $aData[$i][1])
    ConsoleWrite("RA: " & @error & "-" & @extended & @CRLF)
    $oItem.Display()
Next

; Set signature for new emails to previous value
For $i = 1 To $aSignatures[0][0]
    If $aSignatures[$i][1] = True Then
        _OL_MailSignatureSet($aSignatures[$i][0], Default)
        ExitLoop
    EndIf
Next

 

Edited by Revelation343

Share this post


Link to post
Share on other sites

Looks quite good :)
Before using the script in production you could remove the ConsoleWrite statements. I inserted them for debugging reasons.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Okay. Is there a maximum of new messages outlook has? So far I'm just running in batches of about 24. I don't know if it would be dependent on RAM or if Outlook has a hard cap. I feel like I've encountered a cap before when I was doing an deliverable report.

Share this post


Link to post
Share on other sites

Outlook is just the frontend to a mail server like Exchange. I've never heard of a limit for Outlook. Maybe the mail server has a limit to prevent mail floods.

Next idea would be to set the date in the Excel file. This would allow to restart the script without re-sending all mails by checking the date.
Right now the created mails get displayed. If you want to send them automatically then better error checking would be needed.

What do you plan as a next step?

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

I like the ability to review before sending. Though in my first 3 batches of 24, there were no errors. I mean, this function is to basically pat some people on the back and isn't a high influence sort of issue. I've been out of the office for a few days so I have 249 more to send, but on the daily, this functionality should be more than sufficient. I am interested in the concept of being able to check the date incase I do have a large list like this in the future though.

 

Edit: I was able to just keep hitting send and produce all 249 at once so the hard cap that I ran into before must have only been a figment of my overworked imagination. :) 

Edited by Revelation343

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

  • Similar Content

    • ShawnW
      By ShawnW
      I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct.
      This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc.
      If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted.
      I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file.
      I've included a test excel file with a single line and test script to create a csv demonstrating the problem.
      test.xlsx
      test.au3
    • water
      By water
      Extensive library to control and manipulate Microsoft Outlook.
      Theads: Development - General Help & Support - Example Scripts - Wiki
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2018-01-26)
      Function _OL_RuleConditionSet does not properly handle parameter rule condition type ($iRuleConditionType) when value $olConditionSentTo is passed. it is being processed the same way as $olConditionFrom.
      Thanks to user Moonscarlet for reporting this bug.
      Fixed with version 1.3.3.1 of the UDF.
    • Nareshm
      By Nareshm
      I try to activate my opened excel file using this code :
      #include <Excel.au3> $oExcel = _Excel_Open() $sCaption = $oExcel.Caption WinSetState($sCaption, "", @SW_MAXIMIZE) But when i edit cell in my excel file above code not working because it open new excel sheet.
    • kartune
      By kartune
      Hello, i'm trying to rename a variable to another variable name (not the value attached to the variable).  This would save me TONS of coding.
      Here is an example:
       
      <autoit>
      Local $clear1="$SATJ"  ;sets the $clear1 variable to become $SATJ
      GUICtrlSetState($clear1 & "3", $GUI_UNCHECKED)  ;i want this to come out to be $SATJ3, it should look something like - GUICtrlSetState($SATJ3, $GUI_UNCHECKED)
      </autoit>
      Is this possible?  I've been searching all the forums and help files for hours.  My javascript friend referenced me to the solution on JS, which lead me to searching through the "assign" help on autoit, but not much info on it.
      I've already tried
      <autoit>
      Assign($clear1, $SATJ)
      </autoit>
       
      any help is greatly appreciated!!! 
    • KING_NK
      By KING_NK
      #RequireAdmin
      Global $x=0
      Global $y=0
      Func Start()
      While 1
      AutoItSetOption('MouseCoordMode',0)
      AutoItSetOption('SendKeyDelay',10)
      WinActivate('XXX')
      MouseClick('Primary',852,62,1,10)
      sleep(2200)
      MouseClick('Primary',880,196,1,10)
      Send('260',0)      -----------------------> $a as 260
      MouseClick('Primary',880,196,1,0)
      sleep(2200)
      MouseClick('Primary',1019,194,1,10)
      Sleep(2200)
      Send('98',0) ------------------------->$b as 98
      sleep(1000)
      Wend
      EndFunc
       
       
      I want to use Different number in every loop in the place of $a and  $b.
      Is it possble?If it possible Kindly help me