emails from database via VBA script (1 Viewer)

[mish]

Hello

This is first my posting and I'm a Access novice.

I have a simple 2 table database (Access 2010) one called members and the other called sales. What I'd like to do is use a VBA script to create an email to each member (who's emails are listed in the members' field) when executed. The to field will get their email address and the body section will get a list of their monthly purchases from the purchases table.

I thought (wrongly) that this would be a well trodden path, as it turns out the guidance I have thus found is vague and difficult to follow.

What I have deduced so far is that I'll need a recordset which gets it's data from an SQL query. I have done this and tested with debug.print and it prints all of the sorted data I want in the immediate window. All I need to know is how to get it into separate emails.

I have already read the MS article on this topic and failed to understand what was going on.

Any help is massively appreciated, even if it is as little as pointing me in the direction of a simple guide.

 

[pbaldy]

See if this helps:

http://www.granite.ab.ca/access/email/recordsetloop.htm

 

[mish]

Cheers, but I have read that to and it makes very little sense to the uninitiated. It's just reams of code with little in the way of explanation. There aren't even any comments, just a sentence at the top saying what is roughly does.

 

[CJ_London]

Hi,

To send emails you need to use

DoCmd.SendObject

This link might help

http://www.tek-tips.com/faqs.cfm?fid=4509

If you are scanning through tables for emails to send you would have VBA something like

Dim mydb as database
Dim Rst as recordset
 
set mydb=currentdb
set rst=mydb.openrecordset("SELECT * FROM members where ..there's something to send..") 'note body needs to be one field, if it is multiline (e.g. a list) then you'll either need to send as a table or query attachment or build up the body line by line before sending
 
while not rst.eof
    buildbody 'call to a function
 
    docmd.sendobject ... rst.fields("SendTo"), rst.fields("Subject Line"),rst.fields("Body")
    rst.movenext
wend
 
set rst=nothing
set mydb = nothing

 

[mish]

Hi,

To send emails you need to use

DoCmd.SendObject





If you are scanning through tables for emails to send you would have VBA something like

Dim mydb as database
Dim Rst as recordset
 
set mydb=currentdb
set rst=mydb.openrecordset("SELECT * FROM members where ..there's something to send..") 'note body needs to be one field, if it is multiline (e.g. a list) then you'll either need to send as a table or query attachment or build up the body line by line before sending
 
while not rst.eof
    buildbody 'call to a function
 
    docmd.sendobject ... rst.fields("SendTo"), rst.fields("Subject Line"),rst.fields("Body")
    rst.movenext
wend
 
set rst=nothing
set mydb = nothing

I don't get the buildbody 'call to a function line

and am I to assume the the rst.fields elements in the docmd are meant to reference parts of the recordset created above it in which case what do you call fields once they are in a recordset?

 

[CJ_London]

Hi,

Sorry they are prompts for where you need to build code.

I suggest Buildbody is a function that returns true if you have built it successfully and false if not so your code would actually be something like

if buildbody=true then
send email

The contents of the function would be to create the data you want to send in whatever format you want to send it (attachment or body content). Call it what you like, if you decide you want to send an attachment, call it AttatchmentBuiltOK

Re rst/field names, if your query is say

(Select ID, CustName from Table)

then you refer to the fields as rst.fields("ID") or rst.fields("CustName")

Alternatively they can be referred to as rst.fields(0) or rst.fields(1)

To cut down on the typing a bit you can use
With rst
.fields("ID")
.fields("CustName")
end with

 

[mish]

Hi,

Sorry they are prompts for where you need to build code.

I suggest Buildbody is a function that returns true if you have built it successfully and false if not so your code would actually be something like

if buildbody=true then
send email

The contents of the function would be to create the data you want to send in whatever format you want to send it (attachment or body content). Call it what you like, if you decide you want to send an attachment, call it AttatchmentBuiltOK

Thanks for your help so far CJ but I still don't understand what you mean here....are you suggesting an if loop returning a boolian result. Do you have any example code to help illustrate what this might look like? I totally understand the

Docmd.sendObject part now btw so you have managed to help me some of the way.

 

[mish]

Got it my script working with Outlook but as soon as it runs I get a message saying "A program is trying to send an email message on your behalf. Click deny and check your virus definitions are up to date"

I click 'allow' instead of 'deny' and I get a run time 424 error saying object required.

When I check my default Outlook outbox there is one email which has been created which is progress of sorts but I need this to send 80+ separate emails and the body section needs to get data from several not just one field, is this possible?

I had heard about this problem having read countless forums and guide on this topic and if it's something insurmountable does anybody think its worth going down the Microsoft Outlook Object Model route instead?

 

[CJ_London]

which line do you get this error - might be an idea to post your code

This link might be helpful otherwise copy the entire message into your IE search box

http://office.microsoft.com/en-gb/o...-sending-e-mail-on-my-behalf-HA001229943.aspx

body section needs to get data from several not just one field, is this possible

Yes - you would do this in your buildbody function

Here is a suggestion - note I have changed it to a string function and you will need to do some tidying up to get the columns to line up.

Private Function BuildBody(MemID as long) as string
Dim MyDb as Database
Dim Rst as Recordset
Dim BodyStr as string
 
set mydb=currentdb
set rst=mydb.openrecordset("SELECT ID, ItemName, Quantity From Sales WHERE MemID=" *& MemID) ' change to what you want to report and from what table(s) - try creating in query builder then copy th text to here
 
BodyStr=""
While not rst.eof
    Bodystr=Bodystr & rst.fields(0) & "    " & rst.fields(1) & "    " & rst.fields(2) & vbcrlf '(linefeed)
    rst.movenext
