Results 1 to 9 of 9
  1. #1
    Rzadziu is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2012
    Posts
    28

    Send email with PDF attachment

    Hello,

    Im trying to create seperete PDF files for groups of recorts based on report and then send them by Email.
    For a now i have a code for creating one file and send it.


    Can you help me to make a loop to create seperete PDF with group of records??
    My report name is Cert_archer and i would like to grop this by filed on this report TrInf1.
    Report is based on Query "qryOrder"


    My code
    Code:
    Sub cmdLogin_Click()
    Dim objol As Object
    Dim objmail As Object
    Dim objFolder As Object
    Dim fso As Object
    Dim fsFolder As Object
    Dim fsFile As Object
    Dim FileName As String
    Dim FilePatch As String
    Dim oOutlook As Outlook.Application
    '---------------------------------------------------------------------------------------//
        
          
        
        
        '// Create various needed objects.  I happen to use late-binding.                   //
        Set fso = CreateObject("Scripting.FileSystemObject")
        ObjstartFolder = "C:\Temp\Access"
        Set fsFolder = fso.GetFolder(ObjstartFolder)
        
        
        '// Create .PDF files//
        FileName = Me.Tekst60
        FilePatch = "C:\Temp\Access\" & FileName & ".pdf"
        DoCmd.OutputTo acOutputReport, "cert_archer", acFormatPDF, FilePatch
        
        
         '//Create Email with attachment//
        Set objol = CreateObject("Outlook.Application")
        Set objmail = objol.CreateItem(0) '(olMailItem)
            If oOutlook Is Nothing Then
            Set oOutlook = New Outlook.Application
        End If
    
        
        
        With objmail
            .To = "Me@Wherever.com"
            .Subject = Me.Tekst60
            .Body = "Here's a test"
            .NoAging = True
            
            '// Using the file system object, return/add all the PDF files in the picked  //
            '// folder.                                                                     //
            For Each fsFile In fsFolder.Files
                If fsFile.Name Like "*.pdf" Then
                    .Attachments.Add "C:\Temp\Access" & "\" & fsFile.Name
                End If
            Next
            
            .Display
            
        End With
        
    errhndl:
        Set objFolder = Nothing
        Set fso = Nothing
        Set fsFolder = Nothing
        Set objol = Nothing
        Set objmail = Nothing
    
    
    
    
    End Sub

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It seems like what is missing is looping through records in order to generate various reports. I would open a recordset in DAO. You could even use a form to open and Filter a recordset. A DAO recordset can clone a form's recordset.

    Once you have your DAO recordset open, loop through the records and use one or more of the fields to assign a unique name to your PDF file. The PK can serve as a Filter for opening/generating the report.

  3. #3
    Rzadziu is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2012
    Posts
    28
    Thanks ItsMe for your advices.
    Unfortunately i am early beginner of VBA and have a problem with this loop.
    Can you help me with coding??

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I was attempting to illustrate two approaches using DAO. One approach may be easier than the other if having a User open a form and apply filters to the form's recordset.For instance, a User opens a form and uses the Filter tool to limit the records visible. Let's say a user opens a form and all 100 records are available. After the user is finished working with the Filter tool, there might be only 14 records available. If you build some VBA behind the form, maybe a Command Button Click Event, you can Clone the Form's filtered recordset (14 records).

    You can create a clone with the following code.
    Code:
    dim rs as dao.recordset
    set rs = Me.recordsetclone
    Now you can loop through all of the records within your recordset named rs.
    Code:
    while rs.eof = false
    msgbox rs![NameOfColumn]
    rs.movenext
    wend
    Maybe you can try the above using a form. Make sure that you are testing with a small recordset (< 14 records). Otherwise, you will be clicking OK many times as your code loops through all of the records. Also, you will want to replace NameOfColumn with a valid name of a field in your table. Maybe use the name of a bound control.

  5. #5
    Rzadziu is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2012
    Posts
    28
    ItsMe, do you have a possibility to implement this loop in code i have posted.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Well, I believe that is what I am doing. However, I need to understand what direction you want to go. It should be determined what recordset you plan on looping through.

    Perhaps you can explain what "groups of recorts based on report" is. Are you going to require a recordset to define this group?

  7. #7
    Rzadziu is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2012
    Posts
    28
    What i would like to do is to create seperate PDF file for groupe of records (normaly around 20 records) and send them by email.
    Code i have post is colecting all PDF files from directory and attach them to email.
    Unfortunately i cant make a loop to create seperate files as i wolud like to have.
    I think it suposed to be a loop on line below base on field Trinf1 which is a part of report Cert_archer
    Code:
    DoCmd.OutputTo acOutputReport, "cert_archer", acFormatPDF, FilePatch

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Why don't we try this. Make a copy of the query that your report is based on. You can right click copy the Query directly from the Navigation Pane and then paste a copy in the navigation pane. Now, open that query in design view.

    With your new query, add criteria that will produce the 'group' you desire. Save that query. Switch to SQL view of the query. Copy the SQL and paste it here so we can see it.

    .
    Click image for larger version. 

