Invio massivo di Email con Excel

Può capitare di dover inviare ai propri clienti o fornitori delle e-mail con del testo personalizzato a seconda del destinatario, oppure con un allegato diverso per ogni destinatario.

Pensa all’invio delle dichiarazioni di intento, o delle dichiarazioni di origine, oppure all’invio di inviti personalizzati per la partecipazione ad un evento, o ai certificati di partecipazione, ai biglietti di auguri, e molto altro!

Se non hai un software CRM che ti permetta di automatizzare tutto questo, niente paura! puoi farlo con Excel !

In questo articolo ti spiego come fare.

La lista della spesa

Ti basterà avere tre cose:

• Microsoft Office (Excel oppure Access) con abilitazione a VBA.
• blat, un programmino a riga di comando molto potente in grado di inviare email con il protocollo STMP
• un server di posta per l’invio massimo, come ad esempio il gratuito SMTP2GO. In alternativa, se le email non sono molte puoi usare anche il tuo server di posta aziendale.

L’idea di base

Microsoft Office è un pacchetto davvero meraviglioso che non può assolutamente mancare in un’azienda. La forza di questa suite di strumenti (in particolare Excel e Access) è la possibilità di scrivere codice VBA (Visual Basic for Application) e creare con esso automazioni incredibili non solo per l’elaborazione dei dati al loro interno, ma anche per l’integrazione con il Sistema Operativo e con altri strumenti esterni.

Personalmente ci ho fatto di tutto: da sistemi per acquisizione di dati di fabbrica, all’interfacciamento con i PLC, a controlli per bilance elettroniche, a sistemi per il controllo di forni industriali, sistemi di marcatura, presse e molto altro ! VBA si presta a moltissimi impieghi, e quindi perchè non utilizzarlo anche per inviare e-mails ?

D’ora in poi parlerò solo di Excel perché è più conosciuto e semplice da usare, ma la cosa è estendibile anche ad Access.

L’idea è quella di utilizzare quindi una macro VBA in Excel per inviare le e-mails.

Excel però da solo non può inviare e-mail però può chiamare un comando esterno che può farlo per lui. questo comando sarà appunto blat, un programma in grado di inviare comandi per l’invio di posta elettrronica aun server di posta.

ma procediamo per passi.

Prima però voglio ricordarti che inviare email senza il preventivo consenso degli interessati può portare a diverse beghe di natura “privacy”, rendendoci di fatto possibile bersaglio di due diverse tipologie di azioni: una di natura amministrativa (da proporsi davanti al Garante della Privacy), e l’altra di natura giudiziaria, al fine di ottenere un eventuale risarcimento dei danni. Ti raccomando perciò di utilizzare quanto ti sto per spiegare solo per invio ai tuoi clienti/fornitori e non per acquisire nuovi clienti.

Creazione di un account gratuito su SMTP2GO

Questo passo è opzionale ma da me vivamente raccomandato. Inviare molte email “massive” utilizzando il proprio server di posta aziendale infatti espone al rischio di far perdere la buona reputazione al proprio server, e farlo finire in qualche “black list” marcandolo come server che invia SPAM, con il conseguente blocco della possibilità di far recapitare i normali messaggi. Ai server di posta non piace ricevere SPAM, e sono “addestrati” per tentare riconoscere gli abusi, e per difendersi.

Per questo motivo utilizzeremo un server appositamente creato, dietro al quale c’è qualcuno che si preoccupa di mantenere “pulita” la reputazione. Un secondo motivo per farlo è che così facendo alleggeriremo il carico di lavoro al nostro server.

Vai all’indirizzo www.smtp2go.com e registrati con un piano GRATUITO. Il servizio è gratuito con un limite di 1000 email al mese.

Se ti servono più invii mensili all’indirizzo www.smtp2go.com/pricing/ puoi trovare i vari piani e relativi prezzi: da 10k , 40k, 100k, 250k, 500k, 1Milion e oltre 3 Milioni di e-mail al mese.

