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

Hello Water,

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

$sReference = "Reference-Value"
$sVessel = "Vessel-Value"
$sHAWB = "HAWB-Value"
; Create table
$sStyle = "<style>table, td { border: 1px solid black; border-collapse: collapse}</style>"
$sBody = $sStyle & "<Table ><TR><TD>" & $sReference & "</td><TD>" & $sVessel & "</TD><TD>" & $sHAWB & "</TD></TR></TABLE>"

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

Link to comment
Share on other sites

The Createitem method doesn't work this way. You need to use the corresponding _OL_ItemCreate function of the UDF.
Plus you need to remove a redundant bracket after Createitem.

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

I suggest to use the UDF functions where possible and only directly use the COM methods of Outlook where needed:

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

 

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

Dear Water,

I have used same coding what you have provided now, am still getting error Unkown function name

Sorry am bothering you lot, please help to fix this.

$sReference = "Reference-Value"
$sVessel = "Vessel-Value"
$sHAWB = "HAWB-Value"
; Create table
$sStyle = "<style>table, td { border: 1px solid black; border-collapse: collapse}</style>"
$sBody = $sStyle & "<Table ><TR><TD>" & $sReference & "</td><TD>" & $sVessel & "</TD><TD>" & $sHAWB & "</TD></TR></TABLE>"

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

outlook open error.PNG

Link to comment
Share on other sites

You are creating the Outlook application object twice!
The code should look like:

#include <OutlookEX.au3>

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
Global $oOutlook = _OL_Open()
Global $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "*", "", "Subject=TestMail", "BodyFormat=" & $olFormatHTML, "HTMLBody=" & $sBody)
If @error <> 0 Then Exit MsgBox(16, "Outlook", "Error creating the mail item. @error = " & @error & ", @extended = " & @extended)
$oItem.Display

 

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

The OutlookEX UDF does not come with the standard AutoIt install. You need to download (for download please see my siganture) and store it in one of two places:

  • User Include Folder. This can be set by running the SciTE Config Tool: In SciTE select Tools -> SciTE Config and set teh folder on tab "Geneeral 1"
  • The folder where your script resides

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

The UDF is just a wrapper for the Outlook COM.
But why recode what is already available in the OutlookEX UDF?

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

Sure.
But as I stated: You are re-inventing the wheel.

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

I suggest to do the following minimum steps to make my script from post #65 work:

  • Download the OutlookEX ZIP file
  • Extract files OutlookEX.au3 and OutlookEXConstants.au3 to the directory where your script resides
  • Run my script from post #65

 

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

Glad you like the UDF :D

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

9 hours ago, sumandevadiga said:

 

Hello Water,

Do you have better solution to add signature, i used send command to add signature, its working but some how am not conformable or confident on this.

#include <OutlookEX.au3>
Global $sReference = "Reference-Value"
Global $sVessel = "Vessel-Value"
Global $sHAWB = "HAWB-Value"
Global $ssubject1 = "Carting Order"
Global $sCellValue = "suman.devadiga@expeditors.com"
Global $EiRef = "22C0065413"
Global $vsl = "TG317/17IGM#85613"
Global $Eihb =  "4051769663"
; Create table
Global $sStyle = "<style>table, td { border: 1px solid black; border-collapse: collapse}</style>"
Global $sBody = $sStyle & "<Table> <TR><TD>" & $sReference & "<TD>" & $EiRef & "</TD>" &"</TD></TR>" & "<TR><TD>" & $sVessel & "<TD>" & $vsl & "</TD>" & "</TR></TD>" & "<TR><TD>" & $sHAWB & "<TD>" & $Eihb & "</TD>" & "</TD></TR></TABLE>"
; Create the item
Global $oOutlook = _OL_Open()
Global $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "*", "","", "BodyFormat=" & $olFormatHTML, "HTMLBody=" & $sBody)
If @error <> 0 Then Exit MsgBox(16, "Outlook", "Error creating the mail item. @error = " & @error & ", @extended = " & @extended)
$oItem.Display
WinWait("[CLASS:rctrl_renwnd32]")
ControlSetText("[CLASS:rctrl_renwnd32]", "", "RichEdit20WPT1",$sCellValue)
ControlSetText("[CLASS:rctrl_renwnd32]", "", "RichEdit20WPT4","Carting Order")
Send("{TAB}")
Send("{TAB}")
Send("{TAB}")
Send("{TAB}")
Send("{PGDN}")
Send("!{H}")
Send("{AS}")
Send("AS")
Send("{Enter}")

Link to comment
Share on other sites

When a new mail is created the signature is added to the empty body. Unfortunately _OL_ItemCreate using "HTMLBody=.." overwrites the signature.
So you need to use this approach:

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

 

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

_OL_ItemSend?

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