Scadenziario automatizzato Excel invio email

[luca_aa]

Buongiorno a tutti. Inizio ringraziando quanti vorranno tentare di darmi una mano.
Sono praticamente digiuno di programmazione e vi chiedo di essere "semplici".
Ho verificato prima di postare ed ho trovato diversi topic simili alla mia richiesta, ma non perfettamente aderenti e non so come cucire le parti di codice che fanno al mio caso.

Situazione: dispongo di un file excel che riporta:
col. A: mail destinatario
col. B: data scadenza contratto
col. C: mesi preavviso scadenza
col. D: dicitura da incorporare nel messaggio
col. E: link al file del contratto
col. F: altra specifica relativa al contratto in scadenza

Vi spiego il progetto, dividendo in 3 step:

1) realizzare una macro da lanciare manualmente, capace di percorrere l'intero file excel e se col. B meno col. C corrisponde ad oggi (più o meno 3 giorni per saltare i Week end)...
2) ...allora inviare automaticamente una mail al destinatario indicato in col. A, con un testo standard "buongiorno il contratto è in scadenza", poi personalizzato da quanto contenuto in col. D, col. E e col. F
3) Far si che la macro scriva sul file excel nella colonna G che per quella riga, è stata inviata la mail di promemoria (magari con la data di invio)

Di nuovo grazie per l'aiuto.

[mezzodestro]

Un saluto a tutti gli Utenti e allo Staff,
mi sono appena iscritto, uso Excel per motivi di lavoro e da qualche anno mi diverto ad imparare VBA dalla rete a perditempo. In questo caso credo di poterti essere utile, se c'è un coefficiente di difficoltà da 1 a 10 siamo a livello 2 credo...Io arrivo al massimo a 3, quindi ci rientro.
Ho ipotizzato che la riga 1 contenga l'intestazione e i dati iniziano dalla cella A2.
Funziona con Office 2003, se hai una versione successiva vedremo cosa fare.....

La Macro è più o meno così:

Codice: Seleziona tutto

Sub invia_email()

    MsgBox ("ACCEDI AD OUTLOOK PRIMA DI PROCEDERE") ' MESSAGGIO: APRI OUTLOOK
    oggi = Date
    ultimariga = Range("A65000").End(xlUp).Row      ' CALCOLA ULTIMA RIGA DELLA TABELLA
    For i = 2 To ultimariga
    If Range("G" & i) <> "" Then GoTo Salta         ' SALTA L'ELABORAZIONE SE E' GIA' SOLLECITATO
    deadline = Range("B" & i).Value - (30 * Range("C" & i).Value)  ' CALCOLA LA DATA DI RIFERIMENTO (30 giorni X mesi col. "C")
    If oggi < deadline Then GoTo Salta
    variabileEmailDelDestinatario = Range("A" & i).Value        ' DA QUI IN POI ESPORTA IN OUTLOOK
    allegato = Range("E" & i).Value
    If Dir(allegato) = "" Then GoTo ErroreUno                    ' CONTROLLA L'ESISTENZA DEL FILE ALLEGATO (sennò genera un errore)
    Set VarOggApplicazioneOutlook = CreateObject("Outlook.Application")
    Set VarOggMailInOutlook = VarOggApplicazioneOutlook.CreateItem(olMailItem)
    VarOggMailInOutlook.To = variabileEmailDelDestinatario
    VarOggMailInOutlook.Subject = "SCADUTO"
    VarOggMailInOutlook.Body = Range("D" & i).Value & " - " & allegato & " - " & Range("F" & i).Value
    VarOggMailInOutlook.Attachments.Add allegato
    VarOggMailInOutlook.Display                                 ' OPZIONE PREVIEW EMAIL (usa la riga sotto se vuoi inviare direttamente)
    ' VarOggMailInOutlook.Send                                  ' OPZIONE SPEDISCI DIRETTAMENTE
    Range("G" & i) = oggi                                           ' ANNOTA LA DATA DEL SOLLECITO SUL DATABASE nella colonna "G"
Salta:
    Next i
    GoTo Fine
ErroreUno:
    MsgBox ("Errore su Scaduto " & Range("A" & i).Value & " :allegato INESISTENTE")
    GoTo Salta
Fine:
End Sub

Buon lavoro!

[luca_aa]

Bingo! Funziona, anche in excel 2007.
Intanto grazie. Sarai anche alle prime armi, ma devo dire che ci sai fare.

