Solved

auto email

Posted on 2004-09-27
41
306 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

21 Experts available now in Live!

Get 1:1 Help Now