The Object is not responding because the source application..

Fermé
r4944 Messages postés 122 Date d'inscription vendredi 2 octobre 2015 Statut Membre Dernière intervention 3 mars 2017 - Modifié par Chris 94 le 24/06/2016 à 21:46
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, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "EXP16" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B38").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E38").Text & "<br/>"
                 End If
                 
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "EXP17" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B39").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E39").Text & "<br/>"
                 End If
                
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "EXP18" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B40").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E40").Text & "<br/>"
                 End If
                 
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "EXP19" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B41").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E41").Text & "<br/>"
                 End If
                
                
                  If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "EXP20" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B42").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E42").Text & "<br/>"
                 End If
                 

    ' Competencies
    
                   If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "Comp1" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B93").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E93").Text & "<br/>"
                 End If
                 
                    If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "Comp2" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B94").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E94").Text & "<br/>"
                 End If
    
    
    
    
    
    
    'Abilities
    
    '-------------------------------------------------------------------------------------------------------------------------------
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "A1" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B69").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E69").Text & "<br/>"
                 End If
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "A2" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B70").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E70").Text & "<br/>"
                 End If
    'Personal Suitability
    '-------------------------------------------------------------------------------------------------------------------------------
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "PS1" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B81").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E81").Text & "<br/>"
                 End If
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "PS2" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B82").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E82").Text & "<br/>"
                 End If
    'Asset Education
    '-------------------------------------------------------------------------------------------------------------------------------
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "AEDU1" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B114").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E114").Text & "<br/>"
                 End If
    
    
    
    
    'Experience / Expérience
    '-------------------------------------------------------------------------------------------------------------------------------
    
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "AEXP1" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B119").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E119").Text & "<br/>"
                 End If
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "AEXP2" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B120").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E120").Text & "<br/>"
                 End If
                 If Sheets("SBR").Cells(i, 26 + Exp).Value = "DNM" And Sheets("SBR").Cells(4, 26 + Exp) = "AEXP3" Then
                            bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets("Process Info").Range("B121").Text & "<br/>"
                            fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets("Process Info").Range("E121").Text & "<br/>"
                 End If
            
    
    
    

    
     'Éducation
    '------------------------------------------------------------------------------------------------------------------------------
    
                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 Sub






le 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