Solved

auto email

Posted on 2004-09-27
41
307 Views
Last Modified: 2012-06-27
Here is the code that I know was successful back when I accepted the answer.  Now I can figure out why it's stopping at the docmd line with the email showing in my window.

Dim MyDb As Database
Dim MyRS As Recordset
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strMsg As String
Dim strSubject As String
Dim x As Variant
Set MyDb = CurrentDb
Set MyRS = MyDb.OpenRecordset("Email addresses", dbOpenDynaset)
MyRS.MoveFirst
Do
strTo = MyRS("Email Address")
'Build your message and incorporate the info about customer
strMsg = "Message for Customer " & MyRS("name")
strSubject = "Billing Information Notice"
DoCmd.SendObject acSendNoObject, , "MS-DOS Text (*.txt)", strTo, strCC, strBCC, strSubject, strMsg, Val(0)
MyRS.MoveNext
Loop Until MyRS.EOF
MyRS.Close
MyDb.Close
0
Comment
Question by:TimKestermont
  • 22
  • 18
41 Comments
 

Author Comment

by:TimKestermont
ID: 12163962
This is the link I accepted as the answer.
http://www.experts-exchange.com/Databases/MS_Access/Q_20768220.html
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164090
DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strMsg, 0

Dave
0
 

Author Comment

by:TimKestermont
ID: 12164112
Is there a reference in the library that needs to be checked?
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164177
Not for SendObject.

You will need to adda refrence to Microsoft DAO object Library to do the other stuff though...  If you dont have this refrecened it should have errored on this line Set MyDb = CurrentDb

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164196
these may also be a problem : chaneg them to....

strTo = MyRS("[Email Address]")

and

strMsg = "Message for Customer " & MyRS("[name]")

Dave
0
 

Author Comment

by:TimKestermont
ID: 12164233
Set MyRS = MyDb.OpenRecordset("Email addresses", dbOpenDynaset) ' This is my tbl name Email addresses
strTo = MyRS("Email Address")  this is my email field
strMsg = "Message for Customer " & MyRS("name") this is the "name" of the person

It worked a while back but I didn't have to implement it then.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164272
Yes, Name is a reserved word, so to get around complications that this causes, try wrapping it in [].  Seeing Email Address has a space, also try wrapping this in [] as shown above

Dave
0
 

Author Comment

by:TimKestermont
ID: 12164784
It is now stopping when the email pulls up and sets focus to strTo.
0
 

Author Comment

by:TimKestermont
ID: 12164810
Then I try without the spaces by renaming the fields and it stops at the docmd. again.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164828
you sure you got it in the right spot

pls post you new code

Dave
0
 

Author Comment

by:TimKestermont
ID: 12164939
I can set the spaces in brackets as you mentioned and it's still the same except on name.  It will not allow me do just use brackets there.  It will usually stop on the strTo  in the Docmd. line.  or at least that is where it stops and leaves focus on the email.  Thanks

Dim MyDB As Database
Dim MyRS As Recordset
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strMsg As String
Dim strSubject As String
Dim x As Variant
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Email Addresses", dbOpenDynaset)
MyRS.MoveFirst
Do
strTo = MyRS("EmailAddress")
'Build your message and incorporate the info about customer
strMsg = "This is a test for the auto email " & MyRS("name")
strSubject = "Research Institute"
DoCmd.SendObject acSendNoObject, , "MS-DOS Text (*.txt)", strTo, strCC, strBCC, strSubject, strMsg, 0


MyRS.MoveNext
Loop Until MyRS.EOF
MyRS.Close
MyDB.Close
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164951
use this!

DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strMsg, 0
0
 

Author Comment

by:TimKestermont
ID: 12164976
This below has the same result.  I put a msgbox strTo  so that I could see it is passing the emails, so it has to be in the docmd line.

DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strMsg, 0
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164993
Do you have Outlook or Groupwise or something like that installed??
0
 
LVL 34

Expert Comment

by:flavo
ID: 12164998
you'll need:

Microsoft Exchange, Microsoft Mail, Microsoft Windows for Workgroups mail, or another electronic mail application that uses the Microsoft Mail Applications Programming Interface (MAPI).
0
 

Author Comment

by:TimKestermont
ID: 12164999
Groupwise
0
 

Author Comment

by:TimKestermont
ID: 12165009
I'm not for sure about MAPI because this worked fine a couple of months ago.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12165010
I use Groupwise too (Version 6.0, think it used to have 5.5 and that worked fine too).
0
 
LVL 34

Expert Comment

by:flavo
ID: 12165021
As a test try this


Sub myTest()

Dim sTo As String

sTo = "YourEmailAddressGoesHere!!!"
DoCmd.SendObject acSendNoObject, , , sTo, , , , "This is a test", False

End Sub
0
 

Author Comment

by:TimKestermont
ID: 12165041
Same break at the docmd.sendobject.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:TimKestermont
ID: 12165045
Something must of happened somewhere because I used this cold a number of times to demonstrate to my boss that it could be done.
0
 

Author Comment

by:TimKestermont
ID: 12165051
Just will not get pass the To field or StrTo.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12165102
Just working on another method
0
 
LVL 34

Expert Comment

by:flavo
ID: 12165112
Very simple Automation of Groupwise

Sub Email()

Dim oAcct As GroupwareTypeLibrary.Account
Dim oApp As GroupwareTypeLibrary.Application
Dim oMsg As GroupwareTypeLibrary.Message
Dim sRecipient as String
sRecipient = Replace(Replace("david{dot}leahy{at}brisbane{dot}qld{dot}gov{dot}au", "{dot}", "."),"{at}","@")
**** Email Address modified ******
Alan Warren
Page Ed (Databases)
****************************

Set oApp = CreateObject("NovellGroupWareSession")
Set oAcct = oApp.Login("", "")

Set oMsg = oAcct.MailBox.Messages.Add

oMsg.Subject = "Subject - 0"
oMsg.BodyText = "Body Text"
oMsg.Recipients.Add sRecipient, , 0   '0 for To, 1 for CC, 2 for BCC
oMsg.Send

Set oMsg = Nothing
Set oApp = Nothing
Set oAcct = Nothing

End Sub
0
 
LVL 34

Expert Comment

by:flavo
ID: 12165114
Add a refrence to Groupware Type library
0
 

Author Comment

by:TimKestermont
ID: 12165985
I will let you know tomorrow at work.  Thanks
0
 

Author Comment

by:TimKestermont
ID: 12169445
The new method works for a specific address.  If I type in the address.  But would it send to multiple addresses in a database
0
 

Author Comment

by:TimKestermont
ID: 12170267
I removed Outlook and now there is no runtime error. If I have 4 email addresses in my db it will pull up 4 email windows and once again not populating the TO or sending the email automatically.
0
 

Author Comment

by:TimKestermont
ID: 12170338
Sorry, last comment was incorrect
0
 

Author Comment

by:TimKestermont
ID: 12171088
This works on reports-

DoCmd.SendObject acSendReport, "Heyreport", acFormatRTF, strTo, strCC, strBCC, strSubject, strMsg, Val(0)
0
 

Author Comment

by:TimKestermont
ID: 12171260
This does not
DoCmd.SendObject acSendNoObject, , "MS-DOS Text (*.txt)", strTo, strCC, strBCC, strSubject, strMsg, Val(0)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12174947
It works fine with Win 2k / Access 97 / Groupwise and Win XP / Access 2003 / Outlook 2003 (Tested at home and at work)
0
 

Author Comment

by:TimKestermont
ID: 12174996
Well I don't want to waste anymore of you time.  It simply stops at the TO field.  I actually posted another question

http://www.experts-exchange.com/Databases/MS_Access/Q_21148212.html

The acSendReport works but not the acSendnoobject.  No one has been able to fix or address it adequately.  I know the code is suppose to work as I have used it prior.  But it's not now.  

Thanks for your help.  
0
 
LVL 34

Expert Comment

by:flavo
ID: 12175023
one last go!

DoCmd.SendObject -1, , ,strTo, strCC, strBCC, strSubject, strMsg, 0


What error does it throw anyway?????
Dave
0
 

Author Comment

by:TimKestermont
ID: 12176650
Hey flavo,

Sorry about the delay.  I've got a headache over this...nothing like a stoppage in the work day eh?  I did get your groupwise code to work.  I can't read it to maybe place a variable in there.  But it did work.

There runtime is 2958  reserved error.   I looked it up and I was lost overall.  I will try the above code.

I want to thank you for the extra effort.  I know the code works but you are troubleshooting and I appreciate it a great deal.

Thanks
0
 
LVL 34

Accepted Solution

by:
flavo earned 500 total points
ID: 12176930
Tim,

Ok these are the bits you want to change
Dim sRecipient as String
sRecipient = Replace(Replace("david{dot}leahy{at}brisbane{dot}qld{dot}gov{dot}au", "{dot}", "."),"{at}","@")
**** Email Address modified ******
Alan Warren
Page Ed (Databases)
****************************

oMsg.Subject = "Subject - 0"
oMsg.BodyText = "Body Text"
oMsg.Recipients.Add sRecipient, , 0   '0 for To, 1 for CC, 2 for BCC
oMsg.Send


The first line oMsg.Subject = "Subject - 0" sets the subject of the email to Subject - 0, so in your case you'll want
oMsg.Subject = strSubject

>>oMsg.BodyText = "Body Text"
this is the text inside the email, so once again in your case use
oMsg.BodyText = strMsg

Now the next line is a bit harder to understand.


oMsg.Recipients.Add strTo, , 0

This line will add the email addresses in strTo as TO recipients (ie they'll go in the To line)


oMsg.Recipients.Add strCC, , 1

Will add the email addresses in strCC tobe CC'ed to the email (notice how the 0 changed to a 1)

and lastly, oMsg.Recipients.Add strBCC, , 2

strBCC get BCC'ed (notice the 2 now)

so basically you write omsg.Recipients.add "emailAddress", {blank}, 0 {for TO} or 1 {for CC} or 2 {for BCC}

then the last line oMsg.Send sends the message


Ill put comments for the rest of the code now wo you know whats happening

'Decalare variables
Dim oAcct As GroupwareTypeLibrary.Account
Dim oApp As GroupwareTypeLibrary.Application
Dim oMsg As GroupwareTypeLibrary.Message

'Create a new instance of GW for us to use in VBA
Set oApp = CreateObject("NovellGroupWareSession")
'Log in - note, you you dont have GW logged in, it will prompt with the usual login screen
' you can also use Set oAcct = oApp.Login("userName", "Password") if you want
Set oAcct = oApp.Login("", "")

'create a new email message for us to use
Set oMsg = oAcct.MailBox.Messages.Add

'as above
oMsg.Subject = "Subject - 0"
oMsg.BodyText = "Body Text"
oMsg.Recipients.Add sRecipient, , 0   '0 for To, 1 for CC, 2 for BCC
oMsg.Send

'clean up our variables
Set oMsg = Nothing
Set oApp = Nothing
Set oAcct = Nothing

Idea???

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12177678
Thanks Alan!
0
 

Author Comment

by:TimKestermont
ID: 12179915
Can I do the CurrentDB recordset with this code and pull from the db?'
0
 
LVL 34

Expert Comment

by:flavo
ID: 12184377
You sure can mate.

What exactly are you trying to do???
0
 

Author Comment

by:TimKestermont
ID: 12184848
I tried to use your code with mine and I sent 2 out of 6 emails with this code. I am trying to send multiple emails to the address's in the DB.  Also, could you answer this question on the this link so I can give points to that open question?
http://www.experts-exchange.com/Databases/MS_Access/Q_21148212.html

Dim MyDb As Database
Dim MyRS As Recordset
Dim oAcct As GroupwareTypeLibrary.Account
Dim oApp As GroupwareTypeLibrary.Application
Dim oMsg As GroupwareTypeLibrary.Message
Dim sRecipient As String
Dim strSubject As String

Set oApp = CreateObject("NovellGroupWareSession")
Set oAcct = oApp.Login("", "")
Set oMsg = oAcct.MailBox.Messages.Add

Set MyDb = CurrentDb
Set MyRS = MyDb.OpenRecordset("Email Addresses", dbOpenDynaset)
MyRS.MoveFirst
Do Until MyRS.EOF
sRecipient = MyRS("EmailAddress")

MyRS.MoveNext

oMsg.Subject = "Subject - 0"
oMsg.BodyText = "Body Text"
oMsg.Recipients.Add sRecipient, , 0   '0 for To, 1 for CC, 2 for BCC
Loop
oMsg.Send

Set oMsg = Nothing
Set oApp = Nothing
Set oAcct = Nothing
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run Time Error 3075 15 47
DBF to ... Converter 5 49
Excess tables to Excel BackUp 3 29
Setting a DEFAULT value within a linked table 4 15
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now