Envoie de mail depuis un Userform avec destinataires et personnes en copie selon cond

P

piwwwa

XLDnaute Junior
Bonsoir cher ami excelien,

Je reviens vers vous car encore une fois mes connaissance on atteint mes limites et malgré mes recherches je ne trouves pas de solution ;

Mon problème et le suivants, j’ai une macro (cf code ci-dessous) dans un usf qui me permet à la fois d’envoyer un mail mais aussi une invitation de calendrier à une liste de personne plus ou moins grande dont le nom et contenu dans des combobox en fonction de critère contenu dans les textbox. Jusque-là pas de problème tout marche bien.

Mon problème est que je souhaiterais dans le cas où il trouve que ma condition et juste et qu’il rajoute cette personne en destinataire qu’il rajoute en copie une personne (qui serait différente selon la personne ajouté), les nom des personnes à mettre en copie se trouve également dans textbox.
Et la par contre plus rien ne vas, tout ce que je tente ne marche pas !!!

Code: 
Sub CommandButton5_Click()
Dim OLApplication As Outlook.Application, olmail As Outlook.MailItem
Set OLApplication = CreateObject("Outlook.Application")
Set olmail = OLApplication.CreateItem(olMailItem)
Dim Desti As Outlook.Recipient
Dim OkApp As New Outlook.Application
Dim Rdv As Outlook.AppointmentItem
      
With olmail
      .Importance = olImportanceNormal
      .Subject = "Test" 'Objet
      .Body = "Bonjour,"
      .Categories = "Daily"
      .OriginatorDeliveryReportRequested = True 'Accusé de dépôt
.Display '<-- Pour vérifier le mail avant l'envoi
End With

If ComboBox37.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox8)
ElseIf ComboBox37.Value = "Rajouter" Then
ElseIf ComboBox37.Value = "Indisponible" Then
ElseIf ComboBox37.Value = "" Then
End If
If ComboBox47.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox9)
ElseIf ComboBox47.Value = "Rajouter" Then
ElseIf ComboBox47.Value = "Indisponible" Then
ElseIf ComboBox47.Value = "" Then
End If
If ComboBox46.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox10)
ElseIf ComboBox46.Value = "Rajouter" Then
ElseIf ComboBox46.Value = "Indisponible" Then
ElseIf ComboBox46.Value = "" Then
End If
If ComboBox45.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox11)
ElseIf ComboBox45.Value = "Rajouter" Then
ElseIf ComboBox45.Value = "Indisponible" Then
ElseIf ComboBox45.Value = "" Then
End If
If ComboBox44.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox12)
ElseIf ComboBox44.Value = "Rajouter" Then
ElseIf ComboBox44.Value = "Indisponible" Then
ElseIf ComboBox44.Value = "" Then
End If
If ComboBox43.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox13)
ElseIf ComboBox43.Value = "Rajouter" Then
ElseIf ComboBox43.Value = "Indisponible" Then
ElseIf ComboBox43.Value = "" Then
End If
If ComboBox42.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox14)
ElseIf ComboBox42.Value = "Rajouter" Then
ElseIf ComboBox42.Value = "Indisponible" Then
ElseIf ComboBox42.Value = "" Then
End If
If ComboBox41.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox15)
ElseIf ComboBox41.Value = "Rajouter" Then
ElseIf ComboBox41.Value = "Indisponible" Then
ElseIf ComboBox41.Value = "" Then
End If
If ComboBox40.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox16)
ElseIf ComboBox40.Value = "Rajouter" Then
ElseIf ComboBox40.Value = "Indisponible" Then
ElseIf ComboBox40.Value = "" Then
End If
If ComboBox39.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox17)
ElseIf ComboBox39.Value = "Rajouter" Then
ElseIf ComboBox39.Value = "Indisponible" Then
ElseIf ComboBox39.Value = "" Then
End If
If ComboBox38.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox18)
ElseIf ComboBox38.Value = "Rajouter" Then
ElseIf ComboBox38.Value = "Indisponible" Then
ElseIf ComboBox38.Value = "" Then
End If
If ComboBox49.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox19)
ElseIf ComboBox49.Value = "Rajouter" Then
ElseIf ComboBox49.Value = "Indisponible" Then
ElseIf ComboBox49.Value = "" Then
End If
If ComboBox48.Value = "Confirmer" Then
Set Desti = olmail.Recipients.Add(ComboBox20)
ElseIf ComboBox48.Value = "Rajouter" Then
ElseIf ComboBox48.Value = "Indisponible" Then
ElseIf ComboBox48.Value = "" Then
End If


