emails from database via VBA script

[mish]

CJ I know there has been a massive break in this but is there any way you can explain the above in simpler terms. I have tried to implement your suggestions and it doesn't work.

 

[mish]

I think what you were suggesting was to change this part of the script

While Not rs.EOF
BodyStr = BodyStr & rs.Fields(0) & "|           " & rs.Fields(1) & "|            " & rs.Fields(2) & "|                " & rs.Fields(3) & "|                     " & rs.Fields(4) & "|                    " & rs.Fields(5) & "|               " & rs.Fields(6) & "|            " & rs.Fields(7) & "|" & rs.Fields(8) & "|" & rs.Fields("MthFoodTtl") & vbCrLf  ' this loads the data from the recordset to the emails iteratively 0 is items ID and 8 is tdate
rs.MoveNext
Wend

If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar     " & "|" & "Cellar   " & "|" & "Date" & "              |" & "Total" & vbCrLf & BodyStr          
emailbodfunc = BodyStr

Set rs = Nothing
Set db = Nothing
End Function

inserting

While Not rs.EOF
BodyStr = BodyStr & rs.Fields(0) & "|           " & rs.Fields(1) & "|            " & rs.Fields(2) & "|                " & rs.Fields(3) & "|                     " & rs.Fields(4) & "|                    " & rs.Fields(5) & "|               " & rs.Fields(6) & "|            " & rs.Fields(7) & "|" & rs.Fields(8) & "|" & rs.Fields("MthFoodTtl") & vbCrLf  
(CJ CODE)body2=body2 & ".... 
rs.MoveNext
Wend
(CJ CODE)bodystr=body & body2


If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar     " & "|" & "Cellar   " & "|" & "Date" & "              |" & "Total" & vbCrLf & BodyStr          
emailbodfunc = BodyStr

Set rs = Nothing
Set db = Nothing
End Function

Just to recap, the functionality I'm looking for from the script is to output a monthly total not tablulated and repeated over and again as is currently I'd like it to instead output something like "Your monthly total total is....."

Thanks

 

[CJ_London]

It depends on what you are trying to to.

