Jump to content
gauravvogue

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

Recommended Posts

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...

#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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
water

How about this? Sends a mail to all recipients defined in an Excel file (column B) 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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

Share this post


Link to post
Share on other sites
water

Copy&Paste error. Needs to be:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.

Share this post


Link to post
Share on other sites
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]

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

Share this post


Link to post
Share on other sites
water

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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

Share this post


Link to post
Share on other sites
gauravvogue

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

Share this post


Link to post
Share on other sites
kylomas

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

post-83815-0-32529400-1407871167_thumb.j

Share this post


Link to post
Share on other sites
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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

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

×