Set Rdv = OkApp.CreateItem(olAppointmentItem)
  
 With Rdv
     .MeetingStatus = olMeeting
     .Subject = "tes envoie calendrier depuis USF"
     .Body = "...description ...."
     .Location = "test"
     .Start = #10/24/2014 9:30:00 PM#     ' Attention : format mois/jours/année
     .Duration = 30 'minutes
     .Display
 End With

If ComboBox37.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox8)
ElseIf ComboBox37.Value = "Rajouter" Then
ElseIf ComboBox37.Value = "Indisponible" Then
ElseIf ComboBox37.Value = "" Then
End If
If ComboBox47.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox9)
ElseIf ComboBox47.Value = "Rajouter" Then
ElseIf ComboBox47.Value = "Indisponible" Then
ElseIf ComboBox47.Value = "" Then
End If
If ComboBox46.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox10)
ElseIf ComboBox46.Value = "Rajouter" Then
ElseIf ComboBox46.Value = "Indisponible" Then
ElseIf ComboBox46.Value = "" Then
End If
If ComboBox45.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox11)
ElseIf ComboBox45.Value = "Rajouter" Then
ElseIf ComboBox45.Value = "Indisponible" Then
ElseIf ComboBox45.Value = "" Then
End If
If ComboBox44.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox12)
ElseIf ComboBox44.Value = "Rajouter" Then
ElseIf ComboBox44.Value = "Indisponible" Then
ElseIf ComboBox44.Value = "" Then
End If
If ComboBox43.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox13)
ElseIf ComboBox43.Value = "Rajouter" Then
ElseIf ComboBox43.Value = "Indisponible" Then
ElseIf ComboBox43.Value = "" Then
End If
If ComboBox42.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox14)
ElseIf ComboBox42.Value = "Rajouter" Then
ElseIf ComboBox42.Value = "Indisponible" Then
ElseIf ComboBox42.Value = "" Then
End If
If ComboBox41.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox15)
ElseIf ComboBox41.Value = "Rajouter" Then
ElseIf ComboBox41.Value = "Indisponible" Then
ElseIf ComboBox41.Value = "" Then
End If
If ComboBox40.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox16)
ElseIf ComboBox40.Value = "Rajouter" Then
ElseIf ComboBox40.Value = "Indisponible" Then
ElseIf ComboBox40.Value = "" Then
End If
If ComboBox39.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox17)
ElseIf ComboBox39.Value = "Rajouter" Then
ElseIf ComboBox39.Value = "Indisponible" Then
ElseIf ComboBox39.Value = "" Then
End If
If ComboBox38.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox18)
ElseIf ComboBox38.Value = "Rajouter" Then
ElseIf ComboBox38.Value = "Indisponible" Then
ElseIf ComboBox38.Value = "" Then
End If
If ComboBox49.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox19)
ElseIf ComboBox49.Value = "Rajouter" Then
ElseIf ComboBox49.Value = "Indisponible" Then
ElseIf ComboBox49.Value = "" Then
End If
If ComboBox48.Value = "Confirmer" Then
Set Desti = Rdv.Recipients.Add(ComboBox20)
ElseIf ComboBox48.Value = "Rajouter" Then
ElseIf ComboBox48.Value = "Indisponible" Then
ElseIf ComboBox48.Value = "" Then
End If

Set OkApp = Nothing


Set OLApplication = Nothing
Set olmail = Nothing

Merci d'avance pour votre aide!!!
 
Trier par date Trier par votes
P

piwwwa

XLDnaute Junior
Re : Envoie de mail depuis un Userform avec destinataires et personnes en copie selon

Je viens de m’apercevoir que j’avais encore demandé un coup de main, sans même avoir posé un extrait de fichier, voilà une bêtise de réparée.

Merci d'avance pour votre aide
 

Pièces jointes

  • Test mail.xlsm
    225.8 KB · Affichages: 44
  • Test mail.xlsm
    225.8 KB · Affichages: 46
  • Test mail.xlsm
    225.8 KB · Affichages: 53
Vote positif 0 Vote négatif
P

piwwwa

XLDnaute Junior
Re : Envoie de mail depuis un Userform avec destinataires et personnes en copie selon

Bonjour,

Un petit up de ma part car malgré mais recherche et mon avancer sur le reste du projet, je suis totalement perdu sur ce point-là, et je n’ai plus aucune idée à tester ^^ !

Merci d’avance pour votre aide.
 
Vote positif 0 Vote négatif
P

piwwwa

