The Object is not responding because the source application
r
Salut ,
je suis entrain d'essayer une application VBA qui s'en charge d'envoyer des courriels d'une maniere automatique a partir d'un user form , où il se trouyver un bouton parcourir pour choisir la template de la lettre . sauf que ca fait 2 journees que ce message d'erreur ne s'arrete pas de sortir
error message (The Object is not responding because the source application may be busy ) ou (Excel is Waiting for another Application to complete OLE Action.....). quand je fais OK j'aurais cette fenêtre :Automation error ,call was rejected by callee
voici un petit aperçu de mon code
Sub Emails_Screening_Manual_PV()
'Déclaration des variable
'-----------------------------------------------------------------------------------------------------------------------------------------
Dim wdDoc As Object
Dim wdFileName As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim WApp As Object
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim VAR_Message As String
Dim VAR_Message_Eng As String
Dim VAR_Message_FR As String
Dim i As Integer
Dim Nbre_Line As Integer
Dim VAR_TO As String
Dim VAR_CC As String
Dim VAR_BCC As String
Dim VAR_Subject As String
Dim VAR_Beginning As String
Dim VAR_End As String
Dim ABody As String
Dim qualification_EN As String
Dim qualification_FR As String
Dim Exp As Byte
Dim bodymessage(0 To 19) As String
Dim fr(1 To 20) As String
Dim signature As String
Dim significant_FR As String
Dim Significant_EN As String
Dim Recent_EN As String
Dim Recent_FR As String
Dim qualification_cma As String
Dim qualification_cmf As String
Dim cma(1 To 20) As String
Dim cmf(1 To 20) As String
'
'-----------------------------------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
wdFileName = UserForm1.txtExcelDatasheet.Value
Set wb = ThisWorkbook
Set ws = wb.Sheets("Process Info")
Set WApp = CreateObject("Word.Application")
If wdFileName = False Then Exit Sub
Set wdDoc = WApp.Documents.Open(wdFileName)
With wdDoc
'-----------------------------------------------------------------------------------------------------------------------------------------
Nbre_Line = Sheets("SBR").Range("A456541").End(xlUp).Row
'Boucle pour tous les lignes du feuille numéro 2
'-----------------------------------------------------------------------------------------------------------------------------------------
For i = 8 To Nbre_Line
'Initialisation des variables
'
'-----------------------------------------------------------------------------------------------------------------------------------------
qualification_EN = ""
qualification_FR = ""
bodymessage(0) = ""
bodymessage(1) = ""
bodymessage(2) = ""
bodymessage(3) = ""
bodymessage(4) = ""
bodymessage(5) = ""
bodymessage(6) = ""
bodymessage(7) = ""
bodymessage(8) = ""
bodymessage(9) = ""
bodymessage(10) = ""
bodymessage(11) = ""
bodymessage(12) = ""
bodymessage(13) = ""
bodymessage(14) = ""
bodymessage(15) = ""
bodymessage(16) = ""
bodymessage(17) = ""
bodymessage(18) = ""
bodymessage(19) = ""
fr(1) = ""
fr(2) = ""
fr(3) = ""
fr(4) = ""
fr(5) = ""
fr(6) = ""
fr(7) = ""
fr(8) = ""
fr(9) = ""
fr(10) = ""
fr(11) = ""
fr(12) = ""
fr(13) = ""
fr(14) = ""
fr(15) = ""
fr(16) = ""
fr(17) = ""
fr(18) = ""
fr(19) = ""
fr(20) = ""
cma(1) = ""
cma(2) = ""
cma(3) = ""
cma(4) = ""
cma(5) = ""
cma(6) = ""
cma(7) = ""
cma(8) = ""
cma(9) = ""
cma(10) = ""
cmf(1) = ""
cmf(2) = ""
cmf(3) = ""
cmf(4) = ""
cmf(5) = ""
cmf(6) = ""
cmf(7) = ""
cmf(8) = ""
cmf(9) = ""
cmf(10) = ""
Significant_EN = ""
significant_FR = ""
Recent_EN = ""
Recent_FR = ""
Dim Flag_1etoile As Boolean
Dim Flag_2etoiles As Boolean
'Condition pour la qualification
'-----------------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 67) = "OUT" And Sheets("SBR").Cells(i, 68) = "OUT" And Sheets("SBR").Cells(i, 69) <> "OUT" And Sheets("SBR").Cells(i, 70) <> "OUT" And Sheets("SBR").Cells(i, 1) <> "" And Sheets("SBR").Cells(i, 3).Text Like "?*@?*.?*" Then
For Exp = 1 To 20
Select Case Exp
Case 1 To 20
'Condition pour les critéres du mérite
'-----------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EDU1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B19").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E19").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EDU2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B20").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E20").Text & "<br/>"
End If
'Éxpériences
'------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B23").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E23").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B24").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E24").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP3" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B25").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E25").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP4" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B26").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E26").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP5" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B27").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E27").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP6" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B28").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E26").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP7" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B29").Text & "<br/>"
cmf(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E29").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP8" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B30").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E30").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP9" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B31").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E31").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP10" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B32").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E32").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP11" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B33").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E33").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP12" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B34").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E34").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP13" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B35").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E35").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP14" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B36").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E36").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP15" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B37").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E37").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP16" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B38").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E38").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP17" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B39").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E39").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP18" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B40").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E40").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP19" Then
cma(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B41").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E41").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP20" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B42").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E42").Text & "<br/>"
End If
' Competencies
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "Comp1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B93").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E93").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "Comp2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B94").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E94").Text & "<br/>"
End If
'Abilities
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "A1" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B69").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E69").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "A2" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B70").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E70").Text & "<br/>"
End If
'Personal Suitability
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "PS1" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B81").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E81").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "PS2" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B82").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E82").Text & "<br/>"
End If
'Asset Education
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEDU1" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B114").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E114").Text & "<br/>"
End If
'Experience / Expérience
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEXP1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B119").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E119").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEXP2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B120").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E120").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEXP3" Then
cmf(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B121").Text & "<br/>"
cma(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E121").Text & "<br/>"
End If
End If
End Select
Next Exp
'Alimentation des variables qualification_EN & qualification_FR
'-----------------------------------------------------------------------------------------------------------------------------------------
qualification_EN = "<br/>" & "*** Stream 1 - Screening *** " & "<br/>" & bodymessage(0) & bodymessage(1) & bodymessage(2) & bodymessage(3) & bodymessage(4) & bodymessage(5) & bodymessage(6) & bodymessage(7) & bodymessage(8) & bodymessage(9) & "<br/>" & "*** Stream 2 - Screening *** " & "<br/>" & bodymessage(10) & bodymessage(11) & bodymessage(12) & bodymessage(13) & bodymessage(14) & bodymessage(15) & bodymessage(16) & bodymessage(17) & bodymessage(18) & bodymessage(19)
qualification_FR = "<br/>" & "*** Volet 1 - Présélection ***" & "<br/>" & fr(1) & fr(2) & fr(3) & fr(4) & fr(5) & fr(6) & fr(7) & fr(8) & fr(9) & fr(10) & "<br/>" & "*** Volet 2 - Présélection ***" & "<br/>" & fr(11) & fr(12) & fr(13) & fr(14) & fr(15) & fr(16) & fr(17) & fr(18) & fr(19) & fr(20)
qualification_cma = "<br/>" & "*** Common streams - Screening ***" & "<br/>" & cma(1) & cma(2) & cma(3) & cma(4) & cma(5) & cma(6) & cma(7) & cma(8) & cma(9) & cma(10)
qualification_cmf = "<br/>" & "*** Volets communs - Présélection ***" & "<br/>" & cmf(1) & cmf(2) & cmf(3) & cmf(4) & cmf(5) & cmf(6) & cmf(7) & cmf(8) & cmf(9) & cmf(10)
'Alimentation du variable VAR_Message_Eng (la partie anglais du message du corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
VAR_Message_Eng = "<b>" & wdDoc.Tables(1).Cell(1, 0).Range & "</b>" & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(2, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 2) & ", " & Sheets("Process Info").Cells(8, 2) & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(3, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(4, 0).Range & "</b>" & Sheets("Process Info").Cells(6, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(5, 0).Range & "</b>" & Sheets("Process Info").Cells(7, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(6, 0).Range & "</b>" & Sheets("Process Info").Cells(8, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(7, 0).Range & "</b>" & Sheets("Process Info").Cells(9, 2) & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(8, 0).Range & "<br/>" _
& wdDoc.Tables(1).Cell(9, 0).Range & "<br/>" & "<br/>" _
& "<b>" & qualification_EN & qualification_cma & Significant_EN & Recent_EN & "</b>" & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(11, 0).Range & Sheets("Process Info").Cells(10, 2) & "<br/>" _
& wdDoc.Tables(1).Cell(12, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(13, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(14, 0).Range & Sheets("Process Info").Cells(11, 2) & "<br/>" _
& wdDoc.Tables(1).Cell(15, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(16, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(17, 0).Range & "<br/>" & "<br/>"
'Alimentation du variable VAR_Message_FR (la partie française du message du corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
VAR_Message_FR = "<b>" & wdDoc.Tables(1).Cell(18, 0).Range & "</b>" & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(19, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 5) & ", " & Sheets("Process Info").Cells(8, 5) & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(20, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(21, 0).Range & "</b>" & Sheets("Process Info").Cells(6, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(22, 0).Range & "</b>" & Sheets("Process Info").Cells(7, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(26, 0).Range & "</b>" & Sheets("Process Info").Cells(8, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(24, 0).Range & "</b>" & Sheets("Process Info").Cells(9, 5) & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(25, 0).Range & "<br/>" _
& wdDoc.Tables(1).Cell(26, 0).Range & "<br/>" & "<br/>" _
& "<b>" & qualification_FR & qualification_cmf & significant_FR & Recent_FR & "</b>" & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(28, 0).Range & Sheets("Process Info").Cells(10, 5) & "<br/>" _
& wdDoc.Tables(1).Cell(29, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(30, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(31, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(32, 0).Range & Sheets("Process Info").Cells(11, 5) & "<br/>" _
& wdDoc.Tables(1).Cell(33, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(34, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(35, 0).Range & "<br/>" & "<br/>"
'Alimentation du variable VAR_Message (pour le corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
VAR_Message = VAR_Message_Eng & VAR_Message_FR
'Alimentation du variable VAR_Message (pour le corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.display
End With
signature = OutMail.HTMLBody
'Setting of Email
VAR_TO = Sheets("SBR").Cells(i, 3)
VAR_CC = Sheets("Process Info").Cells(13, 2)
VAR_BCC = Sheets("Process Info").Cells(14, 2)
VAR_Subject = Sheets("SBR").Range("Q1") & ", " & Sheets("Process Info").Cells(4, 2) & ", " & Sheets("Process Info").Cells(8, 2)
'VAR_Beginning = ""
'VAR_End = ""
ABody = VAR_Message
With OutMail
.To = VAR_TO
.CC = VAR_CC
.BCC = VAR_BCC
.Subject = VAR_Subject
.HTMLBody = ABody & signature
.display
.Save
'.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End If
Next i
'Close the Word document with saving
wdDoc.Close False
Set wdDoc = Nothing
'wdFileName = Dir
End With
Application.ScreenUpdating = True
End Suble probleme que si ca sort je dois créer un autre fichier word aussi c'est chiant. j'ai essayé le ficher sur une autre machine , ça fonctionne super bien
toutes vos suggestions seront les bienvenues
p
Bonjour,
Je ne serai d'aucune aide mais si tu réfléchis un peu, comment veux-tu que les gens te disent où est l'erreur sans voir le fichier, le tester et dire où ça plante , simplement en lisant des dizaines de lignes de code ?
Quand il est conseillé d'envoyer un fichier, c'est pas pour rien
P.
Invité
Bonjour r4944
En vérifiant ton code, il y a apparemment des début et fin de boucles anormales
Essaye avec ce code corrigé
Sub Emails_Screening_Manual_PV()
'Déclaration des variable
'-----------------------------------------------------------------------------------------------------------------------------------------
Dim wdDoc As Object
Dim wdFileName As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim WApp As Object
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim VAR_Message As String
Dim VAR_Message_Eng As String
Dim VAR_Message_FR As String
Dim i As Integer, J As Integer
Dim Nbre_Line As Integer
Dim VAR_TO As String
Dim VAR_CC As String
Dim VAR_BCC As String
Dim VAR_Subject As String
Dim VAR_Beginning As String
Dim VAR_End As String
Dim ABody As String
Dim qualification_EN As String
Dim qualification_FR As String
Dim Exp As Byte
Dim bodymessage(0 To 19) As String
Dim fr(1 To 20) As String
Dim signature As String
Dim significant_FR As String
Dim Significant_EN As String
Dim Recent_EN As String
Dim Recent_FR As String
Dim qualification_cma As String
Dim qualification_cmf As String
Dim cma(1 To 20) As String
Dim cmf(1 To 20) As String
'-----------------------------------------------------------------------------------------------------------------------------------------
' Application.ScreenUpdating = False ' NE PAS METTRE pour VERIFICATION
wdFileName = UserForm1.txtExcelDatasheet.Value
Set wb = ThisWorkbook
Set ws = wb.Sheets("Process Info")
Set WApp = CreateObject("Word.Application")
If wdFileName = False Then Exit Sub
Set wdDoc = WApp.Documents.Open(wdFileName)
'-----------------------------------------------------------------------------------------------------------------------------------------
Nbre_Line = Sheets("SBR").Range("A456541").End(xlUp).Row
'Boucle pour tous les lignes du feuille numéro 2
'-----------------------------------------------------------------------------------------------------------------------------------------
For i = 8 To Nbre_Line
'Initialisation des variables
'-----------------------------------------------------------------------------------------------------------------------------------------
qualification_EN = ""
qualification_FR = ""
For J = 0 To 19: bodymessage(J) = "": Next J
For J = 1 To 20: fr(J) = "": Next J
For J = 1 To 10: cma(J) = "": Next J
For J = 1 To 10: cmf(J) = "": Next J
Significant_EN = ""
significant_FR = ""
Recent_EN = ""
Recent_FR = ""
Dim Flag_1etoile As Boolean
Dim Flag_2etoiles As Boolean
'Condition pour la qualification
'-----------------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 67) = "OUT" And Sheets("SBR").Cells(i, 68) = "OUT" And Sheets("SBR").Cells(i, 69) <> "OUT" And Sheets("SBR").Cells(i, 70) <> "OUT" And Sheets("SBR").Cells(i, 1) <> "" And Sheets("SBR").Cells(i, 3).Text Like "?*@?*.?*" Then
For Exp = 1 To 20
Select Case Exp
Case 1 To 20
'Condition pour les critéres du mérite
'-----------------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EDU1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B19").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E19").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EDU2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B20").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E20").Text & "<br/>"
End If
'Éxpériences
'------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B23").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E23").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B24").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E24").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP3" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B25").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E25").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP4" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B26").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E26").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP5" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B27").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E27").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP6" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B28").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E26").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP7" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B29").Text & "<br/>"
cmf(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E29").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP8" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B30").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E30").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP9" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B31").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E31").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP10" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B32").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E32").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP11" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B33").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E33").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP12" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B34").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E34").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP13" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B35").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E35").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP14" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B36").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E36").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP15" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B37").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E37").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP16" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B38").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E38").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP17" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B39").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E39").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP18" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B40").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E40").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP19" Then
cma(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B41").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E41").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "EXP20" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B42").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E42").Text & "<br/>"
End If
' Competencies
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "Comp1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B93").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E93").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "Comp2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B94").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E94").Text & "<br/>"
End If
'Abilities
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "A1" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B69").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E69").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "A2" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B70").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E70").Text & "<br/>"
End If
'Personal Suitability
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "PS1" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B81").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E81").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "PS2" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B82").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E82").Text & "<br/>"
End If
'Asset Education
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEDU1" Then
cma(Exp) = cma(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B114").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E114").Text & "<br/>"
End If
'Experience / Expérience
'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEXP1" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B119").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E119").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEXP2" Then
cma(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B120").Text & "<br/>"
cmf(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E120").Text & "<br/>"
End If
If Sheets("SBR").Cells(i, 16 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 16 + Exp) = "AEXP3" Then
cmf(Exp) = cma(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("B121").Text & "<br/>"
cma(Exp) = cmf(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E121").Text & "<br/>"
End If
End Select
Next Exp
End If
'Alimentation des variables qualification_EN & qualification_FR
'-----------------------------------------------------------------------------------------------------------------------------------------
qualification_EN = "<br/>" & "*** Stream 1 - Screening *** " & "<br/>" & bodymessage(0) & bodymessage(1) & bodymessage(2) & bodymessage(3) & bodymessage(4) & bodymessage(5) & bodymessage(6) & bodymessage(7) & bodymessage(8) & bodymessage(9) & "<br/>" & "*** Stream 2 - Screening *** " & "<br/>" & bodymessage(10) & bodymessage(11) & bodymessage(12) & bodymessage(13) & bodymessage(14) & bodymessage(15) & bodymessage(16) & bodymessage(17) & bodymessage(18) & bodymessage(19)
qualification_FR = "<br/>" & "*** Volet 1 - Présélection ***" & "<br/>" & fr(1) & fr(2) & fr(3) & fr(4) & fr(5) & fr(6) & fr(7) & fr(8) & fr(9) & fr(10) & "<br/>" & "*** Volet 2 - Présélection ***" & "<br/>" & fr(11) & fr(12) & fr(13) & fr(14) & fr(15) & fr(16) & fr(17) & fr(18) & fr(19) & fr(20)
qualification_cma = "<br/>" & "*** Common streams - Screening ***" & "<br/>" & cma(1) & cma(2) & cma(3) & cma(4) & cma(5) & cma(6) & cma(7) & cma(8) & cma(9) & cma(10)
qualification_cmf = "<br/>" & "*** Volets communs - Présélection ***" & "<br/>" & cmf(1) & cmf(2) & cmf(3) & cmf(4) & cmf(5) & cmf(6) & cmf(7) & cmf(8) & cmf(9) & cmf(10)
'Alimentation du variable VAR_Message_Eng (la partie anglais du message du corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
VAR_Message_Eng = "<b>" & wdDoc.Tables(1).Cell(1, 0).Range & "</b>" & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(2, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 2) & ", " & Sheets("Process Info").Cells(8, 2) & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(3, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(4, 0).Range & "</b>" & Sheets("Process Info").Cells(6, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(5, 0).Range & "</b>" & Sheets("Process Info").Cells(7, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(6, 0).Range & "</b>" & Sheets("Process Info").Cells(8, 2) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(7, 0).Range & "</b>" & Sheets("Process Info").Cells(9, 2) & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(8, 0).Range & "<br/>" _
& wdDoc.Tables(1).Cell(9, 0).Range & "<br/>" & "<br/>" _
& "<b>" & qualification_EN & qualification_cma & Significant_EN & Recent_EN & "</b>" & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(11, 0).Range & Sheets("Process Info").Cells(10, 2) & "<br/>" _
& wdDoc.Tables(1).Cell(12, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(13, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(14, 0).Range & Sheets("Process Info").Cells(11, 2) & "<br/>" _
& wdDoc.Tables(1).Cell(15, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(16, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(17, 0).Range & "<br/>" & "<br/>"
'Alimentation du variable VAR_Message_FR (la partie française du message du corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
VAR_Message_FR = "<b>" & wdDoc.Tables(1).Cell(18, 0).Range & "</b>" & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(19, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 5) & ", " & Sheets("Process Info").Cells(8, 5) & "<br/>" & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(20, 0).Range & "</b>" & Sheets("Process Info").Cells(4, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(21, 0).Range & "</b>" & Sheets("Process Info").Cells(6, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(22, 0).Range & "</b>" & Sheets("Process Info").Cells(7, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(26, 0).Range & "</b>" & Sheets("Process Info").Cells(8, 5) & "<br/>" _
& "<b>" & wdDoc.Tables(1).Cell(24, 0).Range & "</b>" & Sheets("Process Info").Cells(9, 5) & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(25, 0).Range & "<br/>" _
& wdDoc.Tables(1).Cell(26, 0).Range & "<br/>" & "<br/>" _
& "<b>" & qualification_FR & qualification_cmf & significant_FR & Recent_FR & "</b>" & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(28, 0).Range & Sheets("Process Info").Cells(10, 5) & "<br/>" _
& wdDoc.Tables(1).Cell(29, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(30, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(31, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(32, 0).Range & Sheets("Process Info").Cells(11, 5) & "<br/>" _
& wdDoc.Tables(1).Cell(33, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(34, 0).Range & "<br/>" & "<br/>" _
& wdDoc.Tables(1).Cell(35, 0).Range & "<br/>" & "<br/>"
'Alimentation du variable VAR_Message (pour le corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
VAR_Message = VAR_Message_Eng & VAR_Message_FR
'Alimentation du variable VAR_Message (pour le corps d'email)
'-----------------------------------------------------------------------------------------------------------------------------------------
Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.display
End With
signature = OutMail.HTMLBody
'Setting of Email
VAR_TO = Sheets("SBR").Cells(i, 3)
VAR_CC = Sheets("Process Info").Cells(13, 2)
VAR_BCC = Sheets("Process Info").Cells(14, 2)
VAR_Subject = Sheets("SBR").Range("Q1") & ", " & Sheets("Process Info").Cells(4, 2) & ", " & Sheets("Process Info").Cells(8, 2)
'VAR_Beginning = ""
'VAR_End = ""
ABody = VAR_Message
With OutMail
.To = VAR_TO
.CC = VAR_CC
.BCC = VAR_BCC
.Subject = VAR_Subject
.HTMLBody = ABody & signature
.display
.Save
'.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
'End If
Next i
'Close the Word document with saving
wdDoc.Close False
Set wdDoc = Nothing
'wdFileName = Dir
Application.ScreenUpdating = True
End SubA+
r
Merci Bruno pour ton initiative , je viens de l'essayer il m'affiche une erreur ici '' wdDoc.Close False'' y'a til une façon de forcer la fermeture d'un document word ?
Invité
Bonjour,
r4944 a écrit :Merci Bruno pour ton initiative , je viens de l'essayer il m'affiche une erreur ici '' wdDoc.Close False'' y'a til une façon de forcer la fermeture d'un document word ?
Sans fichier, difficile de tester
A+