Jump to content
gauravvogue

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

Recommended Posts

hmm... how about if i just activate the excel sheet and copy paste the required column using GUI.....

I am ready to do that if possible..... its like once my excel GUI  work will be executed then my outlook will send the message.....

Share this post


Link to post
Share on other sites

I'm on vacation right now and have no access to Excel at home.

Will come back to you as soon as I find some spare time.


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

Found a few minutes :)

Maybe something like this (I'm not sure I'm picking the correct columns). But it should be a good starting point.

#include <Excel.au3>
#include <OutlookEX.au3>
Global $sWorkbook = @ScriptDir & "\PO\Final.xlsx"
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.Sheets(1).Usedrange)
_Excel_Close($oExcel, False)

$oOutlook = _OL_Open()
For $i = 1 To UBound($aData, 1) - 1
    $sTemp = $sBody & "<p>" & '<html><body><style>table{width:100%;}td{border:1px solid;}</style>' & _
            '<table><tr bgcolor="#ffff00"><td>' & $aData[0][3] & '</td><td>' & $aData[0][4] & '</td>' & _
            '<td>' & $aData[0][5] & '</td><td>' & $aData[0][6] & '</td><td>' & $aData[0][7] & '</td>' & _
            '<tr><td>' & $aData[$i][3] & '</td><td>' & $aData[$i][4] & '</td>' & _
            '<td>' & $aData[$i][5] & '</td><td>' & $aData[$i][6] & '</td><td>' & $aData[$i][7] & '</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][1], $aData[$i][2], "", $aData[$i][0], $sTemp, "", $olFormatHTML)
Next
_OL_Close($oOutlook)
Exit

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

You have done a wonderful coding here. I have been very close to what i needed initially. Thank you very much for your help here. 

Can you just let me know or give the link to download some document so that i can make few minor changes here by myself about the type of function u have used here in setting the body of the message. I will work on my program and get back to you about its progress. Really appreciated ur help and time here.

$sTemp = $sBody & "<p>" & '<html><body><style>table{width:100%;}td{border:1px solid;}</style>' & _
            '<table><tr bgcolor="#ffff00"><td>' & $aData[0][3] & '</td><td>' & $aData[0][4] & '</td>' & _
            '<td>' & $aData[0][5] & '</td><td>' & $aData[0][6] & '</td><td>' & $aData[0][7] & '</td>' & _
            '<tr><td>' & $aData[$i][3] & '</td><td>' & $aData[$i][4] & '</td>' & _
            '<td>' & $aData[$i][5] & '</td><td>' & $aData[$i][6] & '</td><td>' & $aData[$i][7] & '</td>' & _
            '</tr></table></body></html>

Share this post


Link to post
Share on other sites

This lines simply create a HTML mail body. So any HTML reference should be able to answer your questions.

Or are they Autoit related?


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 solution. I just have one doubt here. Whenever I am copying the data from excel so it is converting the DATE format to numbers instead of actual format that i am saving in my excel sheet.

Sent Date July 22, 2014

 

but the data coming into the message body is as below

Sent Date 20140722000000

 

Is there any way to resolve this issue?

Share this post


Link to post
Share on other sites

Function _Excel_RangeRead by default returns the value of a cell. The value of a date is an integer stgarting January 1, 1900.

You can either

  • Enter the data as a string in Excel so _Excel_RangeRead returns a string
  • Create a function in AutoIt to convert the integer to a date (don't know if this exists)
  • Set parameter $iReturn = 3 to retrieve the date as you see it in the Excel GUI. Unfortunately this function only works on single cells.

Which one do you prefer?

Edit:

Forget what I wrote above.

You get the date without formatting. So simply use some of the String* functions and modify the format to your liking. Now you have YYYYMMDDHHMMSS.

Edited by water

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

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 sir,

I am facing issues with date format. I have tried all options of date format available in excel but it always returs ddmmyyhhmmss value in outlook mail body....is there any way to fix it?...

The only thing that worked was...if i entered the date manually for example ( August 27 2014) ....it gives the correct data....if you notice there is no comma afterwards 27 but in excel format there is a comma between day and year...thats why its not working. I dont want to enter all dates manually. Please let me know if therr is some alternative to copy date format. I will really appreciate your time

Share this post


Link to post
Share on other sites

Can you show the code you use to write to Outlook? From your last post I take that you re-format the value returned from Excel.


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)

_Excel_Close($oExcel, False)


$oOutlook = _OL_Open()
For $i = 1 To UBound($aData, 1) - 1
    $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][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][9] & '</td>' & _
            '<td>' & $aData[$i][11] & '</td><td>' & $aData[$i][13] & '</td><td>' & $aData[$i][16] & '</td><td>' & $aData[$i][17] & '</td><td>' & $aData[$i][18] & '</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
_OL_Close($oOutlook)
Exit

Hello water sir,

this code is working fine and i am able to fetch all the columns i needed to paste in my outlook body.. there is only one problem with date format..... Let me paste one sample mail date format here... i have tried all excel options( 10 atleast) but it doesnt give me the right result.

Start Date

End Date

20140512000000

20141231000000

Share this post


Link to post
Share on other sites

As I posted in #29 you need to format the returned date: "So simply use some of the String* functions and modify the format to your liking. Now you have YYYYMMDDHHMMSS"


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

_GetDateInString ... I found this UDF during searching the help and support section.

Can you let me know how should I incorporate it into the program as I will be picking the date from one single excel column..

 

Will it be like this in my program

 

_GetDateInstring[$i][13]

Share this post


Link to post
Share on other sites

_GetDateInString returns always date/time in "YYYY/MM/DD hh:mm:ss" format.

You could use something like

$sDate = StringLeft($aData[$i][13], 4) & "-" & StringMid($aData[$i][13], 5, 2) & "-" & StringMid($aData[$i][13], 7, 2)

to get "2014-05-12"


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

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
    $sDate = StringLeft($aData[$i][13], 4) & "-" & StringMid($aData[$i][13], 5, 2) & "-" & StringMid($aData[$i][13], 7, 2) ; Format date to YYYY-MM-DD
    $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][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][9] & '</td>' & _
            '<td>' & $aData[$i][11] & '</td><td>' & $sDate & '</td><td>' & $aData[$i][16] & '</td><td>' & $aData[$i][17] & '</td><td>' & $aData[$i][18] & '</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
_OL_Close($oOutlook)
Exit


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

Is this in the header row or the data lines?


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