Jump to content
gauravvogue

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

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

Share this post


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

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

Share this post


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

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

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

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


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

Glad you like the UDF :D


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
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}")

Share this post


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

_OL_ItemSend?


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