XLDnaute Junior
Re : Envoie de mail depuis un Userform avec destinataires et personnes en copie selon

Au cas ou la solution intéresse quelqu'un j'ai fini par la trouver :
Code: 
Private Sub CommandButton6_Click()
Dim OLApplication As Outlook.Application, olmail As Outlook.MailItem
Set OLApplication = CreateObject("Outlook.Application")
Set olmail = OLApplication.CreateItem(olMailItem)
Dim Desti As Outlook.Recipient
Dim OkApp As New Outlook.Application
Dim Rdv As Outlook.AppointmentItem

Dim objRecipient As Outlook.Recipient
Const olTo = 1
Const olCC = 2
Const olBCC = 3
With olmail
    .Subject = "Fiche Démarrage  "
    .Body = "Bonjour,"
    .OriginatorDeliveryReportRequested = True 'Accusé de dépôt
    
If ComboBox37.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox8)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox509)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox37.Value = "Confirmer" Then
ElseIf ComboBox37.Value = "Indisponible" Then
ElseIf ComboBox37.Value = "" Then
End If

If ComboBox47.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox9)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox510)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox47.Value = "Confirmer" Then
ElseIf ComboBox47.Value = "Indisponible" Then
ElseIf ComboBox47.Value = "" Then
End If

If ComboBox46.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox10)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox511)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox46.Value = "Confirmer" Then
ElseIf ComboBox46.Value = "Indisponible" Then
ElseIf ComboBox46.Value = "" Then
End If

If ComboBox45.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox11)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox512)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox45.Value = "Confirmer" Then
ElseIf ComboBox45.Value = "Indisponible" Then
ElseIf ComboBox45.Value = "" Then
End If

If ComboBox44.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox12)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox513)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox44.Value = "Confirmer" Then
ElseIf ComboBox44.Value = "Indisponible" Then
ElseIf ComboBox44.Value = "" Then
End If

If ComboBox43.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox13)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox514)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox43.Value = "Confirmer" Then
ElseIf ComboBox43.Value = "Indisponible" Then
ElseIf ComboBox43.Value = "" Then
End If

If ComboBox42.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox14)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox515)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox42.Value = "Confirmer" Then
ElseIf ComboBox42.Value = "Indisponible" Then
ElseIf ComboBox42.Value = "" Then
End If

If ComboBox41.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox15)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox516)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox41.Value = "Confirmer" Then
ElseIf ComboBox41.Value = "Indisponible" Then
ElseIf ComboBox41.Value = "" Then
End If

If ComboBox40.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox16)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox517)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox40.Value = "Confirmer" Then
ElseIf ComboBox40.Value = "Indisponible" Then
ElseIf ComboBox40.Value = "" Then
End If

If ComboBox39.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox17)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox518)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox39.Value = "Confirmer" Then
ElseIf ComboBox39.Value = "Indisponible" Then
ElseIf ComboBox39.Value = "" Then
End If

If ComboBox38.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox18)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox519)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox38.Value = "Confirmer" Then
ElseIf ComboBox38.Value = "Indisponible" Then
ElseIf ComboBox38.Value = "" Then
End If

If ComboBox49.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox19)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox520)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox49.Value = "Confirmer" Then
ElseIf ComboBox49.Value = "Indisponible" Then
ElseIf ComboBox49.Value = "" Then
End If

If ComboBox48.Value = "Rajouter" Then
Set objRecipient = olmail.Recipients.Add(ComboBox20)
objRecipient.Type = olTo
objRecipient.Resolve
Set objRecipient = olmail.Recipients.Add(TextBox521)
objRecipient.Type = olCC
objRecipient.Resolve
ElseIf ComboBox48.Value = "Confirmer" Then
ElseIf ComboBox48.Value = "Indisponible" Then
ElseIf ComboBox48.Value = "" Then
End If

.Display '<-- Pour vérifier le mail avant l'envoi
End With
 
Vote positif 0 Vote négatif

Discussions similaires

C
  • Question
XL 2013 Envoie groupé de mails contenant login et mdp uniques ... Mais pourquoi un espace en plus !?
Réponses
0
Affichages
774
Cecinerock
C
C
  • Question
envoie mail avec background dans corps de message depuis excel
Réponses
0
Affichages
1 K
ccharvet1
C
B
  • Question
Boucle pour Textbox et Combobox
2
Réponses
18
Affichages
2 K
bloomby
B

Membres actuellement en ligne

4 membres et 68 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 084
Messages
2 085 194
Membres
102 812
dernier inscrit
abdouami