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

[gauravvogue]

I got it now...thanks to you....you are the god of script...

[gauravvogue]

Thanks sir! You are the awesome scripter!

[water]

Glad the problem could be solved

[gauravvogue]

hi water... just one query... in the above program.. will it be possible if i have a column name category.

For example. I have 6 categories in it. Category A, B, C, D, E , F so... what i want to do here..... My program first filter the category A and it will copy the no of rows contained in this category simultaneously in mail body format...... the loop should go on for all categories..... irrespective of the no of rows under one category.

Can you look into it please if its possible ? I would really appreciate your help.

[water]

This should be possible by looping though the array and creating the HTML code you need.

How should the mail look like and how does the input data (array) look like?

[gauravvogue]

hello Water.

For example.... I have maintained one column in excel reflecting category type. so I want my program to filter all rows of category =A first and paste the all rows corresponding to that category and draft in a mail body.

then for category=B, it should select all rows and draft in a mail body for all the rows of category=B.

Resource Name 3rd reminder Stake-holder Reminder No # Project send date P1 Date P3 Aging Start Date End Date Project no N/w status Total time Category Joseph   3     18-Sep-14 Received -41900     4.51E+09 93744773 901 A Jon   3     18-Sep-14 Received -41900     4.51E+09 93744773 901 B David   3     18-Sep-14 Received -41900     4.51E+09 93744773 901 C Kim   3     18-Sep-14 Received -41900     4.51E+09 93744773 901 B Ria   3     18-Sep-14 Received -41900     4.51E+09 93744773 901 A ED   3     18-Sep-14 Received -41900     4.51E+09 93744773 901 A

 

Project Name Resource Name CPM Reminder No # BQ send on Aging Project start Date Project end Date Category      A Mobile Joseph Joseph 1 12/4/2014   1/23/2012 2/12/2009 A      B Mobile Joseph Joseph 1 1/4/2012   23/02/2012 2/12/2009 A

 

In our last program. we were able to achieve for single rows and till project end date.

Now the requirement is i want to add Category and if five rows are there in the excel sheet then it should paste all 5 rows in a mail body and if 3 rows are there in category=B then it should paste 3 rows in a mail body all together.

[gauravvogue]

Attaching the image of excel sheet and mail body requirement,

Resource Name   3rd reminder Stake-holder   Reminder No #   Project send date   P1  Date    P3  Aging   Start Date  End Date    Project no  N/w status  Total time  Category
Joseph      3           18-Sep-14   Received    -41900          4509424082  93744773    901 A
Jon     3           18-Sep-14   Received    -41900          4509424082  93744773    901 B
David       3           18-Sep-14   Received    -41900          4509424082  93744773    901 C
Kim     3           18-Sep-14   Received    -41900          4509424082  93744773    901 B
Ria     3           18-Sep-14   Received    -41900          4509424082  93744773    901 A
ED      3           18-Sep-14   Received    -41900          4509424082  93744773    901 A
                                                    
                                                    
Mail Output                                                 
                                                    
                                                    
Project Name    Resource Name   CPM Reminder No #   BQ send on  Aging   Project start Date  Project end Date    Category                    
     A Mobile   Joseph  Joseph  1   12/4/2014       1/23/2012   2/12/2009   A                   
     B Mobile   Joseph  Joseph  1   1/4/2012        23/02/2012  2/12/2009   A

Edited October 14, 2014 by gauravvogue

[water]

You simply need to loop through the array and select those rows needed

Pseudo code:

For $i = 0 to UBound($aArray, 1) - 1
    If $sArray[$i][Category Column Number] = "A" Then
        ; process this row
    EndIf
Next

[gauravvogue]

#include <Excel.au3>

#include <outlookEX.au3>

#include <INet.au3>

Global $sWorkbook = @ScriptDir & "\PO\Final.xls"
Global $sBody = "Hello" & "<P>" & _
    "Please find below the pending PO details till date"

Global $oExcel = _Excel_Open(False)
Global $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True)
Global $aData = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.Usedrange)
Global $aArray = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.Usedrange)
_Excel_Close($oExcel, False)

$oOutlook = _OL_Open()
For $i = 0 to UBound($aArray, 1) - 1
If $pArray[$i][Category Column Number] = "A" Then
For $i = 1 To UBound($aData, 1) - 1
    $sDate = StringLeft($aData[$i][13], 4) & "-" & StringMid($aData[$i][13], 5, 2) & "-" & StringMid($aData[$i][13], 7, 2)
    $qDate = StringLeft($aData[$i][17], 4) & "-" & StringMid($aData[$i][17], 5, 2) & "-" & StringMid($aData[$i][17], 7, 2)
    $rDate = StringLeft($aData[$i][18], 4) & "-" & StringMid($aData[$i][18], 5, 2) & "-" & StringMid($aData[$i][18], 7, 2)
    $sTemp = $sBody & "<p>" & '<html><body><style>table{width:100%;}td{border:1px solid;}</style>' & _
            '<table><tr bgcolor="#ffcc66"><td>' & $aData[0][1] & '</td><td>' & $aData[0][8] & '</td><td>' & $aData[0][9] & '</td>' & _
            '<td>' & $aData[0][11] & '</td><td>' & $aData[0][13] & '</td><td>' & $aData[0][16] & '</td><td>' & $aData[0][17] & '</td><td>' & $aData[0][18] & '</td>' & _
            '<tr><td>' & $aData[$i][1] & '</td><td>' & $aData[$i][8] & '</td><td>' & $aData[$i][9] & '</td>' & _
            '<td>' & $aData[$i][11] & '</td><td>' & $sDate & '</td><td>' & $aData[$i][16] & '</td><td>' & $qDate & '</td><td>' & $rDate & '</td>' & _
            '</tr></table></body></html>' ; set the body to the static text from $sBody plus 5 cells from Excel
     _OL_Wrapper_SendMail($oOutlook, $aData[$i][9], $aData[$i][10], "", $aData[$i][1], $sTemp,  "", $olFormatHTML)
     Next
EndIf
    Next
_OL_Close($oOutlook)
Exit

Hello Water. I have tried the above program but it is giving me an error of variables used without being declared at line no 20. can you please have  alook into it.?

[water]

This line is wrong (Second array index):

If $pArray[$i][Category Column Number] = "A" Then

[gauravvogue]

How to incorporate it into the program to make it work?

[water]

I'm on vacation for the next two weeks so I will not be able to have a look at your problem.

[sumandevadiga]

Hello Water,

I have see you had helped some of the member in very complex coding, i need your help as well

What i have done here, am pulling data from our internal system and updating in excel sheet, now same data of excel sheet i want to paste in outlook but in table format, can you help me to complete this coding.

#include <Excel.au3>
$oExcel = _ExcelBookOpen ("C:\client\body.xlsx")
_ExcelWriteCell($oExcel, "Reference", 1, 1)
_ExcelWriteCell($oExcel, $EiRef, 1, 2)
_ExcelWriteCell($oExcel, "Vessel", 2, 1)
_ExcelWriteCell($oExcel, $vsl, 2, 2)
_ExcelWriteCell($oExcel, "HAWB", 3, 1)
_ExcelWriteCell($oExcel, $Eihb, 3, 2)
;********************************************************************************************************************************************************************************
Sleep(5000)
$oOApp = ObjCreate("Outlook.Application")
$olMailItem = 0
$oOMail = $oOApp.CreateItem ($olMailItem)
$oOMail.Display
 

body.xlsx

[water]

To work with Outlook I suggest to use my OutlookEX UDF (for download please see my signature).

To create a table in a mail I suggest to use HTML and create the table. Will post an example quite soon

[water]

#include "..\OutlookEX.au3"

Global $oOutlook = _OL_Open()
Global $sReference = "Reference-Value"
Global $sVessel = "Vessel-Value"
Global $sHAWB = "HAWB-Value"

; Create table
Global $sStyle = "<style>table, td { border: 1px solid black; border-collapse: collapse}</style>"

Global $sBody = $sStyle & "<Table ><TR><TD>" & $sReference & "</td><TD>" & $sVessel & "</TD><TD>" & $sHAWB & "</TD></TR></TABLE>"

; Create the item
$oItem = _OL_ItemCreate($oOutlook, $olMailItem, "*", "", "Subject=TestMail", "BodyFormat=" & $olFormatHTML, "HTMLBody=" & $sBody)
If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error creating the mail item. @error = " & @error & ", @extended = " & @extended)
$oItem.Display

 

[sumandevadiga]

Hello Water,

Thanks for the prompt reply, however I can't download  OutlookEX, since we have to use outlook Pro + 2013 as per our company standard, can you help me with coding as per my outlook

 

[water]

The OutlookEX UDF does exactly what you need. But it adds error checking etc

$oOApp = ObjCreate("Outlook.Application")
$olMailItem = 0
$oOMail = $oOApp.CreateItem ($olMailItem)
$oOMail.Display

Would be

#include <OutlookEX.au3>
Global $oOL = _OL_Open() ; Connect to Outlook
Global $oItem = _OL_ItemCreate($oOL, ...) ; Create the mail item
$oItem.Display()

 

[sumandevadiga]

Hello Water,

 

Please find attached my outlook version details.

[water]

Office 2013 shouldn't be a problem.
As long as you can connect to Outlook by using

$oOApp = ObjCreate("Outlook.Application")

the UDF should work properly.

[sumandevadiga]

Hello Water,

carting.au3Thanks, script able to connect outlook however am getting attached error also i have attached the full script 

carting.au3