how to copy one cell at a time from excel sheet and parse the data into outlook mail?

[gauravvogue]

Hi I want to send auto mail to multiple people by fetching the data from excel sheet.... It will copy the name to be marked in "To" and "CC" and will write common subject line and send it to the user.....

I am able to draft a mail but i am unable to parse the excel column data. Can anyone help me how to do pull/copy data from single cell and paste it to the desired destination...

expandcollapse popup

#include <Excel.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>



PO()

Func PO()
   Local $hGUI = GUICreate("Automatic Mail Sending program for RLAM", 500, 400)
   Local $REM1 = GUICtrlCreateButton("Reminder #1", 80, 100, 160, 25)
   Local $REM2 = GUICtrlCreateButton("Reminder #2", 80, 150, 160, 25)
   Local $iExit = GUICtrlCreateButton("Exit", 220, 250, 85, 25)

GUISetState(@SW_SHOW, $hGUI)
   While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE, $iexit
                ExitLoop
Case $REM1
Sleep(300)
Global $Employee = InputBox("Hey ....To how people do you want to send Reminder today ?", "Enter the no of count please", "" , "" , 500, 200, 436, 282, Default )
Switch $Employee
Case 1

Local $oAppl = _Excel_Open()
Local $sWorkbook1 = @ScriptDir & "\PO\Final.xls"
Local $oWorkbook1= _Excel_BookOpen($oAppl, $sWorkbook1, True)

Sleep(500)
WinActivate("Inbox - gaurav.vogue@gmail.com - Microsoft Outlook")
Sleep(500)
WinWaitActive("Inbox - gaurav.vogue@gmail.com - Microsoft Outlook")
Sleep(800)
MouseClick("left", 23, 79, 1)      ;-------------------to click on new mail--------------------
Sleep(600)
WinWaitActive("Untitled - Message (HTML) ")
Sleep(1000)
WinSetState("Untitled - Message (HTML) ", "", @SW_MAXIMIZE)
sleep(200)
WinActivate("Untitled - Message (HTML) ")
sleep(200)
MouseClick("left", 239, 159, 1)                               ;----------------to click on "TO"----------------------

Local $oRange = $oWorkbook1.ActiveSheet.Range("B2")
_Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "B2")

Sleep(200)
Send("^k")
Sleep(500)
MouseClick("left", 239, 186, 1)                                      ;----------------to click on "CC"----------------------
Sleep(200)

Local $oRange = $oWorkbook1.ActiveSheet.Range("C2")
_Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "C2")

Send("{ENTER}")
Sleep(200)
MouseClick("left", 239, 213, 1) ;----------------to click on "Subject"----------------------
Sleep(200)
Send("Reminder 1 Pending PO/ICRRB tracker for RLAM")
Sleep(200)
Send("{ENTER}")
Sleep(200)
MouseClick("left", 38, 262, 1)   ;------------------to click on the msg body---------------
Sleep(200)
Send("Hello Receiver")
Sleep(200)
Send("{ENTER 2}")
sleep(200)
Send("Please find below the pending PO details till date")  ;------------------to write details on the msg body------------
Sleep(200)
Send("{ENTER 3}")
Sleep(200)
Send("regards")
sleep(200)
Send("{ENTER}")
Sleep(200)
Send("Gaurav Kumar")
Sleep(200)
EndSwitch
EndSwitch
   WEnd
EndFunc

[water]

To send mails using Outlook do not automate the GUI, use my OutlookEX UDF. That's much more reliable.

I then would read all recipients into an array (using _Excel_RangeRead) and then loop through this array - that's much faster.

[gauravvogue]

hi... I dont have any problem in using outlook actions.. The problem i am facing is with excel application. can u just post a simple example CODE to copy data from single cell and paste it in Outlook new message body.... either in recipients list or message body. Please a code example

[gauravvogue]

I want to copy the data of one cell for example ( A2 )from excel sheet and then copy it into my outlook message to be in "TO_receipent block"  then read the data from second column " B 2 " and then copy it into subject line......... then i will send my message and then again copy data from two different cells and then copy it into my new outlook message........ i want to do it a multiple times

[Bert]

Have you looked at Excel commands in the help file? Look at _Excel_RangeRead

[water]

