+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
Office Software Thread, Email query result from Access in Technical; Im wanting to find a way to have Access send an email with the results of a Query.. Is this ...
  1. #1

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199

    Email query result from Access

    Im wanting to find a way to have Access send an email with the results of a Query.. Is this possible? could someone point me in the right direction please

  2. #2

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    In fact its probably the report thats generated based on the querey that I would want to email...

  3. #3

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,793
    Thank Post
    3,304
    Thanked 1,056 Times in 977 Posts
    Rep Power
    365
    what version of office or access ? 2003 ? Also is it outlook you are using ( same version as office / access )

  4. #4

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    aye 2003 and outlook

    ive made a form with a button that is adding the report as a html attachment, ideally i would want it to become the html body of the email. also if the report goes more than 1 page then it attaches 2 seperate .html pages. Also i would like to have the subject and to field populated already

    Im sure it must be possible with a bit of fancy VBA but I have 0 skills in that

  5. #5

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    Anyone able to offer any advice on this? Having it fill in the TO and SUBJECT field would be awesome, maybe even executing the send function as well automatically after clicking the button on the form..

    Should I make a post in scripting as Im guessing this is a VBA question mainly??

  6. #6

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,793
    Thank Post
    3,304
    Thanked 1,056 Times in 977 Posts
    Rep Power
    365
    Quote Originally Posted by RabbieBurns View Post
    Anyone able to offer any advice on this? Having it fill in the TO and SUBJECT field would be awesome, maybe even executing the send function as well automatically after clicking the button on the form..

    Should I make a post in scripting as Im guessing this is a VBA question mainly??
    you can do but will send you a pm - give me a min

  7. Thanks to mac_shinobi from:

    RabbieBurns (7th January 2010)

  8. #7

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    SendObject in Microsoft Access

    Thats the kind of thing I mean..

    But does anyone have an actual example of the code I would use?

  9. #8

    Andrew_C's Avatar
    Join Date
    Sep 2005
    Location
    Winchester
    Posts
    2,980
    Thank Post
    64
    Thanked 379 Times in 289 Posts
    Rep Power
    160
    I to would be interested in how it SHOULD work. Someone I have contact with on another forum, beat one of my DBs around to email those teachers who had failed to return videos. It works on the test data set that I sent him, but I can't merge the full set, and the modified database.

    If that is the kind f thing you're trying to do, I'd be happy to send mine across. May be a while before I'm next in, and I don't think I've a copy here.

  10. #9

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    Quote Originally Posted by Andrew_C View Post
    I to would be interested in how it SHOULD work. Someone I have contact with on another forum, beat one of my DBs around to email those teachers who had failed to return videos. It works on the test data set that I sent him, but I can't merge the full set, and the modified database.

    If that is the kind f thing you're trying to do, I'd be happy to send mine across. May be a while before I'm next in, and I don't think I've a copy here.
    Sounds like what Im wanting to do.. Ive got a button on a form (thats all there is pretty much) which then uses the email report function

  11. #10

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    Managed to accomplish what I was trying by using the VBA editor in access and modifying the SendObject string to incude to, subject, and force it to email automatically..

    WIll post the exact string next year if I remember.

    Thanks to all for suggestiona..

  12. #11

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    OK here is what I used as the vba

    Code:
    Private Sub cmdEmailReport_Click()
    On Error GoTo Err_cmdEmailReport_Click
    
        Dim stDocName As String
    
        stDocName = "NAME OF ATTACHMENT"
        DoCmd.SendObject acReport, stDocName, acFormatHTML, "email@domain.com", , , "EMAIL SUBJECT", , False
    
    Exit_cmdEmailReport_Click:
        Exit Sub
    
    Err_cmdEmailReport_Click:
        MsgBox Err.Description
        Resume Exit_cmdEmailReport_Click
        
    End Sub
    How can I append the days date to the email subject? I tried adding =Date() to it but I just get that as plaintext?

  13. #12

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,224
    Thank Post
    874
    Thanked 2,717 Times in 2,302 Posts
    Blog Entries
    11
    Rep Power
    780
    Give this a go:
    Code:
    Dim stSubject as String
     
    stDocName = "NAME OF ATTACHMENT"
    stSubject = "EMAIL SUBJECT on " & Date()
        DoCmd.SendObject acReport, stDocName, acFormatHTML, "email@domain.com", , , stSubject, False
    Edit, if that does not work try Date().Tostring
    Last edited by SYNACK; 7th January 2010 at 12:50 AM.

  14. Thanks to SYNACK from:

    RabbieBurns (7th January 2010)

  15. #13

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    That works great, thanks.

    We have a yank here to has is system date set up the crazy way, and when he uses the system his emails appear to come 1/7/09. Is there any way to force the date object to use a certain format independent of the user local settings?

  16. #14

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,224
    Thank Post
    874
    Thanked 2,717 Times in 2,302 Posts
    Blog Entries
    11
    Rep Power
    780
    Here we go: Date and Time Functions in VBA (Poynor - MIS 333k)

    You should be able to just string build the format you want out of the bits like so:

    Code:
    stSubject = "EMAIL SUBJECT on " & Day() & "/" & Month() & "/" & Year()
    or you can use day and month names with DayName() etc at in the page above.

    Edit: you could also use this:

    Code:
    stSubject = "EMAIL SUBJECT on " &  Format(Date, "dd/mm/yyyy")
    http://www.techonthenet.com/excel/fo...ormat_date.php
    Last edited by SYNACK; 7th January 2010 at 01:14 AM.

  17. Thanks to SYNACK from:

    RabbieBurns (7th January 2010)

  18. #15

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,527
    Thank Post
    1,339
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    199
    Quote Originally Posted by SYNACK View Post
    Here we go: Date and Time Functions in VBA (Poynor - MIS 333k)

    You should be able to just string build the format you want out of the bits like so:

    Code:
    stSubject = "EMAIL SUBJECT on " & Day() & "/" & Month() & "/" & Year()
    or you can use day and month names with DayName() etc at in the page above.

    Edit: you could also use this:

    Code:
    stSubject = "EMAIL SUBJECT on " &  Format(Date, "dd/mm/yyyy")
    Excel: Format Function with Dates (VBA only)
    edit: didnt see your edit. trying now

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [MS Office - 2007] MS Access query help
    By randle in forum Office Software
    Replies: 7
    Last Post: 9th November 2009, 01:17 PM
  2. Access - Date Query
    By garethedmondson in forum Office Software
    Replies: 12
    Last Post: 29th January 2009, 09:14 PM
  3. Vbscript to export query results from MS Access
    By RabbieBurns in forum Scripts
    Replies: 2
    Last Post: 24th June 2008, 11:09 AM
  4. EMBC and Email Query
    By Zebadee in forum Windows
    Replies: 5
    Last Post: 15th April 2008, 02:28 PM
  5. External student email access
    By Norphy in forum How do you do....it?
    Replies: 30
    Last Post: 10th November 2006, 12:24 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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