Bonjour,

Je tente présentement de modifier un code VBS qui utilise du Java pour envoyer un courriel qui incluerait cette fois-ci une pièce jointe. La seule chose qu'il me manque est d'ajouter la dite pièce jointe dans le courriel à envoyer. La pièce jointe s'en va présentement vers un sftp, mais j'aimerais plutôt cette fois-ci l'envoyer par courriel. La personne concernée n'a pas nécessairement les droits d'accéder au serveur en question et il ne s'agit pas de données confidentielles.

Honnêtement, je ne connais pratiquement rien en VBS+Java, donc il m'est difficile de tester le tout autre qu'avec du VBA d'Outlook, d'Exel ou d'Access.

Voici donc mon code VBS pour l'instant. Que dois-je ajouter d'après-vous?

Merci pour votre aide!


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
'Declare objects and variables
Dim Arg, recentFile, flowchartUsername, strProvider, strProvider2, strPath, testString, concatCellInfo, flowchartName, cellCode, offerID, offerListID, offerCode
Dim sFilePathFTP, sFilePathEmail, objFSO, objTextFileFTP, objTextFileEmail
Dim myConnection, myCommand, adoRec, adoRecOfferCode, sftp, adoRecFullName, adoRecEmail, adoRecPhoneNumber, adoTargetCellInfo, adoCustomCellInfo
Dim objShellFTP, objShellEmail
Dim flowchartID, totalCount, campaignID, campaignName
Dim fullName, emailAddress, phoneNumber, optionalEmail
Dim oFTPScriptFSO, oFTPScriptShell, strProviderName, strProvider2Name, strEmailList, strBody
Dim sCmdFTP, sCmdEmail
 
'declare counter variables
Dim lenToCellCount, lenToOfferCode, creativeCodeLength, cellCountLength
 
'Declare date time variables
Dim mostRecentDate
 
'Declare booleans
Dim isMultipleEmailCamp, noCreative
 
'Declare cell variables
Dim cellID, cellName, creativeCode, cellCount
 
'initialize variables
totalCount = 0
offerCode = ""
creativeCode = ""
noCreative=False
 
'initialize constants for column spacing
lenToCellCount = 30
lenToOfferCode = 25
 
'Get the script arguments
Set Arg = WScript.Arguments
 
'Initialize the flowchart variables from the arguments passed to the script
recentFile = Replace(Arg(0),"'","")
flowchartUsername = Replace(Arg(1),"'","")
flowchartID = Replace(Arg(2),"'","")
flowchartName = Replace(Arg(3),"'","")
'get optional emails
optionalEmail = Replace(Arg(4),"'","")
'remove optional email spaces (This is necessary for blat to work correctly)
optionalEmail = Replace(optionalEmail," ","")
 
'define connections
Set myConnection = CreateObject("ADODB.Connection") 
Set myCommand = CreateObject("ADODB.Command") 
 
myConnection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=AAPMSCCMSUC1;" & _
"UID=CCMSUnicaCampaign@app.cogeco.com;PWD=gp6D9EtkG9"
 
myCommand.ActiveConnection=myConnection 
 
'Get the Full name of the user running the flowchart
myCommand.CommandText="SELECT FIRST_NAME + ' ' + LAST_NAME from platform.usm_user where NAME = '" & flowchartUsername & "'"
Set adoRecFullName = myCommand.Execute()
fullName = adoRecFullName(0)
 
'Get the email address of the user from the database
myCommand.CommandText="SELECT EMAIL from platform.usm_user where NAME = '" & flowchartUsername & "'"
Set adoRecEmail = myCommand.Execute()
emailAddress = adoRecEmail(0)
 
'Get the phone number of the user from the database
myCommand.CommandText="SELECT PHONE1 from platform.usm_user where NAME = '" & flowchartUsername & "'"
Set adoRecPhoneNumber = myCommand.Execute()
phoneNumber = adoRecPhoneNumber(0)
 
 
'Get the Campaign ID from the database and use it in the next query
myCommand.CommandText="SELECT CampaignID from platform.UA_Flowchart where FlowchartID = " & flowchartID
Set adoRec = myCommand.Execute()
campaignID = adoRec(0)
 
'Use the campaign ID to get the campaign name from the database
myCommand.CommandText="SELECT Name from platform.UA_Campaign where CampaignID = " & campaignID
Set adoRec = myCommand.Execute()
campaignName = adoRec(0)
 
 
'Check if this is a multiple-email-single-id flowchart by checking if the flowchart id is in the mutiple em history table
myCommand.CommandText="SELECT CAMPCODE from platform.EM_MULTIPLES where sessionid = " & flowchartID
Set adoRec = myCommand.Execute()
 
