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

[water]

You can't add multiple recipients with a single Parameter. You have either to pass an Array with multiple cells or set multiple Parameters for _OL_ItemRecipientAdd.

[sumandevadiga]

Hello Water,

All email ids are under single cell, attached is the screen shot of excel database, am not picking up from multiple cell and merging under single parameter.

[water]

At the moment the simplest way is to use Stringsplit and then pass the array to _OL_ItemRecipientAdd.

The next version of the UDF will automatically split multiple recipients in a single parameter into the correct format.

[sumandevadiga]

Hello Water,

When next version of the UDF will be available?

 

[water]

Currently there is no release date.
Easiest way for now is to use StringSplit as I suggested above.

[sumandevadiga]

ok, will try to use StringSplit and let you know

[sumandevadiga]

Hello Water,

I was trying to test result for stringsplit, however this captruing full value instead of spliting each emails ids

 

#include <MsgBoxConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
$oExcel = _ExcelBookOpen ("F:\Scripting\Autoit\Order Management\carting\master.xlsx")
Local $sCellValue3 = _ExcelReadCell($oExcel,8,3)     
Example()

Func Example()
    Local $emails = StringSplit($sCellValue3, ",")  

    For $i = 1 To $emails[0] ; Loop through the array returned by StringSplit to display the individual values.
        MsgBox($MB_SYSTEMMODAL, "", "$emails[" & $i & "] - " & $emails[$i])
    Next
EndFunc   ;==>Example

 

[water]

$aTemp = StringSplit($sCellValue, ";", 2)
_OL_ItemRecipientAdd($oOutlook, $oItem, Default, $olTo, $aTemp)

 

[sumandevadiga]

Hello Water,

hip hip hurray!

This StringSplit working now, can you please help on below.

 

6 hours ago, sumandevadiga said:

 

Hello Water,

I need your help on excel, i have request from user to automate their weekly task,

Every week on Monday user pulls data from system and dump in below path, file name will be change on weekly (ie. next week file name will be "AR 27.03.2017-BOM") and folder name will be change monthly (ie." 04 April") rest path will remain same.

F:\COMMON\Branch\BOM-Staff\Monthly AR\03 March\AR 20.03.2017-BOM.xlsx

below is the steps user want us to automate.

1) Save as above files in below path, again,path folder name will be change monthly and weekly (ie . next week folder name will change from "Week 12 change" to  "Week 13" and next month folder name change from "03 Mar" to "04 Apr"

 F:\COMMON\Branch\BOM-Staff\Deptwise AR\2017\03 Mar\Week 12

2) However file need to save as in 8 different name under Week 12 folder (eg. 1) "AE-20.03.2017", 2) "AI-20.03.2017" etc.)

3) there are few more task will discuss later once we able to automate above task.

 

 

[JLogan3o13]

@sumandevadiga in reading through this thread since you hijacked it, I see a recurring theme of you simply dropping a problem in water's lap and expecting him to fix it for you, without any effort on your end. To water's credit, he has continued to display the extreme patience that he is known for in our community. You now have yet another problem, and have included nothing regarding what you have tried on your own to resolve it.

This forum is dedicated to helping people improve their scripting ability; it is not a place where you put in a request and someone barfs up code for you (try rent-a-coder.com for that). So how about showing a little effort and at least trying to work out the issue on your own?

[sumandevadiga]

Hello Logan,

Sorry if am troubling you guys, yes, i agree, but am very new to this tool and i always try myself, only if doesn't work i ask for help.

Thanks to water, he has helped me a lot, anyways in future i will try myself 100 times, only if doesn't work, will ask for the help, also will check for the other source of help.

 

[JLogan3o13]

If you "always try myself", as you say, then you need to post what you have tried, otherwise those trying to help you are doubling their work. I have seen several responses from water now where you didn't even go so far as to put quotes around a section of html.

[sumandevadiga]

Hello Logan,

If you see today's example, i have tried myself after that i asked for help, sometimes i don't know which code will be applicable for that i ask help without trying.

Anyways you guys doing great job, thanks for all the support specially for water.

 

1 hour ago, sumandevadiga said:

 

Hello Water,

I was trying to test result for stringsplit, however this captruing full value instead of spliting each emails ids

 

#include <MsgBoxConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
$oExcel = _ExcelBookOpen ("F:\Scripting\Autoit\Order Management\carting\master.xlsx")
Local $sCellValue3 = _ExcelReadCell($oExcel,8,3)     
Example()

Func Example()
    Local $emails = StringSplit($sCellValue3, ",")  

    For $i = 1 To $emails[0] ; Loop through the array returned by StringSplit to display the individual values.
        MsgBox($MB_SYSTEMMODAL, "", "$emails[" & $i & "] - " & $emails[$i])
    Next
