MACRO choisir onglet dans cellule

[a_loic]

Bonjour à tous,

Je suis à la recherche d'un information certainement simple...
J'ai beau avoir cherché je ne trouve pas ma spécificité.

J'ai une macro top qui envoie par mail un onglet, voici le début du code que j'utilise dans un autre tableau :

Sub EnvoiMail()

If MsgBox("Envoyer la demande aux personnes concernées ?", 36, "Confirmation") = vbYes Then

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
  
    Set rng = Sheets("Demande ").Range("B1:J46").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

Ma question :

La partie "Sheets("Demande ").Range("B1:J46").SpecialCells(xlCellTypeVisible)"

Le nom de l'onglet à traiter se trouve en cellule G5 de l'onget "Mail" et donc n'est pas toujours le même onglet...


De la même facon, une seconde partie :

    With OutMail
        .To = "aaa.bbb@CCC.com"
        .CC = ""
        .BCC = ""
        .Subject = "Demande" & Range("D21").Value & " (" & Range("D25").Value & ")"
        .HTMLBody = RangetoHTML(rng)
        .Send

Le mail doit être envoyées à l'adresse mail inscrite en G9 de l'onglet "Mail"


Merci d'avance pour votre aide

Excellente journée,

Loïc

 

[Yurperqod]

Bonjour le forum

Essaies avec ces ajouts et modifications
'à mettre en début de module
Dim NomFichier As String, Destinataire As String
NomFichier=Sheets("Mail").range("G5").Text
Destinataire=Sheets("Mail").range("G9").Text

'lignes existantes à modifier
Set rng = Sheets(NomFichier).Range("B1:J46").SpecialCells(xlCellTypeVisible)

With OutMail
.To = Destinataire

 

[a_loic]

Bonjour

Merci pour ta réponse

Ca fonctionne parfaitement !

J'ai cependant un autre problème, je pensais pouvoir traiter tout seul mes trois autres problèmes mais en fait je bloque aussi :s

Si tu peux toujours m'aider, voici mes trois autres demandes

1. cette ligne :

Set rng = Sheets(NomFichier).Range("B1:J46").SpecialCells(xlCellTypeVisible)

La partie Range est variable, toujours positionnée entre A et J, le nombre de lignes peut être différent.
J'ai tenté ceci mais ca ne fonctionne pas :

  Dim rng As Range, maPlage As Range
  Dim OutApp As Object
  Dim OutMail As Object
  Dim NomFichier As String, Destinataire As String
  Dim DernLigne As Long, DernColonne As Integer
'dernière ligne colonne A
DernLigne = Range("A" & Rows.Count).End(xlUp).Row
'dernière colonne ligne 1
DernColonne = Cells(1, Cells.Columns.Count).End(xlToLeft).Column

Set maPlage = Range(Cells(1, 1), Cells(DernLigne, DernColonne))


NomFichier = Sheets("Mail").Range("G5").Text
Destinataire = Sheets("Mail").Range("G9").Text


  Set rng = Nothing
  On Error Resume Next
 
  Set rng = Sheets(NomFichier).Range(maPlage).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0

2. J'ai 30 onglets qui peuvent être envoyés par mail. Je souhaite que si en cellule G5 on sélectionne "tous", la macro traite les mails pour l'ensemble des onglets (soit 30 mails possibles).

3. s'il n'y a aucune donnée dans l'onglet, un message prévient qu'il n'y a pas de mail à envoyer


Encore un grand merci,

Excellente journée,

Loic

 

[Yurperqod]

En théorie, il faudrait écrire plutôt ainsi peut-être
'mettre les deux lignes ci-dessous juste aprés les déclaration de variables
NomFichier = Sheets("Mail").Range("G5").Text
Destinataire = Sheets("Mail").Range("G9").Text

DernLigne = Sheets(NomFichier).Range("A" & Rows.Count).End(xlUp).Row
'dernière colonne ligne 1
DernColonne = Sheets(NomFichier).Cells(1, Cells.Columns.Count).End(xlToLeft).Column

Set maPlage = Sheets(NomFichier).Range(Cells(1, 1), Sheets(NomFichier).Cells(DernLigne, DernColonne))

 

[a_loic]

Ca ne fonctionne pas, j'ai un debogage sur cette ligne :

Set maPlage = Sheets(NomFichier).Range(Cells(1, 1), Sheets(NomFichier).Cells(DernLigne, DernColonne))

Voilà ce que j'ai noté :

  Dim rng As Range, maPlage As Range
  Dim OutApp As Object
  Dim OutMail As Object
  Dim NomFichier As String, Destinataire As String
  Dim DernLigne As Long, DernColonne As Integer