'verify if flowchart id was in that table
if adoRec.EOF then
	isMultipleEmailCamp = FALSE
else
	isMultipleEmailCamp = TRUE
end if
 
'Get the information needed (cell count, cell name)
myCommand.CommandText="select sum(cellcount) as cellSum, StringValue, MAX(platform.UA_TargetCells.offerListID) from platform.UA_TargetCells INNER JOIN platform.UA_OfferListMember ON platform.UA_TargetCells.OfferListID = platform.UA_OfferListMember.OfferListID" & _ 
" INNER JOIN platform.UA_CELLATTRIBUTE on platform.UA_TargetCells.cellid= platform.UA_CELLATTRIBUTE.cellid where " & _ 
	"FlowchartID = " & flowchartID & " and StringValue!='' and IsControl = 0 and platform.UA_TargetCells.OfferListID is not null group by StringValue" 
Set adoTargetCellInfo = myCommand.Execute()
 
'if there is no creative code entered just use cell information
if adoTargetCellInfo.EOF then
myCommand.CommandText="SELECT DISTINCT CellCount, CellID, platform.UA_TargetCells.offerListID from platform.UA_TargetCells INNER JOIN platform.UA_OfferListMember ON platform.UA_TargetCells.OfferListID = platform.UA_OfferListMember.OfferListID where " & _ 
	"FlowchartID = " & flowchartID & " and IsControl = 0 and platform.UA_TargetCells.OfferListID is not null" 
Set adoTargetCellInfo = myCommand.Execute()
noCreative=True
end if
 
'Initialize concatinated string 
concatCellInfo = vbCrLf & "Here is the summary of the cells and the creative codes: " & vbCrLf & vbCrLf & _
			"Creative Code                 " & "Cell Count               " & "Offer Code" & vbCrLf
 
