Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes basically, I like to be able to send each individual an email with an attached PDF file. As shown in report.jpg, force new page is set on an ID header, when I do a print preview, only each person's report are display on the screen. Now I just need to figure out how to send it via email.

  2. #17
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,353
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    Ok, I will write and test some code for you. It will take some time.

  3. #18
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Great Thank you very much, anything you can do will help me a lot.

  4. #19
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,353
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    Ok, I am attaching a database in Access 2003 format that includes a module with this subprocedure:

    Code:
    Public Sub EmailReport()
    
    
        Dim golApp           As Outlook.Application
        Dim gnspNameSpace    As Outlook.NameSpace
    
    
        Dim rst As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim strSQL As String
        Dim strFileName As String
        Dim objMail As MailItem
    
    
        Set golApp = New Outlook.Application            ' Application object.
        Set gnspNameSpace = golApp.GetNamespace("MAPI") ' Namespace object.
        
        strFileName = "c:\YourFolder\YourFileName.pdf"  'Change this for your specific folder
        
         Set cmd = New ADODB.Command
            
         cmd.ActiveConnection = CurrentProject.Connection
         cmd.CommandType = adCmdText
         
         strSQL = "SELECT Email FROM [instructor table]"
         
         cmd.CommandText = strSQL
                     
        Set rst = cmd.Execute
        
        While Not rst.EOF
        
            Set objMail = golApp.CreateItem(olMailItem)
        
            objMail.Attachments.Add (strFileName)
            objMail.To = rst("Email")
            
            objMail.Body = "This is the email body"   'Replace it by your body
            objMail.Subject = "This is the email subject"
            objMail.Send
            'objMail.Close (olDiscard)
            Set objMail = Nothing
          
             
            rst.MoveNext
        Wend
          
        rst.Close
        Set rst = Nothing
        
    End Sub
    You will notice that there are 3 places where you are required to add changes, properly commented.

    1."strFileName = "c:\YourFolder\YourFileName.pdf" 'Change this for your specific folder

    Replace the red part with the full path and filename of your report

    2. objMail.Body = "This is the email body" 'Replace it by your email body
    3. objMail.Subject = "This is the email subject" 'Replace it by the email subject


    There is also a form in the database, with a button. Once you click the button, the emails will be sent, using the default email account in your Outlook.
    Currently it works with Outlook 2013 and Access 2013. In order to get it to work with Office 2010, you can simply import all the form and the Visual Basic module into your database, open the module in the Visual Basic Editor, open the Tools->References menu, and uncheck the reference to the Microsoft Outlook 15 Object Library. You will then need to scroll the list down and check a reference to the Microsoft Outlook 14 Object Library.

    This should get it working. The code uses a table name instructor table, which I believe is the name of the table that contains the email addresses you want to use.

    As an alternative, you can create a new module yourself, with the code for the procedure I have posted here. You will need to add references to the Microsoft Outlook 14 Object Library and to the Microsoft ActiveX Data Objects 2.x Library (I did it for 2.8 library version).
    Once you do this, you can run the code, the best way being similar to the way I used with the form in the database attached.
    Attached Files Attached Files

  5. #20
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow, that was quick. thank you so much. I will test it tonight after my meeting, I'll will report back tomorrow to let you know the result.

    Thanks again.

  6. #21
    New Lounger
    Join Date
    Sep 2012
    Posts
    8
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi, I dont know exactly but try this thread
    http://windowssecrets.com/forums/sho...eparate-people!

  7. #22
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ruirib,

    I am getting a syntax error on one of the line, see attached. I think I got format incorrectly, can you take a look? Thankssyntax error.JPG

  8. #23
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Enclose the c:\... in double quotes "

  9. #24
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    did that too now I am getting "run time error 5" invalid procedure call and argument.

    this is what I have, I've move the location of the file to root of drive C.
    objMail.Attachments = "c:\instructor1.pdf"

  10. #25
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,353
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    You cannot do that. Look at the code I posted. The file name needs to be changed at the line that looked like this:

    Code:
    strFileName = "c:\YourFolder\YourFileName.pdf"
    The line with the objMail.Attachments statement should not be changed and does need to be changed.

  11. #26
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I send the file back, I added 4 test users with query and report this is how it would look like with the real data.
    Attached Files Attached Files

  12. #27
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did that at first, thats when I get the error so I move the file to c:\. The working folder is located on my desktop: c:\User\rho\Desktop\workshop score\instructor1.pdf

  13. #28
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,353
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    Ok, I have updated the file to use the document in c:\.
    Attached Files Attached Files

  14. #29
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok thanks, I try it soon as I get back got another dame meeting

  15. #30
    New Lounger
    Join Date
    Dec 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK that works, how do I send just correct portion of the report to each person? The instructor1.pdf include all the pages in one file, that mean everyone the the same file with everybody else's information on it. Its there a way to send just the pages for that person? Example: if Larry only has two pages, how do I send him just that two pages out of said 90 pages. On the report if a "force new pages before section" is added to the ID header, then on the print preview, it will display the report by each person's ID not as one continues report as with instructor1.pdf.

Page 2 of 3 FirstFirst 123 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •