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

gauravvogue · August 13, 2014

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

gauravvogue · August 14, 2014

hello Water...

Have you able to find out any solution for this ?

water · August 14, 2014

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.

gauravvogue · August 14, 2014

thanks water for your reply.

I will wait for your response. :sweating:

Enjoy your vacations.

water · August 14, 2014

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

gauravvogue · August 15, 2014

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>

water · August 15, 2014

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

Or are they Autoit related?

gauravvogue · August 18, 2014

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?

water · August 18, 2014

~~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 August 18, 2014 by water

gauravvogue · August 19, 2014

yes water , you were right..

I changed the formatting and it works. Thank you very much ! :thumbsup:

water · August 19, 2014

gauravvogue · August 28, 2014

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

water · August 29, 2014

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.

gauravvogue · August 29, 2014

#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

water · August 29, 2014

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"

gauravvogue · August 29, 2014

_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]

water · August 31, 2014

_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"

gauravvogue · August 31, 2014

Hello water sir,

Can you please look into the solution....

water · August 31, 2014

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

water · August 31, 2014

Is this in the header row or the data lines?

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

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

water

water

water

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members