• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

auto email

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
TimKestermont
Asked:
TimKestermont
  • 22
  • 18
1 Solution
 
TimKestermontAuthor Commented:
This is the link I accepted as the answer.
http://www.experts-exchange.com/Databases/MS_Access/Q_20768220.html
0
 
flavoCommented:
DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strMsg, 0

Dave
0
 
TimKestermontAuthor Commented:
Is there a reference in the library that needs to be checked?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
flavoCommented:
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
 
flavoCommented:
these may also be a problem : chaneg them to....

strTo = MyRS("[Email Address]")

and

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

Dave
0
 
TimKestermontAuthor Commented:
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
 
flavoCommented:
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
 
TimKestermontAuthor Commented:
It is now stopping when the email pulls up and sets focus to strTo.
0
 
TimKestermontAuthor Commented:
Then I try without the spaces by renaming the fields and it stops at the docmd. again.
0
 
flavoCommented:
you sure you got it in the right spot

pls post you new code

Dave
0
 
TimKestermontAuthor Commented:
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
 
flavoCommented:
use this!

DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strMsg, 0
0
 
TimKestermontAuthor Commented:
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
 
flavoCommented:
Do you have Outlook or Groupwise or something like that installed??
0
 
flavoCommented:
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
 
TimKestermontAuthor Commented:
Groupwise
0
 
TimKestermontAuthor Commented:
I'm not for sure about MAPI because this worked fine a couple of months ago.
0
 
flavoCommented:
I use Groupwise too (Version 6.0, think it used to have 5.5 and that worked fine too).
0
 
flavoCommented:
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
 
TimKestermontAuthor Commented:
Same break at the docmd.sendobject.
0
 
TimKestermontAuthor Commented:
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
 
TimKestermontAuthor Commented:
Just will not get pass the To field or StrTo.
0
 
flavoCommented:
Just working on another method
0
 
flavoCommented:
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
 
flavoCommented:
Add a refrence to Groupware Type library
0
 
TimKestermontAuthor Commented:
I will let you know tomorrow at work.  Thanks
0
 
TimKestermontAuthor Commented:
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
 
TimKestermontAuthor Commented:
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
 
TimKestermontAuthor Commented:
Sorry, last comment was incorrect
0
 
TimKestermontAuthor Commented:
This works on reports-

DoCmd.SendObject acSendReport, "Heyreport", acFormatRTF, strTo, strCC, strBCC, strSubject, strMsg, Val(0)
0
 
TimKestermontAuthor Commented:
This does not
DoCmd.SendObject acSendNoObject, , "MS-DOS Text (*.txt)", strTo, strCC, strBCC, strSubject, strMsg, Val(0)
0
 
flavoCommented:
It works fine with Win 2k / Access 97 / Groupwise and Win XP / Access 2003 / Outlook 2003 (Tested at home and at work)
0
 
TimKestermontAuthor Commented:
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
 
flavoCommented:
one last go!

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


What error does it throw anyway?????
Dave
0
 
TimKestermontAuthor Commented:
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
 
flavoCommented:
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
 
flavoCommented:
Thanks Alan!
0
 
TimKestermontAuthor Commented:
Can I do the CurrentDB recordset with this code and pull from the db?'
0
 
flavoCommented:
You sure can mate.

What exactly are you trying to do???
0
 
TimKestermontAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 22
  • 18
Tackle projects and never again get stuck behind a technical roadblock.
Join Now