Ti aggiorno sull'avanzamento del mio progetto. Sto sperimentando cercando nel contempo di capirci qualcosa :
ho visto che hai inserito nel codice una funzione capace di allegare un file alla mail; la mia esigenza era soltanto di allegare il percorso del file (il destinatario infatti lo può recuperare su un NAS in rete) e così ho eliminato quella parte.

Visto che sei stato così disponibile, io provo a farti qualche domanda, cercando di camminare (vabbè, magari iniziamo a gattonare ) con le mie gambe: devo personalizzare e rendere più fruibile il messaggio (il body) della mail. Mi sai indicare una guida che spieghi quali siano i comandi per la formattazione del testo (bold, a capo, punteggio...)? Speravo tanto funzionasse l'html...

Per ora mi fermo, ma ho già in mente qualche altra miglioria . Andiamo per gradi, direi.

Ancora grazie!

[luca_aa]

Avevo parlato troppo presto.
Intanto ho rivalutato la funzione che allega il file e l'ho ripristinata; poi ho imparato ad inserire gli "a capo" sia nella scrittura del codice (con _) che nel testo (comando & vbCr).

Sino ad ora ho fatto girare la macro con una sola riga. L'ho testata con due righe, ma gira soltanto per i dati relativi alla prima riga. A tal proposito, vorrei vagliare con te/voi la possibilità (se non già prevista) che quando mi si presenta l'invio della mail, qualora io decida di non volerla inviare, la macro prosegua presentandomi la mail successiva.

Incollo la macro così com'è in questo momento, sia mai che nelle mie elucubrazioni non abbia danneggiato qualcosa...

Codice: Seleziona tutto

Sub invia_email()

MsgBox ("ACCEDI AD OUTLOOK PRIMA DI PROCEDERE") ' MESSAGGIO: APRI OUTLOOK

oggi = Date

ultimariga = Range("A65000").End(xlUp).Row ' CALCOLA ULTIMA RIGA DELLA TABELLA
For I = 2 To ultimariga

If Range("G" & I) <> "" Then GoTo Salta ' SALTA L'ELABORAZIONE SE E' GIA' SOLLECITATO

deadline = Range("B" & I).Value - (30 * Range("C" & I).Value) ' CALCOLA LA DATA DI RIFERIMENTO (30 giorni X mesi col. "C")

If oggi < deadline Then GoTo Salta

variabileEmailDelDestinatario = Range("A" & I).Value ' DA QUI IN POI ESPORTA IN OUTLOOK

allegato = Range("E" & I).Value
If Dir(allegato) = "" Then GoTo ErroreUno ' CONTROLLA L'ESISTENZA DEL FILE ALLEGATO (sennò genera un errore)

Set VarOggApplicazioneOutlook = CreateObject("Outlook.Application")

Set VarOggMailInOutlook = VarOggApplicazioneOutlook.CreateItem(olMailItem)

VarOggMailInOutlook.To = variabileEmailDelDestinatario

VarOggMailInOutlook.Subject = "Segnalazione scadenza Legal"

VarOggMailInOutlook.Body = "Questo messaggio è generato automaticamente." & vbCr & vbCr & "Il documento in allegato, registrato sull'archivio XXXXXXX, è prossimo alla scadenza." & vbCr & "Questo il sunto delle informazioni:" & vbCr & vbCr _
& " Protocollo XXXXXXXX: " & Range("D" & I).Value & vbCr _
& " Tipo documento: " & Range("F" & I).Value & vbCr _
& " Rapporto documento con: " & Range("H" & I).Value & vbCr & vbCr _
& " Scadenza registrata: " & Range("H" & I).Value & vbCr _
& " Tipo scadenza: " & Range("H" & I).Value & vbCr _
& " Preavviso (mesi): " & Range("H" & I).Value & vbCr _
& " Data inizio/sottoscrizione: " & Range("H" & I).Value & vbCr & vbCr _
& " Archiviato in: " & Range("H" & I).Value & vbCr _
& " Note registrate: " & Range("H" & I).Value & vbCr _
& " Keywords: " & Range("H" & I).Value & vbCr _
& vbCr & vbCr & "Non dimenticare, una volta rinnovato\modificato\eliminato il documento, di informare il XXXXXX per l'aggiornamento dell'archivio!" & vbCr _
& vbCr & "Grazie per la collaborazione."


VarOggMailInOutlook.Attachments.Add allegato