Do While Not adoTargetCellInfo.EOF
	'we will need to loop through all the possible values and insert them into variables
	'cellID = adoTargetCellInfo(0)
	cellCount = adoTargetCellInfo(0)	
 
	if isMultipleEmailCamp = FALSE then
		'Get the custom cell value (creative)
		'myCommand.CommandText="SELECT StringValue from platform.UA_CellAttribute where CellID = " & cellID & " and AttributeID = 104"
		'Set adoCustomCellInfo = myCommand.Execute()
 
		'If nothing in creative simply output the cell count as there is only 1 cell existing and exit loop
		if adoTargetCellInfo.EOF or noCreative = true then
			creativeCode = "N/A"
			'totalCount = cellCount
			'concatCellInfo = vbCrLf & "Total Cell Count: " & cellCount
			'exit do
		Else 'Set the correct value from the database
			creativeCode = adoTargetCellInfo(1)
		End if
 
		if creativeCode <> "" then
			'do nothing
		Else
			creativeCode = "N/A"
		End if
 
		'To get the offer code several steps need to be taken as described below---------
		'set the offer list ID 
		offerListID = adoTargetCellInfo(2)
		'Get the offer ID if it exists using the offerListID
		myCommand.CommandText="SELECT offerID from platform.UA_OfferListMember where OfferListID = " & offerListID
		Set adoRec = myCommand.Execute()
 
		'Reset offer code for next segment
		offerCode = ""
		'Loop through all offerIDs and get their offer codes
		Do While not adoRec.EOF 
			'set the offer ID
			offerID = adoRec(0)
			'Finally get the offer code if it exists using the offer id
			myCommand.CommandText="SELECT OfferCode1 from platform.UA_Offer where OfferID = " & offerID
			Set adoRecOfferCode = myCommand.Execute()
 
			if adoRecOfferCode.EOF then
				'keep going until offercode is found
			Else
				'Finally Set the offer code 
				'Check if it is the first offer code or not and concatinate appropriately
				if offerCode = "" then 
					offerCode = adoRecOfferCode(0)
				else
					offerCode = offerCode & ", &OfferCode=" & adoRecOfferCode(0)
				end if
			end if
			adoRec.MoveNext
		Loop
		'End getting the offer code----------------------------------------------------------
 
		'Get the total of all cells
		totalCount = totalCount + cellCount
	else
 
		'get cellid
		myCommand.CommandTest="select distinct platform.UA_TargetCells.cellid from platform.UA_TargetCells INNER JOIN platform.UA_OfferListMember ON platform.UA_TargetCells.OfferListID = platform.UA_OfferListMember.OfferListID" & _
		" INNER JOIN platform.UA_CELLATTRIBUTE on platform.UA_TargetCells.cellid= platform.UA_CELLATTRIBUTE.cellid where StringValue='" & adoTargetCellInfo(1) & "'" & _ 
		" AND FlowchartID = " & flowchartID & " and StringValue!='' and IsControl = 0 and platform.UA_TargetCells.OfferListID is not null"
		Set adoCustomCellInfo = myCommand.Execute()
		cellID = CStr(adoCustomCellInfo(0))
 
		'Get the custom cell value (cell code)
		myCommand.CommandText="SELECT cellCode from platform.UA_TargetCells where CellID = " & cellID
		Set adoCustomCellInfo = myCommand.Execute()
		cellCode = CStr(adoCustomCellInfo(0))
 
		'Get the most recent date in the em multiple table and select based on it
		myCommand.CommandText="select top 1 EVENT_DATETIME from platform.EM_MULTIPLES where sessionid = " & flowchartID & " order by EVENT_DATETIME desc"
		Set adoCustomCellInfo = myCommand.Execute()
		mostRecentDate = CStr(adoCustomCellInfo(0))
 
		'Get the custom cell value (creative)
		myCommand.CommandText="SELECT CREATE_VERSION, count(*) from platform.EM_MULTIPLES where Cell_CD = '" & cellCode & "' and sessionid = " & _
		flowchartID & " and EVENT_DATETIME = '" & mostRecentDate & "' group by CREATE_VERSION"
		Set adoCustomCellInfo = myCommand.Execute()
 
		'test if empty then ouptut error log
		if adoCustomCellInfo.EOF then
			creativeCode = "N/A"
			'totalCount = cellCount
			'concatCellInfo = vbCrLf & "Total Cell Count: " & cellCount
			'exit do
		else
			creativeCode = CStr(adoCustomCellInfo(0))
		end if
 
		'To get the offer code several steps need to be taken as described below---------
		'set the offer list ID 
		offerListID = adoTargetCellInfo(2)
		'Get the offer ID if it exists using the offerListID
		myCommand.CommandText="SELECT offerID from platform.UA_OfferListMember where OfferListID = " & offerListID
		Set adoRec = myCommand.Execute()
 
		'Reset offer code for next segment
		offerCode = ""
		'Loop through all offersIDs and get the offer codes
		Do While not adoRec.EOF 
			'set the offer ID
			offerID = adoRec(0)
			'Finally get the offer code if it exists using the offer id
			myCommand.CommandText="SELECT OfferCode1 from platform.UA_Offer where OfferID = " & offerID
			Set adoRecOfferCode = myCommand.Execute()
 
			if adoRecOfferCode.EOF then
				'keep going until offercode is found
			Else
				'Finally Set the offer code 
				'Check if it is the first offer code or not and concatinate appropriately
				if offerCode = "" then 
					offerCode = adoRecOfferCode(0)
				else
					offerCode = offerCode & ", &OfferCode=" & adoRecOfferCode(0)
				end if
			end if
			adoRec.MoveNext
		Loop
		'End getting the offer code----------------------------------------------------------
 
		'replace count for cell
		if adoCustomCellInfo.EOF then
			testString = testString & vbCrLf & " ERROR ON QUERY SELECT CREATE_VERSION, count(*) from platform.EM_MULTIPLES where Cell_CD = '" & cellCode & "' and sessionid = " & _
				flowchartID & " group by CREATE_VERSION"
		else
			cellCount = adoCustomCellInfo(1)
		end if
 
		totalCount = totalCount + cellCount
 
	end if
 
	'This is the mechanics that allow for proper indentation. It checks the total amount of spaces between the column
	'titles (i.e. Creative Code_______x-length_______Cell Count____y-length_____) and adds the required
	'amount of spaces (lenght of strings) in between values to keep correct indentation.
 
	'get lenght of creative code 
	creativeCodeLength = Len(creativeCode)
	'convert cell count to string and get lenght as well
	cellCountLength = Len(Cstr(cellCount))
 
	'testString = testString & creativeCodeLength & " " & cellCountLength & " " & lenToCellCount & " " & _
	'	lenToOfferCode & vbcrlf
 
	'initialize first column
	concatCellInfo = concatCellInfo & vbCrLf & creativeCode
 
	'add correct amount of spaces between columns
	for k = 0 to (lenToCellCount - creativeCodeLength - 1)
		concatCellInfo = concatCellInfo & " "
	next
 
	'add second column info
	concatCellInfo = concatCellInfo & cellCount
 
	'add correct amount of spaces for second column
	for k = 0 to (lenToOfferCode - cellCountLength - 1)
		concatCellInfo = concatCellInfo & " "
	next
 
	'add third column info
	concatCellInfo = concatCellInfo & "&OfferCd=" & offerCode
 
	'get next value in array
	adoTargetCellInfo.MoveNext