EndFunc   ;==>Example

 

 

[water]

I can remember when I started here on the forum I surely posted a lot of "silly" questions. A lot of people helped me to find working solutions. Some helped me by providing  code and others by showing me the right direction and kicking me up the a...

When I provide code I think it helps the user to become a better scripter. Sometimes I only provide a link to the help file and sometimes I just tell the user to RTFM.

May patience depends on my form on the day and/or how the user explains his problem and which efforts he shows.

At the moment I feel quite comfortable  

[sumandevadiga]

Water, thank you for your views

 

 

[water]

Back on your other problem.
I would first create some pseudo code and then translate it to AutoIt. To do this some questions need to be answered.

File name:

• Is it always the current date (if the user runs your script on Monday)?
• What if the user doesn't work on Monday and calls your script on Tuesday - should the filename still be the date of Monday.

Directory name:

• Is it always the current month (if the user runs your script on Monday)?
• What if the user doesn't work on Monday and calls your script on Tuesday and the month changed on Tuesday - should the directoryname still be the taken from Monday?

[sumandevadiga]

Hello Water,

Thanks for the reply,please see my comments on below your question

File name:

• Is it always the current date (if the user runs your script on Monday)? Ans- yes, File name always be current date
• What if the user doesn't work on Monday and calls your script on Tuesday - should the filename still be the date of Monday. Ans- No, file name be the date of Tuesday

Directory name:

• Is it always the current month (if the user runs your script on Monday)? Ans- yes, always current Month
• What if the user doesn't work on Monday and calls your script on Tuesday and the month changed on Tuesday - should the directoryname still be the taken from Monday? Ans- No, No, Directory name be the Month of Tuesday

 

 

[water]

#include <Date.au3>
Global $sPath = "C:\temp\"
Global $sDir, $sFileName
$sDir = $sPath & @MON & " " & _DateToMonth(@MON) & "\"
$sFileName = "AR " & @MDAY & "." & @MON & "." & @YEAR & "-BOM.XLSX"
ConsoleWrite($sDir & @CRLF)
ConsoleWrite($sFileName & @CRLF)

The rest is up to you

[sumandevadiga]

Thank you

 

[sumandevadiga]

Hello Water,

I have tried this code, which create path and copy main file and save into different department name in new path.

expandcollapse popup

#include <MsgBoxConstants.au3>
#include <File.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
Local $sFldr1,$sFld2,$FileToRename,$NewName,$sFld1,$aTemp,$var,$sFldr2,$var1,$sFldr2
    
Local $var  = InputBox("Copy From","Please Enter Path to copy file","C:\COMMON\Branch\BOM-Staff\Monthly AR\03 March\AR 20.03.2017-BOM.xlsx", "", _
1000, 150, 200, 200)
Local $sFldr1 = InputBox("Copy To","Please Enter Path to create folder","C:\COMMON\Branch\BOM-Staff\Deptwise AR\2017\03 Mar\Week 12\", "", _
1000, 150, 200, 200)

If DirGetSize($sFldr1) <> -1 Then
        MsgBox($MB_SYSTEMMODAL, "", "Directory already exists!")
EndIf
DirCreate($sFldr1)         
  ;********************************************************************************************************************************************************************************
Local $aTemp = StringSplit($var, "\", 2)
Local $AI = "AI-"
Local $OI = "OI-"
Local $AE = "AE-"
Local $OE = "OE-"
Local $OM = "OM-"
Local $TC = "TC-"
Local $WHDS = "WHDS-"
Local $sFldr2 = $sFldr1 & $aTemp[6]

For $l = 1 to 8
    Filecopy($var,$sFldr1)
    if $l = 1 Then
        Local $sFld1 = $sFldr1 & $AI &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld1
    EndIf   
    if $l = 2 Then
        Local $sFld2 = $sFldr1 & $OI &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld2
    EndIf
    if $l = 3 Then
        Local $sFld3 = $sFldr1 & $AE &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld3
    EndIf
    if $l = 4 Then
        Local $sFld4 = $sFldr1 & $OE &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld4
    EndIf
    if $l = 5 Then
        Local $sFld5 = $sFldr1 & $OM &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld5
    EndIf
    if $l = 6 Then
        Local $sFld6 = $sFldr1 & $TC &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld6
    EndIf
    if $l = 7 Then
        Local $sFld7 = $sFldr1 & $WHDS &$aTemp[6]
        Dim $FileToRename = $sFldr2,$NewName = $sFld7
    EndIf   
    FileCopy($FileToRename, $NewName)
    FileDelete($FileToRename)    
Next