emails from database via VBA script

[CJ_London]

well spotted!

 

[pbaldy]

Thanks! I should have added this for mish:

http://www.baldyweb.com/OptionExplicit.htm

 

[mish]

With many eyes all bugs are shallow! Linus' Law

Cheers pbaldy that fixed all the script errors.

The end product looks like it only got one result though and produced one email. How / where can i add this date between condition to the sql query

Between #11/1/2012# And #11/30/2012#

this way it should return more results and produce multiple emails

 

[mish]

pbaldy that's a really good way to avoid typo problems. Cheers

 

[CJ_London]

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID [COLOR=red]& " AND tdate Between #11/1/2012# And #11/30/2012#[/COLOR])

don't miss the space before the AND!

 

[mish]

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID [COLOR=red]& " AND tdate Between #11/1/2012# And #11/30/2012#[/COLOR])

don't miss the space before the AND!

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate FROM Sales WHERE MembersID=" & MemID & AND tdate Between #11/1/2012# And #11/30/2012#)

This gives an opaque syntax error, have I done it right?

 

[pbaldy]

CJ forgot the ending quotes:

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")

Though I can't see how adding a filter will increase the number of records returned.

 

[mish]

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#)

I spotted the omitted quote mark and added it but it still gave an error message.

 

[CJ_London]

You've got the 'starting quote in the wrong place

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#)

it should be (with missng end qoote)
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate FROM Sales WHERE MembersID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")

 

[mish]

Brilliant, the script works a treat. Thanks CJ and pbaldy for your help. By the looks of the numbers viewing this thread I think it may prove useful to others to boot.

 

[mish]

I have realised that there is some missing functionality in the script, is it possible to sum the fields from the various Sales fields and output the total to the email's body concatenated like "your monthly total is" = monthtotal. Would this require another function with another query or could the original one be altered to achieve this?

 

[CJ_London]

You can do either but this has incorporated the total in the original

SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE  MembersID=Sales.MembersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#

Perhaps a bit cleverer is to show a month to date value instead, in which case you would have:

SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE  MembersID=Sales.MembersID AND tdate >=#11/1/2012# And  TDate<=Sales.tdate) as MthToDate[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#

 

[mish]

CJ that's brilliant that it's possible to sum within the original query, I'll try it out. How flexible is this method? Can I for example, sum and output say wine, dwine,bar and cellar then do the same with bfast,lunch,dinner and desert for a separate total?

 

[mish]

CJ it works without any errors but how to I add the total (MthTtl) to the email body. Do I have to add it here, or do I have to declare some kind of variable to store it and act as a placeholder?

If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar " & "|" & "Cellar " & "|" & "Date" & vbCrLf & BodyStr 'add a header line to the body
emailbodfunc = BodyStr

 

[CJ_London]

Glad you're pleased

Can I for example, sum and output say wine, dwine,bar and cellar then do the same with bfast,lunch,dinner and desert for a separate total?

Yes - just need to copy and paste the subquery where you want it in the order then modify it and give it another name (e.g. BarTtl, RestTtl)

Do I have to add it here, or do I have to declare some kind of variable to store it and act as a placeholder?

You need to add it here as indicated in red and in the bodyfunction as well - -I'm sure your will be able to work that out now

If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar " & "|" & "Cellar " & "|" & "Date"[COLOR=red] & "Month To Date"[/COLOR] & vbCrLf & BodyStr 'add a header line to the body

 

[mish]

Yes - just need to copy and paste the subquery where you want it in the order then modify it and give it another name (e.g. BarTtl, RestTtl)

I don't get this, when you say subquery do you mean everything between Public Function emailbodfunc(MemID As Long) As String and End function or is it part of the sql query.

I have managed to get the sum to output as a field in the email's body which is good but it just repeats the same total for every line. Ideally I'd like it to output to another line in the body just once.

 

[CJ_London]

don't get this, when you say subquery do you mean everything between Public Function emailbodfunc(MemID As Long) As String and End function or is it part of the sql query.

A subquery is part of the sql as highlighted in red. The bit in brackets is the actual subquery and in this case it is called MthTtl

SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE  MembersID=Sales.MembersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#

I have managed to get the sum to output as a field in the email's body which is good but it just repeats the same total for every line. Ideally I'd like it to output to another line in the body just once

To do this, you need to modify your vba code - so remove the total from your body build line that you have just put in

I can't find the code at the moment but you need to change the bodybuild function by adding as follows in red

while not rst.eof [COLOR=green]'existing[/COLOR]
    body=body & "....[COLOR=green]'existing[/COLOR]
    rst.movenext [COLOR=green]'existing[/COLOR]
    [COLOR=red]if rst.eof then body=body & vbcrlf & vbcrlf & "Total for Month:    " & rst.fields("MthTtl")[/COLOR]
wend [COLOR=green]'existing[/COLOR]

 

[CJ_London]

don't get this, when you say subquery do you mean everything between Public Function emailbodfunc(MemID As Long) As String and End function or is it part of the sql query.

A subquery is part of the sql as highlighted in red. The bit in brackets is the actual subquery and in this case it is called MthTtl

SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE  MembersID=Sales.MembersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#

I have managed to get the sum to output as a field in the email's body which is good but it just repeats the same total for every line. Ideally I'd like it to output to another line in the body just once

To do this, you need to modify your vba code

 

[mish]

Thanks for the clarification on the sub.

When you say modify the script is it possible to create another string like BodyStr (BodyStrII?) and have it's data (created by another loop through rs function) inserted into the docmd.sendobject function's messagetext argument.

 

[CJ_London]

yes and no!

Given the present structure-

You can have a second bodystr, but only one is returned through the function so you could appending the second one to the first one.

Assuming you are using the same dataset, there is no need to run it a second time, just build the second bodystr after the first

while not rst.eof 'existing
    body=body & "....'existing
    [COLOR=red]body2=body2 & ".... [/COLOR]
    rst.movenext 'existing
    if rst.eof then body=body & vbcrlf & vbcrlf & "Total for Month:    " & rst.fields("MthTtl")
wend 'existing
[COLOR=red]bodystr=body & body2[/COLOR]

As far as the sendmessage message text is concerned, it can only have one value so the above would work.

If you have a different dataset, create a second function like the first body function and amend accordingly then you would have

docmd.sendobject....body1 & body2