[ms office - 2003] Email query result from Access

[RabbieBurns]

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

[RabbieBurns]

In fact its probably the report thats generated based on the querey that I would want to email...

[mac_shinobi]

what version of office or access ? 2003 ? Also is it outlook you are using ( same version as office / access )

[RabbieBurns]

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

[RabbieBurns]

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??

[mac_shinobi]

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

[RabbieBurns]

SendObject in Microsoft Access

 

Thats the kind of thing I mean..

 

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

[Andrew_C]

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.

[RabbieBurns]

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

[RabbieBurns]

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..

[RabbieBurns]

OK here is what I used as the vba

 

Private Sub cmdEmailReport_Click()
On Error GoTo Err_cmdEmailReport_Click

   Dim stDocName As String

   stDocName = "NAME OF ATTACHMENT"
   DoCmd.SendObject acReport, stDocName, acFormatHTML, "[email protected]", , , "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?

[SYNACK]

Give this a go:

Dim stSubject as String

stDocName = "NAME OF ATTACHMENT"
stSubject = "EMAIL SUBJECT on " & Date()
   DoCmd.SendObject acReport, stDocName, acFormatHTML, "[email protected]", , , stSubject, False

 

Edit, if that does not work try Date().Tostring

Edited January 7, 2010 by SYNACK

[RabbieBurns]

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?

[SYNACK]

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:

 

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:

 

stSubject = "EMAIL SUBJECT on " &  Format(Date, "dd/mm/yyyy")

http://www.techonthenet.com/excel/formulas/format_date.php

Edited January 7, 2010 by SYNACK

[RabbieBurns]

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:

 

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:

 

stSubject = "EMAIL SUBJECT on " &  Format(Date, "dd/mm/yyyy")

Excel: Format Function with Dates (VBA only)

 

edit: didnt see your edit. trying now

[SYNACK]

Ah, yes I messed up it should be:

 

stSubject = "EMAIL SUBJECT on " & Day(Now()) & "/" & Month(Now()) & "/" & Year(Now())

 

if that gives an error try just Now rather than Now() in the brackets. The other method should go though and is probably a bit cleaner

[RabbieBurns]

yea, the

stSubject = "EMAIL SUBJECT on " &  Format(Date, "dd/mm/yyyy")

works a treat..

 

many thanks