How about this? Sends a mail to all recipients defined in an Excel file (column plus a CC to all recipients defined in column C.

#include <Excel.au3>
#include <Outlook.au3>

Global $sWorkbook = @ScriptDir & "\PO\Final.xls"
Global $sSubject = "Reminder 1 Pending PO/ICRRB tracker for RLAM"
Global $sBody = "Hello Receiver" & @CRLF & @CRLF & _
    "Please find below the pending PO details till date") & @CRLF & @CRLF & @CRLF & _
    "regards" & @CRLF & _
    "Gaurav Kumar")
Global $oExcel = _Excel_Open(False)
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True)
Global $aData = _Excel_RangeRead($oBook, Default, $oWorkbook.ActiveSheet.Usedrange)
_Excel_Close($oExcel, False)
$oOutlook = _OL_Open()
For $i = 0 To UBound($aData, 1)
    _OL_Wrapper_SendMail($oOutlook, $aData[$i][1], $aData[$i][2], "", $sSubject, $sBody, "", $olFormatHTML)
Next
_OL_Close($oOutlook)
Exit

Much faster and more reliable than automating the GUI.

[gauravvogue]

#include <Excel.au3>

#include <outlookEX.au3>


Global $sWorkbook = @ScriptDir & "\PO\Final.xls"
Global $sSubject = "Reminder 1 Pending PO/ICRRB tracker for RLAM"
Global $sBody = "Hello Receiver"

Global $oExcel = _Excel_Open(False)
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True)

Global $aData = _Excel_RangeRead($oBook, Default, $oWorkbook.ActiveSheet.Usedrange)

_Excel_Close($oExcel, False)

$oOutlook = _OL_Open()
For $i = 0 To UBound($aData, 1)
    _OL_Wrapper_SendMail($oOutlook, $aData[$i][1], $aData[$i][2], "", $sSubject, $sBody, "", $olFormatHTML)
Next
_OL_Close($oOutlook)
Exit

i am getting an error in line no.... variable used without being declared in line no " Global $aData = _Excel_RangeRead($oBook, Default, $oWorkbook.ActiveSheet.Usedrange)'

I just want to clarify what i want in my auto program for example as per below excel sheet

Subject Line Persons to be included in To Persons to be included in CC Mexico_mobile Gaurav Kumar Gaurav kumar  China-mobile Gaurav Kumar Gaurav kumar

 

for example I want to fetch the B2 data in " to"  receipents list and C2 cell data in 'cc' receipents list and 'A2' data in Subject line. I want the type of program which can read the excel accordingly and sent a no of mails. I will count the data and will put a logic for 5 or 10 mails at a time. Water sir...please help me to automate this

[water]

Copy&Paste error. Needs to be:

Global $aData = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.Usedrange)

[water]

This script should do what you want:

#include <Excel.au3>
#include <Outlook.au3>

Global $sWorkbook = @ScriptDir & "\PO\Final.xls"
Global $sBody = "Hello Receiver" & @CRLF & @CRLF & _
    "Please find below the pending PO details till date") & @CRLF & @CRLF & @CRLF & _
    "regards" & @CRLF & _
    "Gaurav Kumar")
Global $oExcel = _Excel_Open(False) ; Start Excel or connect to a running instance
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True) ; Open the workbook
Global $aData = _Excel_RangeRead($oBook, Default, $oWorkbook.ActiveSheet.Usedrange) ; Read all used cells into an array
_Excel_Close($oExcel, False) ; Close Excel (and the workbook)
$oOutlook = _OL_Open() ; ; Start Outlook or connect to a running instance
For $i = 0 To UBound($aData, 1) ; process all records from Excel
    _OL_Wrapper_SendMail($oOutlook, $aData[$i][1], $aData[$i][2], "", $aData[$i][0], $sBody, "", $olFormatHTML) ; Send a mail 
Next
_OL_Close($oOutlook) ; Close Outlook
Exit

What you need to do is download the OutlookEX UDF (please check my signature) and "install" the UDF according to readme.txt.

[gauravvogue]

hi water sir. Thank you very much for your time !

In message body , I have removed extra brackets but still i am not getting any space in the message body.

Can i read 5 cells in a single row from the excel I am reading the data and paste it as a message body in every new mail  instead of general message in a body. Will it be possible?

#include <Excel.au3>

#include <outlookEX.au3>


Global $sWorkbook = @ScriptDir & "\PO\Final.xls"

Global $sBody = "Hello Receiver" & @CRLF  & _
    "Please find below the pending PO details till date" & @CRLF & @CRLF & @CRLF & _
    "regards" & @CRLF & _
    "Sanjeet"

Global $oExcel = _Excel_Open(False)
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True)

