Hi,
i have an access invoice database. We have an online accounting software, which reads sended pdf invoice by mail automatically.
We want select a range invoice numbers ie: 20100058 - 20100060(access report) convert to PDF, attach these 3 invoices in pdf to mail and send to specific mail address.
Till here everything works fine.
here's my code:
1. i made a module "SepPDF":
Public strRptFilter As String
2. On invoice report event on open:
If Len(strRptFilter) > 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If
3. On invoice report event on close:
strRptFilter = vbNullString
4. Made a button:
Private Sub Command24_Click()
Dim rst As DAO.Recordset
Dim strInputStart As String, strInputEind As String, strInputLng As String, booNotWholeNumber As Boolean
booNotWholeNumber = False
strInputStart = InputBox("Start Factuur Nummer")
strInputEind = InputBox("Eind Factuur Nummer")
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [factuurid] FROM [tbl_vh_factuur] WHERE (tbl_vh_factuur.factuurid Between " & strInputStart & " And " & strInputEind & " ) ORDER BY [factuurid];", dbOpenSnapshot)
Do While Not rst.EOF
strRptFilter = "[factuurid] = " & rst![factuurid]
DoCmd.OutputTo acOutputReport, "rpt_vh_factuur_reeks_CL_PDFYUKI", acFormatPDF, "K:\01-Administratie\Database\Yuki_Upload\VH" & "\VH-" & rst![factuurid] & ".pdf"
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
strInputStart = vbNullString
strInputEind = vbNullString
'----------Mail declaraties----------------
Dim mess_body As String, StrFile As String, StrPath As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
' path here
StrPath = "K:\01-Administratie\Database\Yuki_Upload\VH\"
'----------Mail declaraties----------------
With MailOutLook
.BodyFormat = olFormatRichText
.To = "email address"
StrFile = Dir(StrPath & "*.*")
Do While Len(StrFile) > 0
.Attachments.Add StrPath & StrFile
.Subject = StrFile
StrFile = Dir
Loop
.Send
End With
MsgBox "Reports have been sent", vbOKOnly
End Sub
Problem is:
Do While Len(StrFile) > 0
.Attachments.Add StrPath & StrFile
.Subject = StrFile
line:.Subject = StrFile takes only the last invoice name, so in my sended mail i see subject 20100060.pdf.
I want see 3 names of attached files as subject. ie subject: 20100058, 20100059, 20100060.
How can i arange that? Tried allready so many things...
Thnx in advance