Invoicing by mail or print

[Boomkweker]

I told in my introducing on this forum that i'm looking for more functionality in my own made DB. What i really would like is this but i dont know if it is possible.

In my DB i make Invoices every first of the month. I do the numbering of the invoices by hand and when i click the button invoices it asks "from what number" and the DB reproduce a report with a page for each invoice.

What i really want would be automated reproduction of the invoices and attach these as pdf to a mail when a proper mailadres is available and when not, print the invoice. After that save the invoice with the name of the invoicenumber. Or perhaps the other way around, saving to pdf and attach this to a mail. Resuming i want to click a button and the DB reproduce the invoices for that month and make a decision wether to attach or print.

It would be nice when the numbering of the invoices also be automated, but not of all importance.

I know that i'm asking a lot but first of all knowing if it is possible is inportant to me.

 

[pbaldy]

You can start here:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

and the associated looping code link. Within the loop you can test the email field and send/print as appropriate. If you want to save as PDF either way, I use OutputTo.

 

[Boomkweker]

Paul,

I tried to change a code I was allready using for a newsletter. Actueally it's a code from Jephens. I try to make this do my invoicing. Right now it opens the complete report and(offcourse) it didn't attach them allready(this will be the next step). Is it possible to filter the specific records out of the report? The query "digitaalfactureren" which is set as "maillist"in the code also holds the Ivoice_Id on which I want to filter the report.

Public Function SendInvoice()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim strPptName As String


Set fso = New FileSystemObject
strRptName = "DigitaleFactuur"
' First, we need to know the subject.

' We can??t very well be sending around blank messages...

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")

' If there??s no subject, call it a day.

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If

' Now we need to put something in our letter...

'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
"We Need A Body!")

' If there??s nothing to say, call it a day.

'If BodyFile$ = "" Then
'MsgBox "No body, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain??t Got No-Body!"
'Exit Function
'End If

' Check to make sure the file exists...
'If fso.FileExists(BodyFile$) = False Then
'MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain??t Got No-Body!"
'Exit Function
'End If

' Since we got a file, we can open it up.
'Set MyBody = fspenTextFile(BodyFile, ForReading, False, TristateUseDefault)

' and read it into a variable.
'MyBodyText = MyBody.ReadAll

' and close the file.
'MyBody.Close

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Set up the database and query connections

Set db = CurrentDb()

Set MailList = db.OpenRecordset("DigitaalFactureren")

' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.

Do Until MailList.EOF

' This creates the e-mail
DoCmd.OpenReport strRptName, acViewPreview

Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it

MyMail.To = MailList("Factuurmailadres")

'This gives it a subject
MyMail.Subject = Subjectline$

'This gives it the body
MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"

'This sends it!

MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

'MyMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

Thanks for your help so far and hopefully you can help me out further.
Thanks anyway,

Jaap

 

[pbaldy]

Did you get this sorted out? If you mean the report opened here:

DoCmd.OpenReport strRptName, acViewPreview

you could use this method:

http://www.baldyweb.com/wherecondition.htm

but referring to the recordset rather than a form.

 

[gemma-the-husky]

Generically, I would tend to do this in this manner

1. by using a local table loaded with the orders/jobs I want to invoice. - using an append query of some manner
2. Allocate sequential numbers in this table, as a process
3. print invoices by joining this table to the real orders
4. for those customers wanting email invoices, raise an email
5. for those wanted printed invoices, print the invoices
6. I would not ask for email text. I would have a control table that carries the message subject and body. If blank, the code itself would carry a default
7. once the invoice process completes, update the "real database" with the invoice numbers, dates, etc, and any other information temporarily stored in the working table
8. Often I put the emails in the draft folder (mymail.save) tso that the user can review before sending
9. for the emails, I would have a query that extracts the different customers from the temporary table, and process each customer separately - so that the customer gets a single email with all his invoices.

I find this just makes it really easy to have a safe and re-runnable procedure (in the event of any errors)

 

[Boomkweker]

Paul,

When I implement the code you suggested to open the report I get
'invalid use of keyword Me". What am I doing wrong?

Jaap

 

[pbaldy]

Doesn't sound like you changed it to refer to the recordset. You're also going to have to output the report to a file to be able to attach it using the method you are.

 

[Boomkweker]

Dave the more I look at your suggestions the more I like it. I think however that I can't come to the solution all by myself. Let's say I have a table which holds: Customer_ID, Invoicemonth, Invoice_date and Invoice_number. The two last columns are empty till the day I want to print/email the invoices. Would it then be possible to get out the records with the empty columns and number them each with a number higher then the highest till then and fill in the date of that day and after that update them to the originel table.

Thanks in advance,
Jaap

 

[Boomkweker]

I'm still working on this issue. What I got working right now is the following. It stores the split up report in the right folder as .pdf. What I want right now is to print or mail it I want the code to decide this based on if there is a mailadress or not. Wouls this be possible(get the attachment back from the folder). There is allready a part with comments. Is that useable or not?

Public Function SendInvoice()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim fso As FileSystemObject
Dim strRptName As String


Set fso = New FileSystemObject
strRptName = "DigitaleFactuur"

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If


Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("DigitaalFactureren")



Do Until MailList.EOF


DoCmd.OpenReport "DigitaleFactuur", acViewPreview, , "Factuurnummer = " & MailList!Factuurnummer
DoCmd.OutputTo acOutputReport, strDocname, acFormatPDF, "D:\AJ de Lange\Jaaroverzichten\DigitaleFacturen\" & MailList!Factuurnummer & ".pdf"
DoCmd.Close acReport, strRptName, acSaveNo

' Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it