Global $aData = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.Usedrange)


_Excel_Close($oExcel, False)

$oOutlook = _OL_Open()
For $i = 1 To UBound($aData, 1)
    _OL_Wrapper_SendMail($oOutlook, $aData[$i][1], $aData[$i][2], "", $aData[$i][0], $sBody, "", $olFormatHTML)
Next
_OL_Close($oOutlook)
Exit

i am also getting an error at $olFormatHTML

Error: Array variable has incorrect no of subscripts or subscript dimension range exceeded.

[water]

Func _OL_Wrapper_SendMail($oOL, $sTo = "", $sCc = "", $sBCc = "", $sSubject = "", $sBody = "", $sAttachments = "", $iBodyFormat = $olFormatPlain, $iImportance = $olImportanceNormal)

Sorry, my bad. You send the mail as HTML so spaces and @CRLF in the body will be ignored.

Try this:

#include <Excel.au3>
#include <outlookEX.au3>
Global $sWorkbook = @ScriptDir & "\PO\Final.xls"
Global $sBody = "Hello Receiver" & "<P>" & _
    "Please find below the pending PO details till date" & "<P><P><P>" & _
    "regards" & "<P>" & _
    "Sanjeet"
Global $oExcel = _Excel_Open(False)
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True)
Global $aData = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.Usedrange)
_Excel_Close($oExcel, False)
$oOutlook = _OL_Open()
For $i = 1 To UBound($aData, 1) - 1
    _OL_Wrapper_SendMail($oOutlook, $aData[$i][1], $aData[$i][2], "", $aData[$i][0], $sBody, "", $olFormatHTML)
Next
_OL_Close($oOutlook)
Exit

If you want to read the mail body from 5 cells (lets say starting with column D then use something like this in the loop:

$sBody = $aData[$i][3] & $aData[$i][4] & $aData[$i][5] & $aData[$i][6] & $aData[$i][7]

[gauravvogue]

Hi....

1. Can we include two messasge body in one mail...the first one would be the common message

2. Can i include html format excel rows in message body as my second message body?

Please let me know if it is possible in the above code....i would be grateful

[water]

1. Can you give an example? What means "two message body in one mail"?

2. Sure. Example: "This is <b>bold</b> text"

[gauravvogue]

For example...for every auto mail...first i want to display the above message as declared in " $sBody " then i want to copy the five cells of single row from the excel which was opened in the program itself as in above code

[gauravvogue]

Those 5-6 cells have colors and i want to paste them as it is...

[kylomas]

@water - Thanks, you just solved a problem I'm having...needed "activesheet.usedrange"

[water]

This solves problem #1:

#include <Excel.au3>
#include <outlookEX.au3>
Global $sWorkbook = @ScriptDir & "\PO\Final.xls"
Global $sBody = "Hello Receiver" & "<P>" & _
    "Please find below the pending PO details till date" & "<P><P><P>" & _
    "regards" & "<P>" & _
    "Sanjeet"
Global $oExcel = _Excel_Open(False)
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True)
Global $aData = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.Usedrange)
_Excel_Close($oExcel, False)
$oOutlook = _OL_Open()
For $i = 1 To UBound($aData, 1) - 1
    $sTemp = $sBody & "<p>" & $aData[$i][3] & $aData[$i][4] & $aData[$i][5] & $aData[$i][6] & $aData[$i][7] ; set the body to the static text from $sBody plus 5 cells from Excel
    _OL_Wrapper_SendMail($oOutlook, $aData[$i][1], $aData[$i][2], "", $aData[$i][0], $sTemp, "", $olFormatHTML)
Next
_OL_Close($oOutlook)
Exit

Edited August 12, 2014 by water

[water]

Those 5-6 cells have colors and i want to paste them as it is...

Can you post a screenshot? I don't get what you mean by colors. The background color, color as text e.g. "red" etc.?

[gauravvogue]

I am having problem with my laptop...cant attach the attachment now. Will try to upload in the morning. Please bear with me here. Its 1 am in my country..cant get my lapi checked now... I have clicked a picture to explain...my point is in my first mail i want to attach column header shown in yellow .....which will be common for all mails and cell column A2 B2 C2 D2 E 2 in first mail and A3B3 C3 D3 and so on in second mail and so on. I want to paste them in the snapshot shared from excel

[water]

I see.

Now it starts to get a bit more complex

What you want is pure HTML. I will have a look as soon as I'm in my office again (might take a few days as I'm on vacation).