wend
if Bodystr<>"" then bodystr="ID         " & "Item       " & "Quantity   " & vbcrlf & bodystr 'add a header
buildbody=bodystr
set rst=nothing
set mydb=nothing
end function
 
Calling procedure =
 
..
set rst=mydb.openrecordset("SELECT * FROM members where ..there's something to send..") 'note body needs to be one field, if it is multiline (e.g. a list) then you'll either need to send as a table or query attachment or build up the body line by line before sending
 
while not rst.eof
    bodystr=  buildbody(rst.fields("memID") )  
    if bodystr<>"" then docmd.sendobject ... rst.fields("SendTo"), rst.fields("Subject Line"),bodystr
    rst.movenext
wend

 

[JBRTaylor]

Hi, I have found this post really useful as i am trying to do a similar thing however i have very little knowledge of VBA. I have tried to follow your example as closely as possible and have created two tables with the filed headings as in the sql statements however in the second part i keep getting an error message saying Object Required. Any ideas on what i may have done wrong. It is possible i may have misinterpreted a part of you code and included something which shouldn't be there or should have been changed.

Thanks in advance
Jon

 

[CJ_London]

Hi JB,

Can you post your code and highlight which line is the problem (the one highlighted in yellow)

A couple of thoughts to try first - you need to declare Mydb and Rst in the second part

so

Dim Mydb as Database
Dim Rst as Recordset

Set Mydb=currentdb
Set rst=mydb.openrecordset...

don't forget to set them to nothing once you have finished with them

 

[JBRTaylor]

Hi,
Yes that was the problem i hadn't declared the variables and set the database as you said. I have got it working in a test database, now to set it up in the main one. Fingers crossed i'll manage that one on my own, lol. Thanks for your help with this, it is going to be so useful.
Jon

 

[mish]

I'm not having as much joy as JBR Taylor

my script keeps on complaining about object or block variables.


Public Function emailbodfunc()

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

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From sales;")

BodyStr = ""
While Not rs.EOF
BodyStr = BodyStr & rst.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & vbCrLf '(linefeed)
rst.MoveNext
Wend
If BodyStr <> "" Then BodyStr = "ID " & "Item " & "Quantity " & vbCrLf & BodyStr 'add a header
buildbody = BodyStr
Set rs = Nothing
Set db = Nothing
End Function



Public Sub emailaddressfunc()

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


Set rs = db.OpenRecordset("SELECT Firstname, Surname, emailaddress " & _
"FROM members" & _
"ORDER BY members.ID;")

While Not rs.EOF
strEmailAddress = strEmailAddress & rs("emailaddress") & ","
email = rs(2)
DoCmd.SendObject acSendNoObject, Null, Null, rs.Fields("emailaddress"), Null, Null, "Test subject", rs.Fields("bfast"), False, Null
rs.MoveNext
Wend


rs.Close
Set rs = Nothing
End Sub

Sorry about the delay in responding, again, any help is appreciated.

 

[CJ_London]

You are missing a few bits! Have noted in italic, but not sure if the forum will remove the formatting

Public Function emailbodfunc[B][I](ID as Long) as string[/I][/B]
Dim rs As Recordset
Dim db As Database
Dim BodyStr As String
 
[B][I]Set db=currentdb[/I][/B]   
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From sales[B][I] WHERE ID=" & ID)[/I][/B]
BodyStr = ""
 
While Not rs.EOF
BodyStr = BodyStr & rst.Fields(0) & "|" & rs.Fields(1) & "|" & rs.Fields(2) & vbCrLf '(linefeed) [I]appreciate only test data but suggest using pipes since spaces could get lost if text either side has spaces - also means it can easily posted into another db[/I]
[B][I]rs[/I][/B].MoveNext
Wend
 
If BodyStr <> "" Then BodyStr = "ID [B][I]" & "|" & "[/I][/B]Item[I]"[B] & "|" &[/B] "[/I]Quantity " & vbCrLf & BodyStr 'add a header
[B][I]emailbodfunc[/I][/B] = BodyStr
Set rs = Nothing
Set db = Nothing
End Function
 
and your emailaddressfunc should look like this
 
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 " & _
"FROM members" & _
"ORDER BY members.ID;")
 
While Not rs.EOF
[B][I]BodyStr = emailbodfunc(rs.fields("ID")[/I][/B]
[B][I]if BodyStr<>"" then[/I][/B] DoCmd.SendObject acSendNoObject, , , rs.Fields("emailaddress"), , , "Test subject",[B][I] BodyStr[/I][/B]
rs.MoveNext
Wend
 
 
Set rs = Nothing 
[B][I]set db = nothing[/I][/B]
End Sub

Give that a try

 

[mish]

CJ

I updated the script with your suggested additions and it seemed to run well till this line in the emailaddressfunc function which returns a syntax error.

BodyStr = emailbodyfunc(rs.Fields("ID")

 

[CJ_London]

sorry - missed a bracket off

BodyStr = emailbodyfunc(rs.Fields("ID"))

 

[mish]

Now this line is flagging up a syntax error in query expression 'ID='

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From sales WHERE ID=" & ID)

 

[CJ_London]

if ID is of a type text then you need

WHERE ID= '" & ID & "'"

 

[mish]

hmmmm 'ID' is just the autonumber standard field which all Access tables create. I have another field called memberID in members and its data type is 'number' .

How does that effect the script?

 

[CJ_London]

can you check this line


Set rs = db.OpenRecordset("SELECT Firstname, Surname, emailaddress " ...


You need to bring ID through so it should be

Set rs = db.OpenRecordset("SELECT ID, Firstname, Surname, emailaddress