Name:	ViewSQL_Ribbon.jpg 
Views:	17 
Size:	35.6 KB 
ID:	26062

  9. #9
    Rzadziu is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2012
    Posts
    28
    Hello ItsMe,

    Thats the SQL from the Query i use
    Code:
    SELECT dbo_Rsv.OrdNo, dbo_Rsv.SerNo, dbo_ShpBal.Inf, dbo_Rsv.ProdNo, dbo_Rsv.ShpNo, dbo_Ord.Nm, dbo_Ord.Ad1, dbo_OrdLn.TrInf1, dbo_FreeInf2.Gr12, dbo_FreeInf2.Txt2, dbo_OrdLn.Descr, dbo_Rsv.TrInf2, dbo_Ord.PNo, dbo_Ord.YrRef, dbo_Ord.Label, dbo_ShpBal.DurDt AS EXP, dbo_ShpBal.Dt1 AS Cure, DateSerial(Left(IIf([EXP]=0,"19000101",[EXP]),4),Mid(IIf([EXP]=0,"19000101",[EXP]),5,2),Right(IIf([EXP]=0,"19000101",[EXP]),2)) AS EXPfix, DateSerial(Left(IIf([Cure]=0,"19000101",[Cure]),4),Mid(IIf([Cure]=0,"19000101",[Cure]),5,2),Right(IIf([Cure]=0,"19000101",[Cure]),2)) AS Curefix, dbo_OrdLn.FinDt AS Ferdigmeldtdato, Format([dbo_OrdLn]![TrInf1],"000000") AS Sorting, dbo_FreeInf2.Val28, IIf([Val27]=0,"",[Val27]) & IIf([Val28]>0,"-") & IIf([Val28]=0,"",[Val28]) AS Hard, dbo_OrdLn.LnNo, [dbo_Rsv]![OrdNo] & [lnno] AS [key], dbo_Ord.PArea, dbo_OrdLn.TrInf1 AS item, dbo_FreeInf2.Val1, dbo_FreeInf2.Val2, dbo_FreeInf2.Val3, dbo_FreeInf2.Val4, dbo_FreeInf2.Val5, dbo_FreeInf2.Val6, dbo_FreeInf2.Val7, dbo_FreeInf2.Val8, dbo_FreeInf2.Val9, dbo_FreeInf2.Val23, dbo_FreeInf2.Val22, dbo_FreeInf2.Val24, dbo_FreeInf2.Val25, dbo_FreeInf2.Val26, dbo_FreeInf2.Val27, dbo_FreeInf2.Val12, dbo_FreeInf2.Val11, dbo_FreeInf2.Val10, dbo_FreeInf2.PK, dbo_FreeInf2.Gr2, dbo_FreeInf2.Val17, dbo_FreeInf2.Val13
    FROM dbo_OrdLn INNER JOIN (dbo_Ord INNER JOIN (dbo_ShpBal INNER JOIN (dbo_Rsv INNER JOIN dbo_FreeInf2 ON (dbo_Rsv.ProdNo = dbo_FreeInf2.ProdNo) AND (dbo_Rsv.ShpNo = dbo_FreeInf2.ShpNo)) ON (dbo_ShpBal.ShpNo = dbo_Rsv.ShpNo) AND (dbo_ShpBal.ProdNo = dbo_Rsv.ProdNo)) ON dbo_Ord.OrdNo = dbo_Rsv.OrdNo) ON (dbo_OrdLn.OrdNo = dbo_Ord.OrdNo) AND (dbo_OrdLn.LnNo = dbo_Rsv.OrdLnNo) AND (dbo_OrdLn.ProdNo = dbo_ShpBal.ProdNo)
    GROUP BY dbo_Rsv.OrdNo, dbo_Rsv.SerNo, dbo_ShpBal.Inf, dbo_Rsv.ProdNo, dbo_Rsv.ShpNo, dbo_Ord.Nm, dbo_Ord.Ad1, dbo_FreeInf2.Gr12, dbo_FreeInf2.Txt2, dbo_OrdLn.Descr, dbo_Rsv.TrInf2, dbo_Ord.PNo, dbo_Ord.YrRef, dbo_Ord.Label, dbo_ShpBal.DurDt, dbo_ShpBal.Dt1, dbo_OrdLn.FinDt, Format([dbo_OrdLn]![TrInf1],"000000"), dbo_FreeInf2.Val28, dbo_OrdLn.LnNo, [dbo_Rsv]![OrdNo] & [lnno], dbo_Ord.PArea, dbo_OrdLn.TrInf1, dbo_FreeInf2.Val1, dbo_FreeInf2.Val2, dbo_FreeInf2.Val3, dbo_FreeInf2.Val4, dbo_FreeInf2.Val5, dbo_FreeInf2.Val6, dbo_FreeInf2.Val7, dbo_FreeInf2.Val8, dbo_FreeInf2.Val9, dbo_FreeInf2.Val23, dbo_FreeInf2.Val22, dbo_FreeInf2.Val24, dbo_FreeInf2.Val25, dbo_FreeInf2.Val26, dbo_FreeInf2.Val27, dbo_FreeInf2.Val12, dbo_FreeInf2.Val11, dbo_FreeInf2.Val10, dbo_FreeInf2.PK, dbo_FreeInf2.Gr2, dbo_FreeInf2.Val17, dbo_FreeInf2.Val13
    HAVING (((dbo_Rsv.OrdNo)=[Forms]![Printingform]![Input]) AND ((dbo_Rsv.ProdNo) Like [Forms]![Printingform]![cmoProduct] & "*") AND ((dbo_Rsv.ShpNo) Like [Forms]![Printingform]![cmoParti] & "*") AND ((dbo_Rsv.TrInf2)>"0") AND ((dbo_OrdLn.FinDt) Like [Forms]![Printingform]![cmoFerddato] & "*") AND ((dbo_OrdLn.TrInf1) Like [Forms]![Printingform]![cmoitem] & "*" And (dbo_OrdLn.TrInf1) Like [Forms]![Printingform]![cmoItem] & "*" And (dbo_OrdLn.TrInf1) Not Like "*r") AND ((dbo_FreeInf2.Gr2)=3))
    ORDER BY Format([dbo_OrdLn]![TrInf1],"000000");
    I would like to generete seperate PDF grupped by TrInf1 field
    Click image for larger version. 

Name:	Qry.JPG 
Views:	14 
Size:	33.7 KB 
ID:	26125

Please reply to this thread with any new information or opinions.

Similar Threads

  1. send email with attachment using Gmail (CDO)
    By DubCap01 in forum Programming
    Replies: 2
    Last Post: 05-20-2016, 03:12 AM
  2. Send email with Attachment
    By scoe in forum Programming
    Replies: 3
    Last Post: 09-23-2014, 03:02 AM
  3. How to send email without attachment
    By behnam in forum Programming
    Replies: 3
    Last Post: 07-30-2014, 08:24 AM
  4. Send email in Outlook with attachment
    By kelkan in forum Programming
    Replies: 1
    Last Post: 02-01-2013, 10:31 PM
  5. Beginner trying to send email with attachment
    By ahm in forum Programming
    Replies: 2
    Last Post: 03-24-2009, 08:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums