send report from access to email address access looks up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My database generates Invoices
I want to print that invoice to Adobe & email to customer
Customer email is in database, I want to be able to press a button that
creates the invoice and attaches to an email addressed to the contact in
database without having to lookup the address manually.
 
This should get you started. The code takes two reports - one a
remittance advice listing all invoices & invoice balances and another
which is the actuall invoice - outputs them as snapshots to a designated
location and then grabs them and attaches them to a new mail message. At
the end, the new email is displayed to allow me to add specific
comments. Since the reports are saved as Snapshots, I include a link
that allow my clients to download Microsoft Snapshot Viewer if they
don't have it. I don't have personal experience outputting reports as
..pdf files, but you'll find plenty of experience around here.

Public Sub emailInvoice(strDescription As String, dblInvoiceBatchNumber,
strAccountType As String, lngAccountID As Long)

strTargetFolder = getSystemValue("REPORTDIR")

Select Case strAccountType
Case "Client"
DoCmd.Echo False
DoCmd.OpenReport "rptInvoice", acViewPreview, ,
"[dblInvoiceBatchNumber] = " & dblInvoiceBatchNumber, , "3," &
strAccountType & "," & lngAccountID & ",rptRs_invoiceClientV1"
DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatSNP,
strTargetFolder & "\rptInvoice.snp"
DoCmd.Close acReport, "rptInvoice"
DoCmd.Echo True
DoCmd.Echo False
DoCmd.OpenReport "rptInvoiceRemittanceAdvice",
acViewPreview, , "[lngClientID] = " & lngAccountID & " AND [expBalance]
<> 0", , "3," & strAccountType & "," & lngAccountID &
",rptRs_invoiceClientV1"
DoCmd.OutputTo acOutputReport,
"rptInvoiceRemittanceAdvice", acFormatSNP, strTargetFolder &
"\rptInvoiceRemittanceAdvice.snp"
DoCmd.Close acReport, "rptInvoiceRemittanceAdvice"
DoCmd.Echo True
Case "Master Account"
DoCmd.Echo False
DoCmd.OpenReport "rptInvoice", acViewPreview, ,
"[dblInvoiceBatchNumber] = " & dblInvoiceBatchNumber, , "3," &
strAccountType & "," & lngAccountID & ",rptRs_invoiceMasterAccountV1"
DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatSNP,
strTargetFolder & "\rptInvoice.snp"
DoCmd.Close acReport, "rptInvoice"
DoCmd.Echo True
DoCmd.Echo False
DoCmd.OpenReport "rptInvoiceRemittanceAdvice",
acViewPreview, , "[lngMasterAccountID] = " & lngAccountID & " AND
[expBalance] <> 0", , "3," & strAccountType & "," & lngAccountID &
",rptRs_invoiceMasterAccountV1"
DoCmd.OutputTo acOutputReport,
"rptInvoiceRemittanceAdvice", acFormatSNP, strTargetFolder &
"\rptInvoiceRemittanceAdvice.snp"
DoCmd.Close acReport, "rptInvoiceRemittanceAdvice"
DoCmd.Echo True
End Select

strMsgText = ""
strMsgText = strMsgText & "Microsoft Snapshot Viewer is required to
view this file. "
strMsgText = strMsgText & "If the invoice does not open, MS
Snapshot viewer may need to be installed "
strMsgText = strMsgText & "on your PC." & Chr(13) & Chr(13)
strMsgText = strMsgText &
"http://www.gatewayorlando.com/content/downloads.asp?dwnld=MSSnap"
Select Case strAccountType
Case "Client"
strEmailAddress = DLookup("txtClientEmailAddress",
"tblClients", "[lngClientId] = " & lngAccountID)
Case "Master Account"
strEmailAddress =
DLookup("txtMasterAccountContactEmailAddress", "tblMasterAccounts",
"[lngMasterAccountId] = " & lngAccountID)
Case Else
strEmailAddress = ""
End Select

If IsNull(strEmailAddress) = True Then strEmailAddress = ""

Dim objOutlook As Outlook.Application
Dim nms As Outlook.NameSpace
Dim newMail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set nms = objOutlook.GetNamespace("MAPI")
Set newMail = objOutlook.CreateItem(olMailItem)

newMail.Body = strMsgText
newMail.To = strEmailAddress
Set newMailAttachments = newMail.Attachments
file1 = strTargetFolder & "\rptInvoice.snp"
file2 = strTargetFolder & "\rptInvoiceRemittanceAdvice.snp"
newMailAttachments.Add file1, olByValue, 1, "Invoice"
newMailAttachments.Add file2, olByValue, 1, "Remittance Advice"
newMail.Subject = "INVOICE: " & strDescription & " (" & Now() & ")"
newMail.Display

End Sub
 
Back
Top