VarOggMailInOutlook.Display ' OPZIONE PREVIEW EMAIL (usa la riga sotto se vuoi inviare direttamente)
' VarOggMailInOutlook.Send ' OPZIONE SPEDISCI DIRETTAMENTE
Range("G" & I) = oggi ' ANNOTA LA DATA DEL SOLLECITO SUL DATABASE nella colonna "G"


Salta:
Next I
GoTo Fine
ErroreUno:
MsgBox ("Errore su Scaduto " & Range("A" & I).Value & " :allegato INESISTENTE")
GoTo Salta
Fine:
End Sub

Resto in attesa di news e vado a sperimentare ulteriori implementazioni.
Saluti a tutti.

[mezzodestro]

vacci piano coi complimenti perché sto già al limite pure io....

MI sembra di aver capito che ti si sono aperte 3 problematiche:
1) formattare il testo del messaggio
2) ti è funzionato per una sola riga
3) vorresti poter decidere preventivamente quali email spedire

andiamo un po' alla volta...

1) non ho mai avuto molto appeal per le formattazioni, anzi vedo che sei più avanti di me

2) i motivi possono essere molteplici: il programma così com'è identifica un valore "ultimariga" come l'ultimo valore presente nella colonna A. Per verificare il valore "ultimariga" basta inserire una riga con su scritto "STOP" subito dopo "FOR i =2 TO ULTIMARIGA", esegui la macro (che si bloccherà in virtù dello stop), rientri nell'editor VBA in modalità DEBUG e quando transiti col mouse sopra il campo "ultimariga" ti appare un commento col valore corrispondente.
Un secondo motivo potrebbe essere che nella seconda riga che hai messo la scadenza era più lontana (calcolo colonnaB moltiplicato colonnaC per 30) e in quel caso il programma non va a generare nulla. Non prendo in considerazione il caso in cui manca l'allegato poiché ti sarebbe apparso un msg di errore. Magari prima prova a mettere 4 o 5 righe, poi semmai ne riparliamo..

3) così come gira a me puoi decidere via via se inviare la bozza di email che si presenta. Se non la vuoi mandare bisogna cliccare la "X" rossa in alto e rispondere al messaggio di Outlook. Dal momento in cui facciamo "VarOggMailInOutlook.Display" il controllo passa a Outlook, quindi comanda lui.... Credo si possa riprendere i comandi da excel tramite VBA (tempo fa mi sono interessato, ma devo riguardare...) per poi a fine elaborazione ritrovarsi su Outlook i 4-5-6 nuovi messaggi da inviare....ti guardo e ti ri-aggiorno...

tu intanto vai avanti col punto 2)

PS: Tempo fa comprai una guida sulle macro dove probabilmente dice anche qualcosa sulle formattazioni, ma posso dirti che sul web si trova tutto e meglio

[mezzodestro]

trovato il comando:

per riprendere il controllo di EXCEL bisogna inserire la riga:

AppActivate "Microsoft Excel"

subito dopo la riga "VarOggMailInOutlook.Display"

[luca_aa]

Ciao, scusa il silenzio prolungato, ma ho dovuto spostare la mia attenzione su un altro lavoro. Adesso cerco di concludere questo. Intanto ringrazio per l'ulteriore dritta sul comando da outlook, che credo per ora di non implementare.

