Macro per associare allegati e indirizzi mail

[kiuba]

Buongiorno a tutti, vi presento il mio problema.

Ricevo ogni giorno dei file pdf nominati: NOME COGNOME - XX - XXXXPDF. pdf, dove al posto delle X ci sono dei numeri variabili.

Possiedo un file excel nel quale in colonna N ci sono i nomi e in colonna O i cognomi corrispondenti a quelli dei file. In realtà potrebbero esserci più nomi nel file excel che nei file pdf perchè alcuni file mi arrivano con un giorno di ritardo.
Nel file excel in colonna T ho gli indirizzi email corrispondenti.

Voglio creare una macro che associ ad ogni file .pdf il corrispondente indirizzo email, in modo da inviare una mail automatizzata per ogni persona completata con il giusto allegato.

Sono in grado di inviare le mail con il giusto allegato tramite macro se il nome del file è noto, purtroppo in questo caso il nome del file è solo parzialmente noto, la parte numerica mi crea un grosso problema.

Vorrei perciò chiedervi come procedereste in questo caso. Io ho ipotizzato di modificare tutti i nomi dei file .pdf tagliando la parte successiva a NOME COGNOME, ma non saprei come farlo in modo automatizzato tramite macro, oppure ho pensato che quando scelgo il file da allegare nella mail forse c'è un codice per chiedere di verificare se almeno una parte del nome contiene NOME COGNOME, ma di nuovo non saprei come realizzarla.

Spero di essere riuscito a descrivere il problema in modo chiaro,
Grazie,
Kiuba

[kiuba]

Allego per maggior chiarezza il codice che userei se i nomi dei file .pdf fossero del tipo COGNOME NOME ridotto a due soli invii, con i file PDF nella cartella C:\Nutrizione\

Codice: Seleziona tutto

Sub Invio_mail()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailAddr As String
Dim Subj As String
Dim BodyText As String
Set OutApp = CreateObject("Outlook.Application")

For i = 1 To 2
    Nominat = Sheets("Foglio1").Cells(i + 1, 2).Value '<<< La cella dove si trova il nome
    Cognominat = Sheets("Foglio1").Cells(i + 1, 3).Value '<<< La cella dove si trova il cognome
    OutFile = "C:\Nutrizione\" & Nominat & " " & Cognominat & ".pdf" '<<< il modo in cui vorrei si chiamasse il file
   

    'compilazione di un testo standard di accompagnamento
    BDT = "Le invio il risultato del test."
    BDT = BDT & vbCrLf & "Cordiali saluti" & vbCrLf
    BDT = BDT & "kiuba"


    EmailAddr = Sheets("Foglio1").Cells(i + 1, 4).Value '<<< La cella dove si trova l'indirizzo mail
    Subj = "Invio risultati test COVID"
    '
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = EmailAddr
        .CC = ""
        .BCC = ""
        .Subject = Subj
        .Body = BDT
        .Attachments.Add OutFile
        .send
    End With

    Set OutMail = Nothing
    '
    Application.Wait (Now + TimeValue("0:00:01"))

    Next i

Set OutApp = Nothing

End Sub


Sarebbe ideale aggiungere anche un controllo dell'esistenza del file COGNOME NOME.pdf e saltare il nominativo nel caso in cui il file non sia presente, completando il tutto con il trasferimento dei file inviati nella cartella C:\Nutrizione_utilizzati, così che sia possibile verificare quali file non sono stati spediti ed eventualmente controllare a mano.

Grazie 1000

[Anthony47]

Ho fatto fatica a decodificare il tuo messaggio; alla fine la mia interpretazione e':
-ogni giorno mi trovo sul disco, nella directory C:\Nutrizione, una serie di file denominati NOME COGNOME xyzxyz.pdf (con xyzxyz imprevedibile)
-ho in un file excel una tabella contenente Nome/Cognome + Relativa email
-ho una macro che gia' sarebbe capace di inviare una email con un allegato
-come faccio invece a inviare una email a tutti i presenti nell'elenco, se e' presente nel mio disco il relativo file NOME COGNOME xyzxyz.pdf?
-sarebbe inoltre possibile spostare gli allegati che vengono inviati nella directory C:\Nutrizione_utilizzati?

