0

I've got an Excel Range including mailto email addresses for clients and a path to reach an invoice to enclosed in an email.

My code consists in sending an email (with an invoice) to each mailto address (from my gmail account).

Even if I don't include the attachment, I get an Automation error. Why?

Sub SendMail()

Dim oCdo As Object
Dim oConf As Object
Dim Flds As Object
Dim strHtml As String  'variable contenu du corps de message
Dim destinataire As String
Dim attachment As String
Dim DerLig

' Définit le contenu du message au format HTML
strHtml = "<HTML><HEAD><BODY>"
strHtml = strHtml & "<center><b> Ceci est un message de test au format <i><Font Color=#ff0000 > HTML. </Font></i></b></center>"
strHtml = strHtml & "</br>Veuillez prendre connaissance de la piece jointe."
strHtml = strHtml & "</BODY></HEAD></HTML>"

DerLig = Range("A" & Rows.Count).End(xlUp).Row
For n = 1 To DerLig
n = n + 1
destinataire = Cells(n, 3).Value
attachement = Cells(n, 8).Value

Set oCdo = CreateObject("cdo.Message")
'Set oConf = CreateObject("cdo.configuration")
'Set Flds = oConf.Fields

With oCdo.configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 'adresse du serveur smtp
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'tester 25, 465 ou 587
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True  'Utilise une connection SSL (True or False)
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 40
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 '0 : pas d'authentification, 1 : authentification basique
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MY GMAIL" 'identifiant de messagerie
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "MY PASSWORD" 'mot de passe de messagerie
    .Update
End With

With oCdo
    .Subject = "Votre facture"                                            ' objet du message
    .From = "MY GMAIL"                                                   ' adresse de l'expéditeur
    .To = destinataire                                                    ' adresse du destinataire
    .BCC = ""                                                             ' en copie cachée pour conserver trace
    .HtmlBody = strHtml                                                   ' corps du message HTML
    '.AddAttachment (attachement)                                         ' ajout de pièce jointe
    .MDNrequested = True
    .Send

End With

Set oCdo = Nothing

Next n

End Sub
Community
  • 1
  • 1
user2531004
  • 1
  • 1
  • 1

1 Answers1

0

It's due to the line : .MDNrequested = True

If it is set to false or missing, it works.

I have the same problem. Excel restarts after the first .send when setting MDN to "True". The e-mail is still sent and asks for send a "reception feedback".

BR

user2661656
  • 1