Ho rimesso in funzione la macro accorgendomi che probabilmente non andava perchè non tutti i campi erano correttamente compilati sul file excel. Ora devo adattarla al file vero e proprio (non quello con le colonne esemplificative che ho esposto nel post). Tutto bene fino a quando ho riconosciuto l'abbinamento dei comandi Range, ma non capisco se la particella " & I " sia parte della sintassi del comando ( e quindi debba rimanere com'è), oppure tu abbia utilizzato la prima colonna libera ( I, per l'appunto) come appoggio per le elaborazioni della macro.

Scusa le castronerie, ma sto cercando di arrivarci ragionando. Grazie per la pazienza .

[mezzodestro]

Anche io ho un sacco di cose un ponte...so cosa vuol dire, non ti preoccupare.

Nel caso del codice sopra descritto la lettera "I" di "Imola" sta a significare quello che i programmatori un po' attempati chiamano "indice", ovvero un valore numerico in genere progressivo utile alla generazione degli eventi.
Nel nostro caso "i" va da 2 (la prima riga da considerare) fino a "ultimariga", che sarà il valore numerico relativo alla tua ultima casella occupata della colonna A.
La sintassi Range("G" & I) ti sta a significare che quando il valore di "i" è 2, stai analizzando la cella "G2", Il separatore "&" (e commerciale) è un simbolo unificatore di stringhe. Se avessi dovuto fare un controllo su una sola riga avrei scritto nella sintassi di VBA: Range("G2"), ma poiché noi dobbiamo moltiplicare lo stesso calcolo per G2,G3,G4,G5 ecc... dobbiamo associare una variabile al valore corrispondente il numero della riga.
Tieni conto che invece di Range("LetteraColonna+NumeroRiga") si potrebbe usare anche il comando Cells (righe , colonne), solo che bisogna stare attenti a non incasinarsi poiché l'ordine dei riferimenti è invertito, prima le righe, poi le colonne (nel nostro caso sarebbe stato Cells( i , 7) ).
Il fatto che "i" corrispondeva alla prima colonna libera è una pura coincidenza.

Spero di essere stato il più chiaro possibile.

Un saluto

[luca_aa]

Ciao Mezzodestro,
l'avevo detto che sparavo castronerie!!!
Bhe almeno ho imparato qualcosa...

Ora si pone un'altra questione: ho bisogno di introdurre un altro controllo IF. Ti spiego: oltre alla condizioni originali della verifica della data e del sollecito già eventualmente effettuato, serve vincolare la creazione della mail di sollecito dopo aver verificato cosa appare nella colonna X. Se il valore contenuto è "pippo" o "paperino", allora lo script può proseguire, altrimenti deve saltare al record successivo.

Da profano, immagino la funzione debba essere espressa con un comando IF, poi ovviamente non conosco la sintassi e lo scrivo prendendo spunto da quanto hai già fatto tu. Mi manca come esprimere la condizione "or", per dire che se il valore è diverso da "pippo" oppure "paperino" deve andare a Salta :

IF Range("X" & I) <> "pippo" Then GoTo Salta ' SALTA L'ELABORAZIONE SE IL VALORE E' DIVERSO DA Pippo oppure Paperino

Inoltre, è corretto inserire il controllo immediatamente dopo la riga: For I = 2 To ultimariga ?
Chiedo ancora una volta aiuto. Grazie!

[mezzodestro]

Ti conviene metterlo dopo l'altro IF.

Se sei alle prime armi e il futuro ti cimenterai in queste piccole procedure di ti consiglio di evitare lo "OR", per il semplice motivo che quando non ti torna qualcosa e sei in DEBUGGING (modalità di correzione degli errori) puoi meglio isolare l'evento IF singolarmente per capire quale delle due condizioni non va (per farti un esempio stupido, se scrivi "PIPP0" invece di "PIPPO" sulla colonna X, l'email non parte e vorrai sapere quale delle 2 condizioni "pippo" o "paperino" ti manda a "Salta").

Poi nel nostro caso poi non potresti farlo, aggiungi dopo il primo IF:

IF Range("X" & I) = "pippo" Then GoTo Prosegui
IF Range("X" & I) = "paperino" Then GoTo Prosegui
goto Salta
Prosegui:

se poi non capisci qualcosa approfondiamo...

ciao

[FrancescoS80]

Buongiorno mi scuso se mi inserisco in questa discussione se non e un problema.
Si può aggiungere anche un metodo o proprietà che possa posticipare l'invio a un'altra data e ora che prende da una cella esempio C5 una sorta di posta programmata grazie

[Anthony47]

Intanto "Benvenuto nel forum"
Pero' accodarsi a una discussione vecchia di "qualche anno"...

Comunque, per l'invio ritardato si puo' usare la proprieta' DeferredDeliveryTime. Ad esempio:

Codice: Seleziona tutto

    EmailAddr = Sheets("Foglio1").Range("B1").Value
    Subj = "Invio risultati questionario"
    '
    Set OutMail = OutApp.CreateItem(0)
    sTime = Now + TimeValue("01:00:00")    'Delay 1h0m0s
    With OutMail
        .To = EmailAddr
        .CC = ""
        .BCC = ""
        .Subject = Subj
        .htmlBody = BDT
        '.Attachments.Add OutFile
        .DeferredDeliveryTime = sTime
'        .Display 'or use .send    'Visualizza
        .Send                     'Invio
    End With
    Application.Wait (Now + TimeValue("0:00:01"))
    Set OutMail = Nothing


In questo modo la mail sara' inviata "un'ora dopo"
Pero', dalle prove fatte, mi sembra che per funzionare Outlook debba essere aperto, perche' il meccanismo funzioni.

Ciao