Se ci sono andato molto vicino, allora confermami che se invii per errore il file sbagliato al destinatario "giusto" non succede nulla di grave; se "non c'azzecca" allora devi spendere ancora un po di tempo a descrivere da cosa parti e cosa vorresti fare.

Ciao

[kiuba]

Ci sei andato molto vicino, ma purtroppo non ci dovranno essere errori. Cerco di fare un piacere ad un laboratorio che deve gestire i risultati di alcuni tamponi CoViD e quindi non posso correre il rischio di mandare l'allegato sbagliato.

Cerco di essere più preciso:
- i risultati da allegare arrivano da un ente esterno già denominati COGNOME NOME -XY- KLMHPDF.pdf ( esattamente questa struttura, ma con cifre al posto di X,Y.. ad esempio ROSA_GINO -78- 5654PDF.pdf)

-Ad ogni file di questo tipo deve essere associato l'indirizzo mail preso da un foglio excel in cui ci sono gli elenchi delle persone che aspettano l'esito del tampone.

-Vorrei realizzare tutto questo tramite macro in un foglio excel.

DETTAGLI della Macro
-guarda la prima riga in excel e confronta il nome e cognome della prima persona con i file .PDF nella cartella C:\Nutrizione\
- se trova il file .PDF con lo stesso nominativo invia la mail con il giusto allegato, cancella la riga corrispondente in excel (o la sposta in un altro foglio) e infine sposta il file .PDF allegato in una cartella C:\Nutrizione_utilizzati

- se non trova il file .PDF corrispondente passa alla riga successiva

Grazie ancora per il vostro tempo che mi dedicate.
Kiuba

[kiuba]

Nel riportare il nome del file d'esempio
ROSA_GINO -78- 5654PDF.pdf ho posto erroneamente il trattino basso al posto di uno spazio vuoto. L'esempio corretto è ROSA GINO -78- 5654PDF.pdf

[zsadist]

Ciao
scusa, sono molto duro a capire, devi avere un pò di pazienza

tu hai una cartella con del file pdf con nome tipo "NOME COGNOME - XX - XXXXPDF. pdf" dove X equivale a un numero (non uguale per tutte le X)

hai una lista di nomi, di cui non sai per certo se hai ricevuto il file pdf correlato

vuoi una macro per creare una mail destinata ai nominativi che hai in lista, allegando il file correlato se esiste, altrimenti la mail non viene fatta..

corretto così?

se si, domanda, perchè nella macro che hai postato vi è la stringa

Codice: Seleziona tutto

OutFile = "C:\Nutrizione\" & Nominat & " " & Cognominat & ".pdf" '<<< il modo in cui vorrei si chiamasse il file

se il file PDF già esiste?

[zsadist]

In attesa di una tua risposta..
premetto che difficilmente guarderò il forum prima del 28 dicembre 2020

tu dici che hai un file con Nome, Cognome, Indirizzo Mail

vi è un campo che può identificare univocamente il file PDF?

perchè non è difficile trovare nomi uguali (Mario Rossi..)

[kiuba]

Ciao
scusa, sono molto duro a capire, devi avere un pò di pazienza

tu hai una cartella con del file pdf con nome tipo "NOME COGNOME - XX - XXXXPDF. pdf" dove X equivale a un numero (non uguale per tutte le X)

hai una lista di nomi, di cui non sai per certo se hai ricevuto il file pdf correlato

vuoi una macro per creare una mail destinata ai nominativi che hai in lista, allegando il file correlato se esiste, altrimenti la mail non viene fatta..

corretto così?

se si, domanda, perchè nella macro che hai postato vi è la stringa

Codice: Seleziona tutto

OutFile = "C:\Nutrizione\" & Nominat & " " & Cognominat & ".pdf" '<<< il modo in cui vorrei si chiamasse il file

se il file PDF già esiste?

Ho messo quella riga perchè se il file si chiamasse semplicemente NOME COGNOME avrei risolto buona parte del problema. All'inizio del post chiedevo se era possibile rinominare i file PDF in modo da togliere la parte successiva a NOME COGNOME, a quel punto le mail avrebbero il giusto allegato e mancherebbe solo la parte che salta gli indirizzi senza allegato e rimette in ordine celle e file utilizzati.

Tutto il resto che hai scritto è esatto!

Naturalmente se avete altre soluzioni da proporre la macro può essere cambiata completamente, era solo per darvi un'idea di come avevo pensato di impostare la macro.

[kiuba]

In attesa di una tua risposta..
premetto che difficilmente guarderò il forum prima del 8 dicembre 2020

tu dici che hai un file con Nome, Cognome, Indirizzo Mail

vi è un campo che può identificare univocamente il file PDF?

perchè non è difficile trovare nomi uguali (Mario Rossi..)

Purtroppo non ho un campo con il nome corrispondente a quello del file, perchè la seconda parte del nome, quella dal trattino in poi per capirsi viene generata da qualcun altro e io non posso prevedere in nessun modo che codice avrà, so solo che la lunghezza della parte successiva a NOME COGNOME avrà esattamente quella struttura ( -XX- xxxxPDF.pdf)

[kiuba]

Risposta all'ultimo messaggio di zsadist
Scusami ho capito solo ora il senso della domanda: vi è un campo che può identificare univocamente il file PDF? avevo frainteso il senso.

Ho altri dati dei singoli utenti, ma purtroppo nel nome del file PDF c'è solo NOME COGNOME, quindi in caso di omonimie si genererebbe sicuramente un problema. Visto che saranno dai 50 ai 100 file quotidiani, potrei optare per mettere in ordine alfabetico i nomi e controllare a mano ogni giorno se ci sono omonimie. Anche se preferirei evitarlo.

Secondo te si può aggiungere una parte alla macro che inizialmente faccia il controllo delle eventuali omonimie nel file excel e in quel caso blocchi preventivamente l'invio automatico generando un messaggio d'allerta?

Grazie per l'osservazione.

[Anthony47]

Per me il processo, basato come e' sui nomi, e' assolutamente impostato male; ne' afferro quale sara' il metodo per risolvere eventuali omonimie; ne' mi pare sia garantita la privacy con il risultato di un esame sensibile associato in chiaro a un nominativo.
Pertanto mi dissocio dall'automatizzare un processo che son convinto produrra' risultati erratici; ma se vuoi procedere:
A) puoi controllare sul foglio se ci sono omonimie; es in una colonna libera usi la formula =

Codice: Seleziona tutto

SOMMA(($N$2:$N$100=N2)*($O$2:$O$100=O2)*($O$2:$O$100<>""))

Da confermare con Contr-Maiusc-Enter. Se il risultato e' >1 si tratta di omonimia

B) puoi facilmente cercare se c'e' nel tuo percorso un file Nome#Cognome#- (ho usato # per indicare "spazio") usando il comando Dir

Codice: Seleziona tutto

StartPath = "C:\prova\"
'...
NomeFile = Dir(StartPath & Cells(I, "O") & " " & Cells(I, "N") & " -*.pdf")

Se NomeFile <> "" allora il file esiste e lo alleghi usando .Attachments.Add "C:\Percorso\" & NomeFile
Altrimenti quel nominativo non ha un file prontamente associabile e salti l'invio della mail

C) puoi facilmente spostare un il file dopo che la mail e' stata inviata utilizzando ad esempio il comando Name:

Codice: Seleziona tutto

StartPath = "C:\prova\"
DestPath = "C:\provaDue\"
'....
Name StartPath & NomeFile As DestPath & NomeFile

D) e' opportuno che in una colonna libera venga scritto quale file viene inviato a quell'utente (servira' alla commissione inquirente)

Insomma l'architettura complessiva sarebbe del tipo

Codice: Seleziona tutto

For I = 2 To Cells(Rows.Count, "O").End(xlUp).Row       'Loop su tutte le righe coi nomi
    If Cells(I, "M") = 1 Then                           'Controllo che la riga non sia un duplicato
        'calcolo NomeFile
        '...
        If Len(NomeFile) > 0 Then
            'Istruzioni per creazione della mail
            'Istruzione per allegare il file
            Cells(I, "Z").Value = NomeFile              'Registrazione in Z del nome file inviato
            '...
            'Invio email
            'Attesa 1 sec
            'Istruzioni per spostare il file
        End If
    End If
Next I


Ho immaginato che il check duplicato sia in colonna M, e che in Z venga scritto il nome del file inviato

Ma ripeto: secondo me un processo con dati sensibili centrato sull'uso di nome/cognome e' destinato a produrre casini

BUONE FESTE

[zsadist]

Buondì
Sono d'accordo con Anthony47
Con questi dati sensibili e senza campi per fare confronti univoci i rischi di errori sono tanti e problematici..
Forse, e dico forse, vi è un tentativo da fare se:
I file PDF sono testuali
Se sono dello stesso formato, ovvero se hanno una forma standard
Se contengono dati che puoi associare a campi del tuo file..
Se ci sono queste condizioni, si può tentare facendo "leggere" i file PDF nelle parti interessate con una macro, per associarli in modo univoco

[kiuba]

I file PDF hanno tutti la stessa intestazione, in tale intestazione è riportata la data di nascita, data che è presente anche nel file excel.

[zsadist]

E.. il formato? Standard?
Cioè, questa data di nascita si trova nella stessa posizione in ogni file PDF?

[kiuba]

Sì, il formato è standard e la posizione è sempre la stessa.

[zsadist]

Ok..
Allora c'è una speranza..
Ma come ho detto qualche post fa.. dovrai attendere dal 28 in poi
Intanto auguri

[kiuba]

Auguri anche a te!
Grazie 1000

[Anthony47]

L'invio di un certificato, dal cui contenuto (che sia esatto o sia sbagliato) puo' derivare un danno al ricevente, per me non e' demandabile a una macro, che non risponde al principio di "accountability".
Il massimo che la macro puo' fare e' compilare una email e metterci un allegato ragionevolmente giusto.
Poi una persona incaricata di gestire il processo esamina la mail, apre ed esamina l'allegato e determina se e' quello giusto; se "giusto" firma l'email e la invia.

Altre vie secondo me ("secondo me") non rispecchiano il livello minimo di sicurezza che le gestione della Salute richiede.

Ciao

[zsadist]

Buongiorno
Si, ha ragione Anthony47
Senza considerare la sfida nel riuscire a realizzare la macro, rimane la cosa più importante.. sono dati troppo sensibili e non vi è la certezza sulla sicurezza di ciò che si ottiene..
Mi spiace Kiuba, ma devo rinunciare al progetto..
Deve essere il laboratorio di cui parli a prendersi la responsabilità di questo invio dati..
Ancora auguri a tutti

[kiuba]

Concordo anch'io con voi.
Proporrò di gestire l'invio delle fatture tramite macro, in modo da alleggerire almeno quella componente.
In quel caso riuscirò ad avere la certezza del file da utilizzare perché posso usare il numero di fattura, oltre al nome e cognome.
Grazie ai vostri consigli questo sono in grado di realizzarlo completamente. Ho imparato molto. Bellissima la soluzione di Anthony47 per il controllo dei doppioni! Cosa può fare una moltiplicazione..

Pensavo infine di modificare la macro per l'invio dei risultati clinici in modo da renderla più accettabile così:
-apra la posta
-controlli le omonimie e la presenza dell'allegato
-prepari il testo, l'allegato. l'indirizzo e l'oggetto
-attenda l'invio manuale (In modo che l'incaricato possa eseguire il controllo)
-passi alla mail successiva

Immagino di dover sostituire .Send, con . Display.. Però come faccio a far attendere alla macro l'invio manuale?

Grazie ancora e auguri a tutti!