and they love us!

We are your Microsoft Access Database Experts

AKA: TopDesk Applications, Since 1994

Quick Code Pros
Made in America

Email an invoice from Access database and link Invoice PDF to Invoice form

(Free demo customer invoice database template below! Includes free United States Zip Codes)

Let’s say you want to send an invoice to a customer. The invoice is a report in your Access database (invoice). You want to save a “hard copy” of the invoice in your “Invoices” file located in a shared DropBox folder, so that the rest of the accounting department can see the invoice if needed. (this will work regardless of where you want your hard copy stored, just change the path). You also need the invoice to be titled something recognizable at first glance, i.e. “ABC Company Invoice 1341 12122016”, (company name, invoice number and the date*) you want a link to it in the Customers’ Invoice Form and you want it saved as a PDF! Not asking too much.

Be sure to have an Invoices folder set to where you want to store the invoices (Currently set to "C:\Users\Topdesk\dropbox\invoices\") Change as necessary.
Be sure you have a bound field on your invoice form titled “invoicelocation” and its data type is set to “Hyperlink” in the table

*Note, in order to have the invoice saved with the invoice date, there is an unbound field on the invoice form called invoicedate2 which removes the slashes from the date to avoid issues with saving.

Here. IS. The. Code:

‘Save invoice to file, email to customer and link hard copy to invoice form (run from a button’s event ‘procedure)

Private Sub Command194_Click()

'First we handle the invoice and send it to our specified file.
    Dim strpath As String
    Dim stDocName As String
    Dim mypath As String
    strpath = "C:\Users\Topdesk\dropbox\invoices\"   
'(modify as you see fit)
    mypath = strpath & Me.CompanyName & " Invoice " & Me.InvoiceNumber & " " & me.invoicedate2 & ".pdf"  
'This will create a file called something like         "Topdesk Invoice 2067 12142016"

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    stDocName = "invoice"
    DoCmd.OutputTo acReport, stDocName, acFormatPDF, mypath, False

'Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
           'Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg

        .To = Me.EmailAddress
'from the Invoice form
        .Subject = "Invoice Attached"

This section creates a signature in your email      

        .HTMLBody = .HTMLBody & "<font face=""arial"" size=""3"" color=""black""><b>TopDesk Applications</b></font><BR>"
        .HTMLBody = .HTMLBody & "<font face=""arial"" size=""2"" color=""blue"">"
        .HTMLBody = .HTMLBody & "<font face=""arial"" size=""2"" color=""black"">14445 15th St. S..</font><BR>"
        .HTMLBody = .HTMLBody & "<font face=""arial"" size=""2"" color=""black"">Afton, MN  55001</font><BR>"
        .HTMLBody = .HTMLBody & "<font face=""arial"" size=""2"" color=""black"">(651) 206.5525 office</font><BR>"
        .HTMLBody = .HTMLBody & "<font face=""arial"" size=""2"" color=""blue""><b><BR></b></font><BR>"
        .HTMLBody = .HTMLBody & ""
        .HTMLBody = .HTMLBody & "<font face=""Arial"" size=""2"" color=""green""><u></u></font><BR>"
        .HTMLBody = .HTMLBody & "<font face=""Arial"" size=""2"" color=""blue"">"

        .Attachments.Add mypath

'(change to "display" if you want to review the email message first)
  [Forms]!invoice!invoicelocation = "Invoice" & "#" & mypath
'links PDF back to the Invoice form
    End With

    Set objOutlook = Nothing

End Sub

Free Customer Invoice Database demo template HERE:

    Go to next tutorial (Create Directory and Folders)