Erreur 91:

l'erreur se trouve sur cette ligne: ==> sender = .cells(lign_selec, c - 13).Value

le code permet de générer un mail à partir d'un modèle de mail prédéfini. J'ai tout tenté mais je ne parviens pas à résoudre le pb.

Voici le code en entier:

Sub Creationmail()

Dim obj As String
Dim xMailBody As String
Dim c As Long
Dim sender As Long
Dim otlAppnewnew As Object
Dim otlNewMailnewnewnewnew As Object

c = ActiveCell.Column
lign_selec = ActiveCell.Row
If cells(1, c).Value <> "Subject of correspondance" Then
MsgBox ("Veuillez selectionner la cellule contenant le référence complète")

With otlAppnewnew
==> sender = .cells(lign_selec, c - 13).Value
End With
Else


On Error Resume Next
Set otlAppnewnew = CreateObject("Outlook.Application")

If sender = "Civil Works" Then
Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Civil works.oft")

If sender = "Mechanical Works" Then
Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Mechanical works.oft")

If sender = "Electrical Works" Then
Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Electrical works.oft")
If sender = "Engineering Multidiscipline Works" Then
Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Engineering Multidiscilpline works.oft")

End If
End If
'xMailBody = "Body content" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
obj = "AZ-OEP-COE-" & ActiveSheet.cells(lign_selec, c - 1).Value

With otlNewMailnewnewnewnew
.Bcc = vEmailsFromSpreadsheet '(set earlier on)
.Display
.Subject = obj
' .Body = xMailBody
.Display 'or use .Send

End With
End If
'otlAppnewnew.Quit
Set otlNewMailnewnewnewnew = Nothing
Set otlAppnewnew = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub

Re,

Plusieurs erreurs dans ton code. Comme je t'avais dit dans mon premier post, le point devant Cells dans

    With otlAppnewnew
        sender = .Cells(lign_selec, c - 13).Value
    End With

n'était acceptable que si la variable otlAppnewnew était on objet Worksheet et ce n'est pas le cas. Donc supprime le point.

Ensuite tu utilises With otlAppnewnew avant d'avoir défini cette variable ! Ça plante forcément. J'ai déplacé la ligne :

    Set otlAppnewnew = CreateObject("Outlook.Application")

au dessus du With...

J'ai repéré pas mal de If sans End If. Soit Le If... Then se trouvent sur la même ligne. Soit i faut utiliser des If...ElseIf...ElseIf...End If, soit un Select Case.

Tu as deux fois On Error Resume Next sans aucune gestion des erreurs!?... Ne t'étonne pas si le résultat est incongru.

Le code corrigé :

Sub Creationmail()

Dim obj As String
Dim xMailBody As String
Dim c As Long
Dim sender As Long
Dim otlAppnewnew As Object
Dim otlNewMailnewnewnewnew As Object

c = ActiveCell.Column
lign_selec = ActiveCell.Row
If Cells(1, c).Value <> "Subject of correspondance" Then
MsgBox ("Veuillez selectionner la cellule contenant le référence complète")
Set otlAppnewnew = CreateObject("Outlook.Application")
With otlAppnewnew
    sender = Cells(lign_selec, c - 13).Value
End With
Else
    On Error Resume Next
    If sender = "Civil Works" Then
        Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Civil works.oft")
        Select Case sender
            Case "Mechanical Works"
                Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Mechanical works.oft")
            Case "Electrical Works"
                Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Electrical works.oft")
            Case "Engineering Multidiscipline Works"
                Set otlNewMailnewnewnewnew = otlAppnewnew.CreateItemFromTemplate("\\atlas.edf.fr\CO\dpit-cit\200-Ligne-International.200\001-Commun.001\AZITO IV\10.0 LNTP NTP\2_LEVEL 2 Communications\4_Modèle de mails\ Communication between OEP_COE_Engineering Multidiscilpline works.oft")
        End Select
    'xMailBody = "Body content" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    obj = "AZ-OEP-COE-" & ActiveSheet.Cells(lign_selec, c - 1).Value
    With otlNewMailnewnewnewnew
        .Bcc = vEmailsFromSpreadsheet '(set earlier on)
        .Display
        .Subject = obj
        ' .Body = xMailBody
        .Display 'or use .Send
    End With
End If
'otlAppnewnew.Quit
Set otlNewMailnewnewnewnew = Nothing
Set otlAppnewnew = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub