[Guida] Crea e invia file PDF da Excel con VBA

[Alexsandra]

Crea e invia file PDF da Excel con VBA

Si legge frequentemente in rete come Excel sia sempre più utilizzato per creare e gestire nuove funzionalità sia in ambito aziendale che personale utilizzando applicazioni create con il VBA. Una naturale evoluzione nell’uso di verticalizzazioni sviluppate in VBA è la conversione in PDF dell’elaborazione fatta dall’applicazione e inviarla via e-mail al destinatario, indipendentemente dai programmi di conversione installati nel sistema o dal client di posta elettronica che usiamo.

Il nostro obiettivo è di automatizzare la procedura direttamente dal nostro applicativo Excel interfacciandolo con opportuni programmi che accettano la programmazione VBA, permettendoci così di bypassare finestre di conferma o altre richieste permettendo un’esportazione in “silent” cioè senza chiederci niente. In pratica sto proponendo una soluzione che consiste in un pulsante presente nel foglio attivo di Excel che esegue la conversione in PDF e lo salva in una cartella e in un altro pulsante che invia il file PDF creato al destinatario. Per svolgere queste operazioni è necessaria la presenza nel sistema di una stampante virtuale, noi utilizzeremo Bullzip PDF Printer mentre per l’invio del file usiamo Blat, entrambe sono due utility Free che funzionano da riga di comando che possiamo personalizzare tramite codice VBA. Vediamo ora nel dettaglio come operare

Creare file Pdf

Durante l'installazione, che è molto semplice e intuitiva, il setup provvederà a dotarvi anche delle librerie Ghostscript, necessario per il corretto funzionamento di BullZip. Una volta completata l’installazione dovete aggiungere come componente aggiuntivo al vostro file Excel la libreria di Bullzip operando direttamente da VBA nel seguente modo:
Entrare nell'editor VBA (ALT+F11) e dal menu Strumenti – Riferimenti mettete la spunta su Bullzip nell'elenco a discesa che compare come da figura

Fig.1

N.B. : Se per vari motivi installate più volte Bullzip, rimuovete sempre le installazioni precedenti dal pannello di controllo “Installazione Applicazioni”. Eseguita questa operazione copiate il seguente codice e inseritelo in un nuovo modulo nel vostro progetto VBA

 Sub creaPdf()
Dim myobject As New Bullzip.PDFPrinterSettings
Dim SavePath As String, FileName As String, SheetName As String, ThisSheet As String

'Path dove salvare il pdf
Dim Unit, DirProg, DirPdf, MiaDir As String
Unit = Sheets("Info").[B1]
DirProg = Sheets("Info").[B2]
DirPdf = Sheets("Info").[B3]
MiaDir = DirProg & DirPdf

' se non ci sono le cartelle le creo
Dim dir1 As String
    dir1 = Dir(Unit & DirProg, 16)
    If Len(dir1) = 0 Then
    ChDir Unit
    MkDir Unit & DirProg
    End If
Dim dir2 As String
    dir2 = Dir(Unit & DirProg & DirPdf, 16)
    If Len(dir2) = 0 Then
    ChDir Unit
    MkDir Unit & DirProg & DirPdf
    End If
    
'assegno un nome al file pdf
MioDoc = ActiveSheet.Name
Set Nrdoc = Range("I3")
Set myDatadoc = Range("I5")

rifDoc = Nrdoc
Datadoc = myDatadoc
    
Dim gg, mm, aa As String
    gg = Day(myDatadoc)
    mm = Month(myDatadoc)
    aa = Year(myDatadoc)
  NomeFile = MioDoc & "_" & rifDoc & "_" & gg & "_" & mm & "_" & aa
       
If LCase(Right(NomeFile, 4)) <> ".pdf" Then NomeFile = NomeFile & ".pdf"

' setup parametri pdf
myobject.SetValue "output", Unit & MiaDir & NomeFile
myobject.SetValue "showsettings", "never"
myobject.SetValue "showPDF", "no"    ' remmare se vuoi visualizzare il PDF
myobject.WriteSettings (True)