Creazione di un utente “mittente”

Per prima cosa diciamo a SMTP2GO quale sarà l’indirizzo e-mail che comparirà come mittente delle e-mails.

Per fare questo dobbiamo creare un utente SMTP.

Dopo la registrazione vai quindi su Settings –> SMTP Users

Ora annotati i dati che vedi sotto la voce “Connecting via SMTP”, ci serviranno piàù tardi:

Connecting via SMTP
SMTP Server: mail.smtp2go.com

SMTP Port: 2525
Alternative ports: 8025, 587, 80 or 25. TLS is available on the same ports.

SSL is available on ports 465, 8465 and 443.

Crea il primo utente per l’invio, fai click sul bottone Add SMTP User

e compila Username con l’indirizzo email della tua azienda con cui vuoi che compaia il mittente

Scegli una Password arbitraria; non deve essere per forza quella vera della tua casella e-mail mittente. annotatela.

Clicca su SAVE

Autorizza SMTP2GO a inviare per conto del tuo dominio

ATTENZIONE: Se al posto di SMTP2GO preferisci utilizzare il tuo server aziendale questo passaggio non è necessario. Se invece utilizzate SMTP2GO è molto probabile che le email non vengano accettate dai server dei destinatari.

Per fare in modo che i server di posta dei destinatari accettino di buon grado le nostre email, dobbiamo autorizzare SMTP2GO a inviare e-mails per conto del nostro dominio. Questo di fa in quattro passaggi:

1. Aggiungi il dominio per il cui conto vogliamo spedire

Vai su Settings–>Sender Domains (Domini di Invio)

digita il dominio della tua azienda e fai click su “Add Domain“

2. Pubblica nel nostro DNS alcuni record di tipo “CNAME”

Adesso clicca sul dominio appena inserito, ti comparirà una finestra con l’elenco dei record CNAME

3. Inserisci i record CNAME indicati

Ora recati nella gestione DNS del tuo dominio e inserisci tutti i record CNAME indicati nella schermata precedente, con i relativi valori. Slava la configurazione e attendi almeno 10 minuti. Nota: potrebbero volerci anche parecchioe ore affinchè il DNS si propaghi a livello globale.

4. Verifica i record DNS

Adesso torna nella pagina “Configura il tuo DNS” di SMTP2GO e fai click sul bottone “Verify”. SMTP2GO teneterà di leggere quei record nel DNS convalidando di fatto la configurazione.

Quando il DNS si è propagato e se i record sono stati inseriti correttamente, ecco apparire le spunte verdi che indicano che la configurazione è OK ! la configurazione SMTP2GO è terminata.

Installazione e configurazione di “blat”

Scarica blat Full Version da questo link

Dentro l’archivio compresso scaricato trovi una prima cartella e dentro poi una seconda chiamata “full”. Scompatta i files “blat.exe” e “blat.dll” nella cartella C:\Windows\System32. Così facendo garantiamo a blat la possibilità di poter essere chiamato da qualsiasi percorso locale.

Qui la parte più difficile. dobbiamo istruire BLAT a inviare le email dicendogli:

• con quale email spediamo
• qual’è il server per l’invio
• con quali crendeziali (username e password) collegarsi al server per l’invio

Apri un Prompt dei comandi in questo modo: premi i tasti Win + R e nella finestra che compare digita “cmd”. premi poi INVIO

Si apre la finestra del prompt dei comandi di Windows. come prima cosa digita: blat e premi INVIO.

Se appare qualcosa come questo qui sotto significa che blat è chiamabile correttamente.

Configura blat con la seguente comando:

blat -install -f <sender-email> -server <email-server> -u <login> -pw <password>

che in realtà diverrà:

blat -install -f LAMIAMAIL@ILMIODOMINIO.COM -server mail.smtp2go.com -u NOMEUTENTE -pw LAMIAPASSSWORD