The bodystr function simply creates a recordset which contains the data required to appear in an email body and then loops through the recordset appending the data to a string to appear in the email body (which is passed bavk through the function BodyStr.

The poster had asked

is it possible to create another string like BodyStr (BodyStrII?) and have it's data (created by another loop through rs function

My response was based on modifying the recordset to include the second set of data then build the two strings at the same time and concatenating them together at the end.

You could just as easily within the BodyStr function build a completely separate recordset and run it after the the first one.

 

[mish]

CJ below is the current script which works brilliantly but I just need to be able to output the summed total outside the table, just once. I don't care which method i.e. the 2nd recordset or the string concatentation method. At the bottom I have printed the script's output as well, although this editor hasn't done it justice.

Option Explicit
Option Compare Database

Public Function emailbodfunc(MemID As Long) As String ' this function issues an sql query  which loads its result into a recordset the loop below the query formats the results and loads them into bodystr

Dim rs As Recordset
Dim db As Database
Dim email As String
Dim BodyStr As String


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate , (Select sum(bfast+lunch+desert) From Sales as tmp WHERE membersID=sales.membersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthFoodTtl FROM sales WHERE membersID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")
BodyStr = ""





While Not rs.EOF
BodyStr = BodyStr & rs.Fields(0) & "|           " & rs.Fields(1) & "|            " & rs.Fields(2) & "|                " & rs.Fields(3) & "|                     " & rs.Fields(4) & "|                    " & rs.Fields(5) & "|               " & rs.Fields(6) & "|            " & rs.Fields(7) & "|" & rs.Fields(8) & "|" & rs.Fields("MthFoodTtl") & vbCrLf  
rs.MoveNext
Wend

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

Set rs = Nothing
Set db = Nothing
End Function




Public Sub emailaddressfunc() 

Dim rs As Recordset
Dim db As Database
Dim BodyStr As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Firstname, Surname, emailaddress,ID FROM members;") 


While Not rs.EOF
BodyStr = emailbodfunc(rs.Fields("ID"))
If BodyStr <> "" Then DoCmd.SendObject acSendNoObject, Null, Null, rs.Fields("emailaddress"), Null, Null, "Monthly Statement", BodyStr, 1 
rs.MoveNext
Wend

Set rs = Nothing
Set db = Nothing
End Sub

Please find below your monthly statement

ID |Breakfast|Lunch|Desert|Desert Wine|Table Wine|Bar |Cellar |Date |Total
19| 5.5| 3| | | 4| | |12/11/2012|60.5
24| 5| 3| | | 4| | |12/11/2012|60.5
26| 5| 3| | | 4| | |12/11/2012|60.5
27| 5| 3| | | 4| | |11/11/2012|60.5
30| 5| 3| | | 4| | |12/11/2012|60.5

Thanks

 

[CJ_London]

Is the figure you want in the existing recordset?
i.e.

(Select sum(bfast+lunch+desert) From Sales as tmp WHERE membersID=sales.membersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthFoodTtl

If so then modify your code as follows:

Dim BodyStr As String
[COLOR=red]Dim Ttl as Double
[/COLOR]
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate , (Select sum(bfast+lunch+desert) From Sales as tmp WHERE membersID=sales.membersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthFoodTtl FROM sales WHERE membersID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")
BodyStr = ""

While Not rs.EOF
[COLOR=green]'remove [B][I]& "|" & rs.Fields("MthFoodTtl")[/I][/B] from this line if not required[/COLOR]
BodyStr = BodyStr & rs.Fields(0) & "|           " & rs.Fields(1) & "|            " & rs.Fields(2) & "|                " & rs.Fields(3) & "|                     " & rs.Fields(4) & "|                    " & rs.Fields(5) & "|               " & rs.Fields(6) & "|            [COLOR=black]" & rs.Fields(7) & "|" & rs.Fields(8)  & vbCrLf  
[/COLOR][COLOR=red]Ttl=Rs.Fields("MthFoodTtl")[/COLOR]
rs.MoveNext
Wend

[COLOR=green]'add additional bit to the end of this line (in red)
[/COLOR]If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar     " & "|" & "Cellar   " & "|" & "Date" & "              |" & "Total" & vbCrLf [COLOR=red][COLOR=black]& BodyStr[/COLOR]  & vbCrLf[COLOR=#000000] [/COLOR]& "Total Food      " & Ttl & vbCrLf[COLOR=#000000]         [/COLOR] [/COLOR]'add a header [COLOR=red]and total[/COLOR] line to the body

emailbodfunc = BodyStr

This is a slightly different solution to the one originally suggested but will do the job.

To make your formatting a bit better I would suggest you look at using the Space function in the following way which will effectively right justify the values. Change the 10 Value to whatever is required for each column

BodyStr & [COLOR=red]space(10-len(rs.Fields(0)) &[/COLOR] rs.fields(0) & "|" & [COLOR=red]space(10-len(rs.Fields(1))  &[/COLOR] rs.Fields(1) & "|" etc
 
then the final line above would become
 
& BodyStr[COLOR=#ff0000]  & vbCrLf[/COLOR][COLOR=#000000] [/COLOR][COLOR=#ff0000]& "Total Food" & Space(60-len(Ttl)) & Ttl & vbCrLf[/COLOR][COLOR=#000000] [/COLOR][COLOR=#ff0000] [/COLOR]

 

[mish]

Brilliant....I'll have a shot at this. Also, I didn't know there was the ability to do simple formatting using this method. It'll be great if this helps tidy the output a bit. At the moment it's not terribly presentable

 

[mish]

Is the figure you want in the existing recordset?
i.e.



If so then modify your code as follows:

Dim BodyStr As String
[COLOR=red]Dim Ttl as Double
[/COLOR]
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate , (Select sum(bfast+lunch+desert) From Sales as tmp WHERE membersID=sales.membersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthFoodTtl FROM sales WHERE membersID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")
BodyStr = ""

While Not rs.EOF
[COLOR=green]'remove [B][I]& "|" & rs.Fields("MthFoodTtl")[/I][/B] from this line if not required[/COLOR]
BodyStr = BodyStr & rs.Fields(0) & "|           " & rs.Fields(1) & "|            " & rs.Fields(2) & "|                " & rs.Fields(3) & "|                     " & rs.Fields(4) & "|                    " & rs.Fields(5) & "|               " & rs.Fields(6) & "|            [COLOR=black]" & rs.Fields(7) & "|" & rs.Fields(8)  & vbCrLf  
[/COLOR][COLOR=red]Ttl=Rs.Fields("MthFoodTtl")[/COLOR]
rs.MoveNext
Wend

[COLOR=green]'add additional bit to the end of this line (in red)
[/COLOR]If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar     " & "|" & "Cellar   " & "|" & "Date" & "              |" & "Total" & vbCrLf [COLOR=red][COLOR=black]& BodyStr[/COLOR]  & vbCrLf& "Total Food      " & Ttl & vbCrLf [/COLOR]'add a header [COLOR=red]and total[/COLOR] line to the body

emailbodfunc = BodyStr

This is a slightly different solution to the one originally suggested but will do the job.

To make your formatting a bit better I would suggest you look at using the Space function in the following way which will effectively right justify the values. Change the 10 Value to whatever is required for each column

BodyStr & [COLOR=red]space(10-len(rs.Fields(0)) &[/COLOR] rs.fields(0) & "|" & [COLOR=red]space(10-len(rs.Fields(1))  &[/COLOR] rs.Fields(1) & "|" etc
 
then the final line above would become
 
& BodyStr[COLOR=#ff0000]  & vbCrLf[/COLOR][COLOR=#ff0000]& "Total Food" & Space(60-len(Ttl)) & Ttl & vbCrLf[/COLOR]

Hello CJ, I tried the formatting as above and it keeps on giving a syntax error message

BodyStr = BodyStr & space(10-len(rs.Fields(0)) & rs.Fields(0) & "|" & rs.Fields(1) & "|            " & rs.Fields(2) & "|                " & rs.Fields(5) & "|                     " & rs.Fields(3) & "|                    " & rs.Fields(7) & "|               " & rs.Fields(4) & "|            " & rs.Fields(6) & "|" & rs.Fields(8) & vbCrLf

I have also received a warning about improper use of NULL when I add more fields to the sum (in the instance that there's no data) in the SQL statement, any ideas.

(Select sum(bfast+lunch+desert)

 

[CJ_London]

space(10-len(rs.Fields(0)))

My mistake - missing an extra closing bracket

(Select sum(bfast+lunch+desert)

you need to wrap these with nz to get over the null problem so

(Select sum(nz(bfast)+nz(lunch)+nz(desert))

 

[mish]

(Select sum(NZ(bfast)+NZ(lunch)+NZ(dwine)+NZ(bar)+NZ(desert)+NZ(cellar)+NZ(twine))

This is still returning invalid use of NULL, am I doing something else wrong? The SPACE formatting works btw, thanks.

NB There isn't a space in the word desert, in case you think it's that. I don't know why the forum isn't rendering it properly.

 

[mish]

Hmmm, i removed the SPACE formatting and it the sum worked even when I removed values from two of the fields. So it looks as though NZ and SPACE don't play well together for some reason.

EDIT: I tried the first field in the below line of the script

|

BodyStr = BodyStr & Space(50 - Len(rs.Fields(0))) & rs.Fields(0) & "|" & rs.Fields(1) & "

and it works for rs.Fields 0 but when I replicate it for the rs.Fields 1 and 2 etc I get invalid use of null. Could it be down to data type? rs.Fields 0 corresponds with a number while the rest are currency.

 

[CJ_London]

I think your problem is here - all fields used in any sort of function need to be nz'd.

space(10-len(rs.Fields(0))

Try

space(10-len(nz(rs.Fields(0)))

What might be a simpler solution is to do your nz'ds in your query then it is covered for any point 'further on'

SELECT ID,nz(bfast,0),nz(lunch,0),nz(dwine,0),nz(bar,0)... etc

The zero is what the nz function will return if the value is null - left blank and it will return a value in context which may be 0 for numbers and dates and a zero length string ("") for text. In the sum situation it will return 0 automatically because that is in context, in the select situation it is better to specify.

In other situations, this can be useful if you want to return something else - e.g. another value such as 1 (to avoid a division by zero error) or perhaps '"Empty", or "Null" or "Not Found" etc for text situations. So in your Select situation above you could put nz(bfast,"Got up Late!")!

 

[CJ_London]

What might be a simpler solution is to do your nz'ds in your query

The other thing to check, which is why I didn't actually think about null values before is that the default value in your tables would normally be set to 0 for numeric fields. Setting this (and writing a quick update routine to update existing records) would also solve this problem

 

[mish]

Hello CJ

I elected to change all of the table's fields default value to 0 instead of blank / null and this seemed to work. Furthermore it seemed to work with SPACE and I managed to format my email better. The problem is now that when I change the date parameters it throws up an incorrect us of null error

Set rs = db.OpenRecordset("SELECT ID, bfast,lunch,dwine,bar,desert,cellar,twine,tdate, (Select sum(NZ(bfast)+NZ(lunch)+NZ(dwine)+NZ(bar)+NZ(desert)+NZ(cellar)+NZ(twine)) From Sales as tmp WHERE MemberID=Sales.MemberID AND tdate Between #9/1/2012# And #9/30/2012#) as MthTtl FROM Sales WHERE MemberID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")

NB I also deleted all the old test records that may have contained the old default value.

Scratch this....I wasn't changing the date parameters in both parts of the query. My fault!

 

[CJ_London]

It implies you either have a null in your data or creating one as a result of your query

Looking at it I notice your subquery is for September whilst your main query is for November

 

[mish]

Thanks for the help with this project BTW!

I know I have asked this before but is it difficult to get the members' first and surnames from the same recordset and add them to each email with dear....... ........
here is your monthly statement?

 

[CJ_London]

What you should be able to do is something like (don't know what your members table and relevant fields are called):

Set rs = db.OpenRecordset("SELECT ID, bfast,lunch,dwine,bar,desert,cellar,twine,tdate, (Select sum(NZ(bfast)+NZ(lunch)+NZ(dwine)+NZ(bar)+NZ(desert)+NZ(cellar)+NZ(twine)) From Sales as tmp WHERE MemberID=Sales.MemberID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl
[COLOR=red], [FirstName] & " " & [LastName] AS Salutation[/COLOR] FROM Sales [COLOR=red]INNER JOIN MEMBERS ON Members.MemberID=Sales.MembersID [/COLOR]WHERE [COLOR=red]Sales.[/COLOR]MemberID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")

and in your vba amend bodystring with something like this:

[COLOR=red]rs.movefirst[/COLOR]
[COLOR=black]If BodyStr <> "" Then BodyStr = [COLOR=red]"Dear " & rs.fields("Salutation") & "," & vbCrLf & vbCrLf &[/COLOR] "Please find below your monthly statement" & vbCrLf & ......[/COLOR]

 

[mish]

Hello CJ, I tried adding the amended query and access just issues a vague syntax error message. I still haven't added the second part to bodystring yet, is that integral to the query working?

Set rs = db.OpenRecordset("SELECT ID, bfast,lunch,dwine,bar,desert,cellar,twine,tdate, (Select sum(NZ(bfast)+NZ(lunch)+NZ(dwine)+NZ(bar)+NZ(desert)+NZ(cellar)+NZ(twine)) From Sales as tmp WHERE MemberID=Sales.MemberID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl, Firstname & " " & Surname AS Salutation FROM Sales INNER JOIN Members ON Members.MemberID=Items.StaffID WHERE Sales.MemberID=" & MemID & " AND tdate Between #11/1/2012# And #11/30/2012#")

Firstname and Surname are the correct fields from the Members table btw.

 

[CJ_London]

Providing your members table is called members (you've confirmed the field names are correct) I can't see why this would not work - have you missed out any spaces or are firstname or lastname also the name of fields in the sales table?

The fact you have not completed the bodystr changes further down will not have an impact.

If the above does not work out, suggest you post the sql code exactly as you have written together with the exact error message

 

[mish]

CJ

I think I read somewhere that HTML formatting was incompatible with the current VBA method I am using, is this the case? I'd really like to format the result of the script / query into a more tidy email. At the moment, even with your SPACE function and with the pipes the table is broken as soon as a longer number is returned.

 

[CJ_London]

Try increasing the number in the SPACE function - it is allowing 10 chars, but perhaps you need to allow more space for certain columns (increase to 20 perhaps?).

Re HTML and the method you are using it should be possible - these links should help:

http://msdn.microsoft.com/en-us/library/office/ff197046.aspx
http://msdn.microsoft.com/en-us/library/office/aa141552(v=office.10).aspx

Not sure what the html code is to right justify if that is what you want to do.

Another alternative is to use your body build function to create a report and then attach it to the email.