email PDF Attchments

[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

 

[Dale Fye]

What version of Access are you using?

The first thing I would do is create a report based on your invoice data.
Then you can use the SendObject method to send the report.

If you are using Access 2007, you can download the free Save as PDF Office
Add-in at:
http://www.microsoft.com/downloads/...3c-6d89-4f15-991b-63b07ba5f2e5&displaylang=en

If you are using an earlier version of Access, check out Steve Leban's
ReportToPDF utility.

 

[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

 

[Rob Wills]

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

 

[colin]

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

 

[Rob Wills]

Have you checked the path works? Try

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

 

[colin]

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

 

[Rob Wills]

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

 

[colin]

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

 

[Rob Wills]

what's the error number?

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

 

[colin]

Runtime error'-2147467259 (80004005)'

what's the error number?

 

[Rob Wills]

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

 

[colin]

The error is on

objMail.send

Is it anything to do with an external application accessing outlook?

 

[Rob Wills]

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

 

[colin]

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