Jump to content
gauravvogue

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

Recommended Posts

Glad the problem could be solved :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by gauravvogue

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

This line is wrong (Second array index):

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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()

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

×
×
  • Create New...