Loop
 
'initialize total count row
concatCellInfo = concatCellInfo & vbCrLf & "Total Cell Count"
'add correct amount of spaces for the total (take spaces until Cell Count)
for k = 0 to (lenToCellCount - 16 - 1) '16 is the lenght of the text for "Total Cell Count"
	concatCellInfo = concatCellInfo & " "
next
'write the total cell count
concatCellInfo = concatCellInfo & totalCount
 
 
'Get the provider name from the flowchart name
strProvider=Mid(recentFile,19,3)
strProvider2=Mid(recentFile,19,3)
 
'Using the provider name Set the appropriate mail list
If strProvider2 = "Internal" then 
	strPath =  "cd ""tsm/Providers list/Internal DNCL"""
	strProvider2Name = "Cible_Recherche"
	strEmailList = ",test2.test2@cogeco.com"
ElseIf strProvider2 = "D2D" then 
	strPath =  "cd ""cim/Providers list"""
	strProvider2Name = "D2D"
	strEmailList = ",test2.test2@cogeco.com"
ElseIf strProvider2 = "INB" then 
	strPath =  "cd ""cim/ToBedeleted"""
	strProvider2Name = "Inbound"
	strEmailList = ",test2.test2@cogeco.com"
End If
 
'Tests to be sent to consultants only
'strEmailList = ",armen.kitbalian@cogeco.com"
'strEmailList = ",test.test@cogeco.com"
 
'Add to email list the optional emails only if they exist
if optionalEmail <> "" then
	strEmailList = strEmailList & "," & optionalEmail
else
	'do nothing
end if
 
'strEmailList = ",armen.kitbalian@cogeco.com"
 
'Set the subject of the email using the appropriate information
strSubject = flowchartName
 
'Set the body of the email using all the required information
strBody="The campaign file " & recentFile & " has been produced by " & fullName & _ 
" on " & WeekDayName(Weekday(Date)) & " " & MonthName(Month(Date)) & " " & Day(Date) & " " & Year(Date) & " at " & _ 
TIME & " and sent to " & strProvider2Name & " under the Internal DNCL folder!" & vbCrLf & vbCrLf & concatCellInfo
 
'Set required object for both email and ftp
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
'Set ftp shell object
Set objShellFTP = CreateObject("WSCript.shell")
sFilePathFTP="D:\app\script.txt"
 
'Set the ftp commands
Set objTextFileFTP = objFSO.CreateTextFile(sFilePathFTP)
objTextFileFTP.Write ("open sftp.cogeco.com" & vbCRLF)
objTextFileFTP.Write ("unica" & vbCRLF)
objTextFileFTP.Write ("zxab9911" & vbCRLF)
objTextFileFTP.Write (strPath & vbCRLF)
objTextFileFTP.Write ("put " & """D:\Program Files\Unica\Campaign\partitions\partition1\Outputs\" & recentFile & """" & vbCRLF)
objTextFileFTP.Write ("bye")
objTextFileFTP.Close
 
'set the ftp launch command
sCmdFTP="ftp -s:" & sFilePathFTP
 
'Run ftp command
objShellFTP.Run sCmdFTP, 0, False
 
'Set email shell object 
Set objShellEmail = CreateObject("WSCript.shell")
 
'Create Blat parameters and commands
sFilePathEmail="D:\Program Files\Unica\Campaign\partitions\partition1\Outputs\send_mailCmpFile.txt"
sCmdEmail = "D:\Tools\blat307_32.full\blat307\Full\Blat " & """" & sFilePathEmail & """" & " -to " & emailAddress & _
strEmailList & " -s " & """" & strSubject & """" & " -i " & """" & "Cogeco Campaign Notifications"
 
'Set the run command for the email
Set objTextFileEmail = objFSO.CreateTextFile(sFilePathEmail)
objTextFileEmail.Write (strBody)
 
'ran email command
objShellEmail.Run sCmdEmail, 0, False
 
'Clean all objects after use to free up memory
Set adoRecOfferCode = Nothing
Set adoTargetCellInfo = Nothing
Set	adoCustomCellInfo = Nothing
Set adoRec = Nothing
Set Arg = Nothing
Set objShellFTP = Nothing
Set objShellEmail = Nothing
Set myConnection = Nothing
Set myCommand = Nothing
Set adoRecFullName = Nothing
Set adoRecEmail = Nothing
Set adoRecPhoneNumber = Nothing
Set oFTPScriptFSO = Nothing
Set oFTPScriptShell = Nothing
Set objFSO = Nothing
Set objTextFileFTP = Nothing
Set objTextFileEmail = Nothing