'setto la stampante virtuale come predefinita
If InStr(ActivePrinter, "BullZip") = 0 Then
    Dim storeprinter$, PrinterChanged As Boolean
    PrinterChanged = True
    storeprinter = ActivePrinter
    ActivePrinter = GetFullNetworkPrinterName("Bullzip PDF Printer")
End If

'creo il file pdf
ActiveSheet.PrintOut

' ripristino la stampante predefinita di sistema
If PrinterChanged Then ActivePrinter = storeprinter
End Sub

Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String

Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
'strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":" ' per win in ENG
strTempPrinterName = strNetworkPrinterName & " su Ne" & Format(i, "00") & ":" ' per win in Ita

On Error Resume Next
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
GetFullNetworkPrinterName = strTempPrinterName
i = 100
End If
i = i + 1
Loop

Application.ActivePrinter = strCurrentPrinterName
End Function

Informazioni Aggiuntive

Nel codice sopra riportato ci sono dei riferimenti al foglio “Info”, che è stato usato come foglio di appoggio per memorizzare i dati per identificare il percorso dove salvare il file PDF e dove viene collocato il file Excel. Le celle del foglio info interessate sono:

B1 = Inserite l’unità in cui collocate il file Excel
B2 = Inserite il nome della cartella in cui inserite il file Excel
B3 = Inserite la sottocartella in cui vengono salvati i file PDF
B4 = Inserite L’SMTP del vostro client di posta predefinito

Fig. 2

Pertanto per un corretto funzionamento dovete inserire nelle celle sopra elencate i percorsi del vostro pc. Inoltre alcuni parametri per la creazione del file PDF vengono prelevati dal foglio DDT, dove viene compilato il documento di trasporto. Potete modificare il foglio come volete, farlo diventare una fattura o qualsiasi altro documento, a patto che le celle:

L3 = deve contenere il N° di documento
L5 = deve contenere la data del documento

Se per qualsiasi motivo nel vostro documento il Numero e la data del documento non si trovano nelle celle L3 e L5 dovete cambiare il codice segnato in rosso inserendo i vostri riferimenti di cella.

Scorrendo il codice proposto poco sopra si può notare che nella fase di conversione in PDF viene eseguito un controllo se le cartelle che abbiamo specificato nelle celle B1-B2-B3 del foglio Info sono presenti nel sistema, in caso contrario per evitare errori vengono create e successivamente viene assegnato il nome del file PDF composto dai valori presenti nelle celle L3 (N° del DDT) e L5 (data DDT). Per velocizzare il reperimento delle informazioni in fase di creazione del file PDF i valori ottenuti dal codice per costruire il nome del file completo di percorso verranno memorizzati sempre nel foglio Info più precisamente:

B8 = Percorso completo di nome del file PDF
B9 = Percorso dove risiede il programma (File Excel)

Questi ultimi dati saranno utili in seguito per inviare il file. A questo punto non ci resta che disegnare un pulsante e inserire come testo “Crea PDF” e associarlo alla macro creaPdf, il cui codice abbiamo visto poco sopra. Ora, cliccando sul pulsante Crea Pdf il foglio attivo viene convertito in PDF e salvato nel percorso specificato nelle celle B1-B2-B3 del foglio “Info” oltre al percorso completo di nome ed estensione del file creato, che è stato inserito nella cella B9


Invio del file via e-mail

Come abbiamo detto in premessa per inviare il file PDF via e-mail useremo Blat, assicuratevi di decomprimere i file di Blat nella stessa cartella in cui mettete il file Excel. Per usare Blat andremo a creare un file .bat tramite VBA a cui passeremo i comandi necessari per il corretto funzionamento. Per inviare il file PDF abbiamo bisogno dell’indirizzo del destinatario ed è stato inserito nell’intestazione del documento e precisamente nella cella G12. Per i vostri documenti potete dotare l’anagrafica degli indirizzi email dei vari clienti ed estrapolare il dato direttamente dall’anagrafica, oppure quando viene inserito il nominativo del cliente, sia che avvenga in modo manuale, oppure tramite codice VBA, recuperando i dati da un’anagrafica presente nel programma e andando ad inserirlo nella cella G12 del foglio principale DDT. Se per qualsiasi motivo dovete cambiare il riferimento di cella nel vostro foglio di lavoro dovete modificare anche il riferimento nel codice della macro che verrà esposto di seguito.

