Add attachement to email with VBA

Question

I would like to be able to compose an email and attach a pdf file to it. This pdf file is also generated by the same VBA code.

Right now, I am able to open the email application and fill in the fields that interest me, but I don't know how to attach the PDF file.

This is the code I have so far:

Sub ToPDFandSend()

    Dim filesave As FileDialog
    Dim rng As Range
    Dim PDFfileName As String
    Dim formatIndex As Long, i As Long
    
    'Variables to send email
    Dim thund As String
    Dim email As String
    Dim subj As String
    Dim body As String

       
    Set filesave = Application.FileDialog(msoFileDialogSaveAs)
    
    
    Select Case Sheets("Factura").Range("M1").Value
    Case 1: t = 1: f = 1 'page 1
    Case Else: f = 1: t = 2 'pages 1-2
End Select
    
    With ThisWorkbook.Worksheets("Factura")
        Set rng = .Range("A1:J97")
        Data = Format(Range("A13"), "YYYYMMDD")
        PDFfileName = .Range("H1").Value & " " & Range("A17").Value & " " & Data
    End With
    
    With filesave
        .Title = "Save as PDF"
        .InitialFileName = PDFfileName
        formatIndex = 0
        For i = 1 To .Filters.Count
            If InStr(1, .Filters(i).Extensions, "pdf", vbTextCompare) > 0 Then formatIndex = i
        Next
        If formatIndex > 0 Then .FilterIndex = formatIndex
        If .Show Then
            rng.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=.SelectedItems(1), _
                OpenAfterPublish:=False, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=True, _
                from:=f, _
                to:=t, _
                Quality:=xlQualityStandard
        End If
    
    End With
    
    'Generar correu electrònic
    With ThisWorkbook.Worksheets("Factura")
      
    Data = Format(Range("A13"), "YYYY/MM/DD")
    Mes = MonthName(Month(Data))
    body = "Benvolguts," & vbNewLine & "Us faig arribar la factura corresponent al mes de " & Mes & "." & vbNewLine & "Atentament,"
    email = ""
    subj = "Factura Nº" & " " & .Range("H1").Value
    
    thund = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
            " -compose " & """" & _
            "to='" & email & "'," & _
            "subject='" & subj & "'," & _
            "body='" & body & "'" & """"

    Call Shell(thund, vbNormalFocus)
    End With
End Sub

I would like the code to attached the created PDF file to the composed email. Can anyone help?

Answer 1

According to the documentation, you can add an attachment with the attachment= keyword.

The filename of the attachment is the result of the file dialog (.SelectedItems(1)), however, your code that creates the mail command is outside of the scope (the With-statement), so .SelectedItems(1) is no longer available. You could move the whole code into the With-Block. Or simply save the filename into a variable.

Additionally, you need make up your mind what should happen when the user left the file dialog without specifying a filename - do you want to send the mail without attachment or do you want to leave the routine?

Untested, as I don't have Thunderbird available:

Dim exportFilename As String
With filesave
    .Title = "Save as PDF"
    .InitialFileName = PDFfileName
    formatIndex = 0
    For i = 1 To .Filters.Count
        If InStr(1, .Filters(i).Extensions, "pdf", vbTextCompare) > 0 Then formatIndex = i
    Next
    If formatIndex > 0 Then .FilterIndex = formatIndex
    If .Show Then
        exportFilename = .SelectedItems(1)
        rng.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=exportFilename, _
            OpenAfterPublish:=False, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=True, _
            from:=f, _
            to:=t, _
            Quality:=xlQualityStandard
    Else
        '  Exit Sub   '<-- Activate this statement if you don't want to send a mail without attachment.
    End If
End With

(...)

thund = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
        " -compose " & """" & _
        "to='" & email & "'," & _
        "subject='" & subj & "'," & _
        "body='" & body & "'" & _
        IIf(exportFilename = "", "", ",attachment='" & exportFilename & "'") & _
        """"