NomFichier = Sheets("Mail").Range("G5").Text
 Destinataire = Sheets("Mail").Range("G9").Text

 DernLigne = Sheets(NomFichier).Range("A" & Rows.Count).End(xlUp).Row
 'dernière colonne ligne 1
 DernColonne = Sheets(NomFichier).Cells(1, Cells.Columns.Count).End(xlToLeft).Column

 Set maPlage = Sheets(NomFichier).Range(Cells(1, 1), Sheets(NomFichier).Cells(DernLigne, DernColonne))


  Set rng = Nothing
  On Error Resume Next
  
  Set rng = Sheets(NomFichier).Range(maPlage).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0

  If rng Is Nothing Then
  MsgBox "Erreur de sélection" & _
  vbNewLine & "Contacter OIIR", vbOKOnly
  Exit Sub
  End If

 

[Yurperqod]

Et avec cette modification

DernColonne = Sheets(NomFichier).Cells(1, Sheets(NomFichier).Cells.Columns.Count).End(xlToLeft).Column

 

[a_loic]

Pareil, même bug

Je te joins le doc si tu veux regarder de plus près

Merci pour ton aide, vraiment !!

 

[Yurperqod]

Tes données commencent en ligne 2 donc
DernColonne = Sheets(NomFichier).Cells(2, Sheets(NomFichier).Cells.Columns.Count).End(xlToLeft).Column

 

[a_loic]

Changement fait mais toujours le même débogage au même endroit

 

[a_loic]

Je viens de réussir

il s'agit de simplement mettre
Set rng = Sheets(NomFichier).Range("A:J").SpecialCells(xlCellTypeVisible)


Il subsiste donc ces 2 soucis :

1. J'ai 30 onglets qui peuvent être envoyés par mail. Je souhaite que si en cellule G5 on sélectionne "tous", la macro traite les mails pour l'ensemble des onglets (soit 30 mails possibles).

2. s'il n'y a aucune donnée dans l'onglet, un message prévient qu'il n'y a pas de mail à envoyer

Merci encore,
Bonne journée,

Loïc

 

[Yurperqod]

Une trame que je te laisse tester sur ton PC
(sur le mien, le test est OK)
EDITION: A quoi correspondent les cellules D21 et D25 qui sont vides dans ton fichier exemple
Je les ai donc mis en commentaire pour réaliser mes tests.

Option Explicit
Public Destinataire As String
Sub Boucle_Test()
Dim Feuille As Worksheet
Select Case Sheets("Mail").Range("G5").Text
Case Is = "Tous"
Destinataire = Sheets("Mail").Range("G9").Text & ";toto@titi.fr" 'remplacer par une adresse mail OK
For Each Feuille In Worksheets
If IsNumeric(Feuille.Name) Then
envoiemail Feuille.Name
End If
Next
Case Else
Destinataire = Sheets("Mail").Range("G9").Text
envoiemail Sheets("Mail").[G5].Text
End Select
End Sub

Sub envoiemail(NomFichier As String)
Dim OutApp
Dim Outmail
Dim rng As Range
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set rng = Sheets(NomFichier).UsedRange

Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)

    On Error Resume Next
    With Outmail
        .To = Destinataire
        .CC = ""
        .BCC = ""
        .Subject = "Demande d'accès " '& Range("D21").Value & " (" & Range("D25").Value & ")"
        .HTMLBody = RangetoHTML(rng)
        .display 'Remettre ici .Send
    End With
    On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

    Set Outmail = Nothing
    Set OutApp = Nothing
    
    MsgBox "Le mail a été envoyé"
End Sub

 

[a_loic]

Re

Je viens de tester et le mail part bien.

Cependant, lorsque je lance la macro "boucle test" avec "tous" en fourn., la macro se lance, je n'ai aucun bug à priori mais je n'ai pas non plus de mails qui partent.

De plus, j'ai un message vide qui part même si l'onglet ne comporte pas de données.


Encore un grand merci pour ton aide,

Bonne journée,
Loïc

EDITION : PS : les cellules D21 et D25 correspondent bêtement à l'ancien tableau sur lequel cette macro servait déjà

 

[Yurperqod]

Lors de mon test, en G9 j'ai supprimé ta formule pour mettre mon adresse mail
et si tu examines le code tu peux voir que si il y a "tous" en G5
alors le destinataire sera : l'adresse mail en G9 et "toto@titi".fr"

Donc il faut que tu modifies ta formule SI en G9

Lors de mon test avec tous, j'avais bien plusieurs mails qui se créént
et un seul si en G5 il y un nombre correspondant à un nom de feuille existant.

 

[a_loic]

Oui mais du coup on n'a pas du se comprendre :s

Je vais être plus clair, promis

Si "tous" est sélectionné, la macro crée autant de mails que d'onglets... Donc que de choix possibles en G5.

Si mon tableau inclue 24 fourn., plutot que de faire 24 fois la macro mail, je sélectionne "tous" et la macro envoie automatiquement les 24 mails aux 24 contacts désignés dans la liste en colonne C mais en séparant bien tous les mails par fourn.

C'est mieux ? :s


Sinon, pour le mail vide, on a une solution de possible? J'ai cherché comment checker que l'onglet comporte une information en A2 de l'onglet mais je n'y arrive pas

 

[Yurperqod]

Chez moi, pas de mail vide
Remets ta formule initiale.

j'ai bien compris ce tu veux faire