' MyMail.To = MailList("Factuurmailadres")

'This gives it a subject
' MyMail.Subject = Subjectline$

'This gives it the body
'MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

'MyMail.Attachments.Add "D:\AJ de Lange\Jaaroverzichten\DigitaleFacturen\" & MailList!Factuurnummer & ".pdf", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"

'This sends it!

'MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

'MyMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[Boomkweker]

I've uncomment some lines now. It's sending the mails with the correct attachments to the correct mailadresses. Now I stil want to build in the decision to mail it or to print it, based on if there is a mailadress available or not. Is this possible? My code now it looks like this

Public Function SendInvoice()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim fso As FileSystemObject
Dim strRptName As String


Set fso = New FileSystemObject
strRptName = "DigitaleFactuur"

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If


Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("DigitaalFactureren")



Do Until MailList.EOF


DoCmd.OpenReport "DigitaleFactuur", acViewPreview, , "Factuurnummer = " & MailList!Factuurnummer
DoCmd.OutputTo acOutputReport, strDocname, acFormatPDF, "D:\AJ de Lange\Jaaroverzichten\DigitaleFacturen\" & MailList!Factuurnummer & ".pdf"
DoCmd.Close acReport, strRptName, acSaveNo
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it

MyMail.To = MailList("Factuurmailadres")

'This gives it a subject
MyMail.Subject = Subjectline$

'This gives it the body
'MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

MyMail.Attachments.Add "D:\AJ de Lange\Jaaroverzichten\DigitaleFacturen\" & MailList!Factuurnummer & ".pdf", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"

'This sends it!

MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

'MyMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[pbaldy]

Along the lines of:

If Len(MailList("Factuurmailadres") & vbNullString) = 0 Then
  DoCmd.OpenReport "DigitaleFactuur", , , "Factuurnummer = " & MailList!Factuurnummer
Else
  'your code to send email
End If

 

[Boomkweker]

Paul thanks for your reply,

It's working perfect but two things. Is it possible to choose a printer and would it be possible to store the report by docmd.output anyway no matter if it is printed or send by mail.

Thanks in advance
Jaap

 

[Boomkweker]

Paul,

For one or another reason it's working like a charm now. Don't now why. But still I want to select a printer. Would this be possible?

Thanks a lot allready,
Jaap

 

[pbaldy]

Sorry, missed the last question. You want the user to select a printer at run time, or you want this report to always go to a specific printer? The first I'm not sure of, the second can be set in Page Setup.

 

[Boomkweker]

Oke Paul,

I´m allmost there. Just one problem. I´m testing with two customers/invoices. When nr1 mailadress is empty I get two same reports. When nr2 is empty it´s working allright, could this be the place of the End If. Myself am just reading and can't test it so that's why i'm asking instead of trying. My code is now:

Public Function SendInvoice()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim fso As FileSystemObject
Dim strRptName As String


Set fso = New FileSystemObject
strRptName = "DigitaleFactuur"

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If


Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("DigitaalFactureren")



Do Until MailList.EOF


DoCmd.OpenReport "DigitaleFactuur", acViewPreview, , "Factuurnummer = " & MailList!Factuurnummer
DoCmd.OutputTo acOutputReport, strDocName, acFormatPDF, "D:\AJ de Lange\Jaaroverzichten\DigitaleFacturen\" & MailList!Factuurnummer & ".pdf"
If Len(MailList("Factuurmailadres") & vbNullString) = 0 Then
DoCmd.OpenReport "DigitaleFactuur", , , "Factuurnummer = " & MailList!Factuurnummer
Else
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it

MyMail.To = MailList("Factuurmailadres")

'This gives it a subject
MyMail.Subject = Subjectline$

'This gives it the body
'MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

MyMail.Attachments.Add "D:\AJ de Lange\Jaaroverzichten\DigitaleFacturen\" & MailList!Factuurnummer & ".pdf", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"

'This sends it!

MyMail.Send
DoCmd.Close acReport, strRptName, acSaveNo
End If

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

'MyMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[pbaldy]

Well, you open the report before the If/Then test but only close it when there is an email address. That close line should be below the End If so that it closes either way.

 

[Boomkweker]

Oke Paul,

Everything is working super right now. There is one thing on my mind. Would it be possible to ask for a time period for query "DigitaalFactureren" let's say the code asks for Which time period? Answer will be for example march 2014. And the query displays only the records of march 2014. Is this code or is it possible in the query itself. I've allready tried the last option but it's giving syntax "to few parameters. expected number :1 "

Thanks for all your help,
Jaap

 

[pbaldy]

You're running into this is sounds like:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

One alternative that often works is wrapping the form reference in the query in the Eval() function.

 

[gemma-the-husky]

Sorry, missed the last question. You want the user to select a printer at run time, or you want this report to always go to a specific printer? The first I'm not sure of, the second can be set in Page Setup.

paul

out of interest, the easiest way to select a printer at runtime, is to set the default printer (application.printer) temporarily to the one you want, then reset it to the original default.

I use this sort of logic

sub setprinter(requiredprinter as string)
Dim ptr as printer

      'loop all printers until you find the right one
      '(not sure if you can find it directly)
      For Each ptr In Application.Printers
            If ptr.DeviceName = requiredprinter Then
                 Set Application.Printer = ptr
                 exit sub
            End If
      Next
end sub

sub clearprinter()
        'reassign the printer to the default
        Set Application.Printer = Nothing
End sub

 

[Boomkweker]

I want to thank you Paul and dave for helping me out. I've got a working module wich I will post when I cleaned up the code. I got a workaround for the parameter issue and for me It's working perfect. Thanks again and perhaps till some other time.

Jaap