email PDF Attchments

  • Thread starter Thread starter colin
  • Start date Start date
C

colin

Hi,

I have a billing database that contains the following:

Accountnumber, emailaddress, invoicenumber,and a field containing a
hyperlink to the pdf invoice.

I would like to automatically email the invoices to the customer but can
find any code examples of how this is done.

Many thanks

Colin
 
Hi Dale,

many thanks for the reply.

Iam using 2007 and have already set up the convert to pdf addin and also set
up the following code in a form that loops through each invoice to create an
individual pdf where the file name is the invoice number:

Private Sub Form_Load()

Dim FileName As String

FileName = [Forms]![Invoice Limiter form]![MinOfInvoice] & ".pdf"

DoCmd.OutputTo acOutputReport, "V2MasterBillReport", acFormatPDF,
"W:\TITAN\Billing\WorldwideInvoices\" & FileName, False
DoCmd.SetWarnings False
End Sub

The above is triggered within a forms onload property and the data for the
form is based on the following

SELECT Min(InvoiceSummaryTable.Invoice) AS MinOfInvoice,
InvoiceSummaryTable.[Low Biller]
FROM InvoiceSummaryTable
GROUP BY InvoiceSummaryTable.[Low Biller], InvoiceSummaryTable.Printed
HAVING (((InvoiceSummaryTable.Printed) Is Null));

After each pdf is generated then the form is closed and the Invoice summary
table is updated to true. The form then reopens and the loop continues.

I have therefore created indivual pdfsfor each customer accountnumber and i
know the invoice number email address and the pathe of where the individual
pdfs are stored.

Iam looking now for a facility to loop through each customer and attach the
indivdual pdfs and email them to the correct email address.

Kind regards

Colin
 
you will need to create a reference to the Outlook object library...

I'm assuming you're able to create your own loops that are able to identify
which customer gets which reports.....

=====================
Dim objOutlook As Object
Dim objMail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objMail = objOutlook.CreateItem(olMailItem)

objMail.to = "CustomerA; CustomerB"
objMail.subject = "Here are some reports"

objMail.Attachments.Add "C:\Temp\CustomerReport1.pdf"

objMail.send

set objOutlook = nothing

Hope this helps
Rob
 
Hi Rob,

Thanks this looks promising many thanks:

My code is as follows:


Private Sub Form_Load()
Dim objOutlook As Object
Dim objMail As Outlook.MailItem
Dim email As String
Dim Path As String
Dim Invoice As String

email = [Forms]![Emailform]!
Path = [Forms]![Emailform]![Path]
Invoice = [Forms]![Emailform]![MinOfInvoice]

Set objOutlook = CreateObject("Outlook.application")
Set objMail = objOutlook.CreateItem(olMailItem)

objMail.to = "email"
objMail.subject = "Please find attached your latest invoice number" & Invoice

objMail.Attachments.Add Path

objMail.send

Iam having problems with the syntax of the following:

objMail.Attachments.Add Path (is this correct)

If I change the statement to:
objMail.Attachments.Add "W:/titan/Billing/WorldwideInvoices/F036039.pdf"

this works but I want to retrieve the path from a datafield.

If I use the latter I get an error advising Outlook does not recognise one
or more names.

Any Suggestions?

Kind regards

Colin

Set objOutlook = Nothing
End Sub
 
Have you checked the path works? Try

if Dir(path) = "" then
msgbox "This file does not exist"
exit sub
end if
 
Hi Rob

I have changed the code to:

Private Sub Form_Load()
Dim objOutlook As Object
Dim objMail As Outlook.MailItem
Dim email As String
Dim Path As String
Dim Invoice As String

email = [Forms]![Emailform]!
Path = [Forms]![Emailform]![Path]
Invoice = [Forms]![Emailform]![MinOfInvoice]

Set objOutlook = CreateObject("Outlook.application")
Set objMail = objOutlook.CreateItem(olMailItem)

objMail.to = "email"
objMail.subject = "Please find attached your latest invoice number" & Invoice

objMail.Attachments.Add Path
If Dir(Path) = "" Then
MsgBox "This file does not exist"
Exit Sub
End If
objMail.send

Set objOutlook = Nothing
End Sub

and I now only get the error message "outlook does not recognise one or more
names" (sorry new to coding did I insert your last snippet in the correct
place?)

Rgds

Colin
 
no worries....

Insert it before the line "objMail.Attachments.Add Path"

If you get that message box then there is something wrong with your "Path"
variable

HTH
Rob
 
Hi Rob,

I have changed that and it does not display the message box so I assume that
this now works : I think that originally I had the path set differently. I
now get the error
outlook does not recognise the name.

Rgds
Colin

The
 
what's the error number?

colin said:
Hi Rob,

I have changed that and it does not display the message box so I assume that
this now works : I think that originally I had the path set differently. I
now get the error
outlook does not recognise the name.

Rgds
Colin

The
Click to expand...
 
and can you tell me on what line of code the error occurs...?

When the error happens you should get an option to debug - then the problem
line will be highlighted in yellow
 
The error is on

objMail.send

Is it anything to do with an external application accessing outlook?
 
I've just recreated the error on my machine...

Outlook does not recognise the names of the people you're sending the e-mail
to...

You need to be exact with the name format...

HTH
Rob
 
Hi Rob

I have cracked it the problem was with the following:

objMail.to = "email"

Should have been

objMail.to = email

The emails arrived successfully wit the attachments

Your help has been great - Many thanks for your time and patience.

Best wishes
Colin
 
Back
Top