A questo punto disegniamo un pulsante sul foglio DDT nominandolo Spedisci Pdf e lo associamo alla seguente macro

Sub invia()
Form1.Show
End Sub

Form1 è una finestra di dialogo che verrà mostrata a video riassumendo i dati necessari per poter inviare la mail

Fig. 3

Al tasto "Annulla" di Form1 associate questo codice

Private Sub CommandButton1_Click()
Unload Me
End Sub

Mentre al tasto "Invia" della form associate questo codice

Private Sub CommandButton2_Click()
Sheets("email").Range("A1:A15").ClearContents
' setup variabili per creazione .bat
Sheets("email").[A1] = "CLS"                                                                     'pulisci lo schermo
Sheets("email").[A2] = "set email=" & Sheets("info").[B5]                  'set variabile mittente
Sheets("email").[A3] = "set server=-server " & Sheets("info").[B4]   'set smtp

Sheets("email").[A4] = "set dest=-to " & Sheets("DDT").[G12]           'set variabile destinatario
Sheets("email").[A5] = "set subject=-s " & Sheets("info").[B6]           'oggetto messaggio
Sheets("email").[A6] = "set attach=-attach " & Sheets("info").[B8]   'allegato pdf
Sheets("email").[A7] = "set path1=" & Sheets("info").[B9]

' comandi per blat
Sheets("email").[A8] = "@CD\"
Sheets("email").[A9] = "@CD %path1%"
Sheets("email").[A10] = "@blat -install %server% %email%"
Sheets("email").[A11] = "@blat testo.txt %dest% %subject% %attach%"

CreaBat
End Sub

Il foglio “email” è un altro foglio di appoggio che usiamo per “comporre” i vari dati che servono per la creazione del file bat da passare all’utility Blat. Nell’evento Activate della Form inseriamo questo codice

 Private Sub UserForm_Activate()
TextBox1 = Sheets("info").[B4] ' smtp
TextBox2 = Sheets("info").[B5] ' mittente
TextBox3 = Sheets("info").[B8] ' path file
TextBox4 = Sheets("DDT").[G12] ' destinatario
TextBox5 = Sheets("info").[B6] ' oggetto messaggio
End Sub

Che usiamo per precompilare le varie caselle di testo all’interno della form. I dati che vedete nella Form sono quelli che abbiamo inserito nel foglio “Info”, prestate attenzione al 1° box, dove c’è il vostro SMTP e inserite nel foglio “Info” la dicitura esatta dello stesso in base al vostro client di posta preferito.

Ora per creare il file bat usiamo questo codice

Private Sub CreaBat()
'creafile
Application.ScreenUpdating = False
Sheets("email").Activate

namefile = ActiveSheet.Name
numrig = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
mfilehandle = FreeFile

Dim Unit, DirProg As String
Unit = Sheets("Info").[B1]
DirProg = Sheets("Info").[B2]

pathbat = Unit & DirProg & namefile & ".bat"
a = " "
Open pathbat For Output As #mfilehandle
For ct1 = 1 To numrig
dato1 = ActiveSheet.Cells(ct1, 1)
Print #mfilehandle, dato1
Next ct1
Close #mfilehandle
Sheets("DDT").Activate
Shell ("C:\Preventivi\email.bat"), 1
Form1.Hide
End Sub

A questo punto possiamo inviare il file ma mancherebbe una presentazione del messaggio. Per farla possiamo usare un file txt in cui inseriamo del testo generico per avvisare il destinatario del contenuto dell’allegato. Possiamo salvare in un file di testo il seguente testo:

Invio Allegati:
Con la presente trasmetto documentazione relativa ai prodotti richiesti
Saluti
Alex

Bisogna prestare attenzione che il file (.txt) sia presente nella cartella in cui collocate il programma. A questo punto abbiamo tutti i dati necessari per trasmettere il nostro file PDF, basta solo cliccare sul pulsante “Invia” e vengono trascritti tutti i dati necessari in un foglio [“email”] che verrà convertito in bat e mandato in esecuzione dal comando Shell di Vba.

Fig. 4

Il processo è terminato e il file inviato all’indirizzo indicato nel foglio di lavoro.



Commenti