Jump to content

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


Go to solution Solved by water,

Recommended Posts

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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>
Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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?

Link to comment
Share on other sites

  • Solution

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Is this in the header row or the data lines?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...