ora inviati una mail di prova per verificare che tutto sia ok:

blat -s "Oggetto" -body "corpo della mail" -to destinatario@dominio.com -html

ovviamente sostituisci destinatario@dominio.com con una tua email

l’ultimo parametro “-html” forza il contenuto in HTML altrimenti arriverà solo in formato testo.

se tutto è andato come doveva vi arriverà una mail con oggetto “Oggetto” e nel corpo la scritta “corpo della mail”

Invia le e-mail con Excel

Ho preparato per te un foglio Excel già pronto ma che potrai personalizzare a piacimento e che gestisce entrambe le esigenze:

• poter personalizzare il testo della mail in base al destinatario (inserendo i valori in colonne diverse)
• poter inviare eventualmente un allegato diverso per ogni destinatario (specificando il nome dell’allegato in una colonna Excel)

Scarica da questo link l’archivio Zip che ho preparato per te: https://github.com/alessandroscola/mass-sending-of-e-mails-with-excel

Scompattalo nella cartella “Documenti” del tuo PC.

Troverai al suo interno:

• il file Excel con la macro
• 3 allegati PDF di esempio: 001.pdf, 002.pdf, 003.pdf

Apri il foglio Excel, si presenta così:

Compila l’OGGETTO della mail e il TESTO. Per il testo ti ricordo che per inserisce gli “a capo” devi premere ALT+INVIO.

Poi dalla riga 6 compila i campi seguenti.

In colonna A scrivi la RAGIONE SOCIALE del destinatario. In colonna B scrivi la MAIL.

Nelle colonne C e D scrivi il nome del file separando NOME ed ESTENSIONE. Questo ti permette di concentrarti solo sul nome. Ad esempio il nome dell’allegato potrebbe coincidere con il CODICE CLIENTE/FORNITORE (magari estrapolato dal tuo gestionale) mentre l’estensione potrà essere sempre la stessa (ad esempio “.pdf” o “.docx” o altro…).

Sarà tua cura poi salvare nella stessa cartella del file excel, tutti gli allegati da spedire, con i nomi corretti (ad es 001.pdf 002.pdf 003.pdf) assecondando appunto il codice cliente/fornitore.

Nella colonna E ho inserito un ipotetico “codice sconto” che verrà inserito nel TESTO della mail al posto del tag %E% .

Per inviare il tutto clicca sul bottone “Invia e-mails”

La macro viene eseguita e per ugni riga a partire dalla riga 6, il testo verrà personalizzato con i valori delle colonne A ed E.

Quindi l’email inizierà con “Spett.le <LA RAGIONE SOCIALE VERA indicata incolonna A>”

Analogamente al posto di %E% verrà inserito il codice sconto.

Inoltre per ogni allegato indicato, verrà verificata la sua reale esistenza nella cartella in cuji è presente il file Excel. Se il file esiste allora verrà allegato, altrimenti l’email sarà inviata senza l’allegato.

Nella colonna F “CONTROLLO ALLEGATO” verrà scritto “Ok” se l’allegato è stato trovato, oppure il messaggio: “ATTENZIONE: ALLEGATO NON TROVATO!” in caso contrario.

Personalizzare il codice VBA

La macro è semplice da comprendere. Puoi personalizzarla a piacimento.

Option Explicit
Public conn, rs As Object

' Code by Alessandro Scola www.alessandroscola.com
'
' Rimbember for INSTALL BLAT:
' blat -install -f <sender-email> -server <email-server> -u <login> -pw <password>
'
' To SEND with BLAT:
'   blat -Subject "email Subject" -body "email body" -to recipient@domain.xxx -html
'
'   alternatively with the email body in a external file "email_body.txt", the command is:
'   blat -s "c:\path\to\email_body.txt" -Subject "email Subject" -to recipient@domain.xxx -attach "c:\path\to\attachment.pdf" -html

Sub send_emails()
Dim row As Long
Dim RetVal As Variant
Dim email As String
Dim command As String
Dim obj_fso As Object
Dim fileName As String
Dim subject As String
Dim body As String

RetVal = MsgBox("Are you sure you want to send all emails ?", vbQuestion + vbYesNo + vbDefaultButton2)
If (RetVal <> vbYes) Then
  Exit Sub
End If


subject = Trim(Cells(2, 2).Value)
subject = Replace(subject, Chr(34), "\" & Chr(34)) ' replaces any " character with a \" not to break the command string


row = 6

email = Trim(Range("B" & row).Value)

While (email <> "")
  body = Trim(Cells(4, 2).Value) ' the initial body text
  
  body = Replace(body, "%A%", Trim(Cells(row, 1))) ' replaces any %A% with the content of A column
  body = Replace(body, "%E%", Trim(Cells(row, 5))) ' replaces any %E%with the content of E column
  body = Replace(body, vbLf, "<br>") ' replaces any "new line" with the "<BR>" TAG. "<BR>" TAG work as "new line " in HTML e-mails
  body = Replace(body, Chr(34), "\" & Chr(34)) ' replaces any " character with \" to not to break the command string
  
  command = "blat.exe -Subject " & Chr(34) & subject & Chr(34) & " -body " & Chr(34) & body & Chr(34) & " -to " & email & " -html"
  fileName = ThisWorkbook.Path & "\" & Trim(Range("C" & row).Value) & Trim(Range("D" & row).Value)
  
  If (fileExists(fileName)) Then
     'Se il file allegato esiste aggiunge al command "BLAT" la parte di codice per allegarlo
     command = command & " -attach " & Chr(34) & fileName & Chr(34)
     Cells(row, 6).Value = "OK"
  Else
     'Altrimenti scrive alla colonna 6 un avviso !
     Cells(row, 6).Value = "ATTENTION: ATTACHMENT NOT FOUND!"
  End If
  RetVal = Shell(command, vbMinimizedFocus)
  
  Application.Wait (Now + TimeValue("0:00:1")) ' Pause for 1 second
  
  row = row + 1

  email = Trim(Range("B" & row).Value)
  fileName = ThisWorkbook.Path & "\" & Trim(Range("C" & row).Value) & Trim(Range("D" & row).Value)
Wend

row = row - 1
MsgBox "Program ended at row: " & row, vbInformation

End Sub

' Check if a file exists
Function fileExists(fileName As String) As Boolean

    Dim obj_fso As Object

    Set obj_fso = CreateObject("Scripting.FileSystemObject")
    fileExists = obj_fso.fileExists(fileName)
End Function

Automatizzare il tutto e possibili impieghi

Se hai dimestichezza con VBA puoi sfruttare questo sistema per automatizzare l’invio di alcune e-mail a intervalli specifici.

Ad esempio, mi è capitato in passato di realizzare in Access un programmino VBA che tutti i mesi, a inizio mese, esportasse le timbrature del rilevatore presenze, e le inviasse via mail in formato Zip all’agenzia di somministrazione interinale. Questo è un esempio di come questa soluzione può togliervi delle ore di lavoro ogni mese!

Vale la pena quindi a volte spenderci un po’ di tempo, per poterlo recuperare dopo.

In quelle situazioni in cui devi ripetere un invio, viene naturale automatizzarlo. La soluzione qui descritta è alla portata di tutti (Chi non ha Excel o Access in azienda?).

Se mastichi di VBA sarai autonomo, anche nella personalizzazione della soluzione, altrimenti sarà necessario un piccolo aiuto dal tuo reparto IT.

Aiutami a sostenere questo blog

Se vuoi puoi aiutarmi concretamente a sostenere questo blog. Puoi farlo con una donazione libera, con carta di credito o paypal. Te ne sarò riconoscente.