How can I automate e-mail from Access? 1

[Turbo]

I have a database that I want to e-mail reports from based upon filtering criteria. How can I automate the e-mail from this database to numerous addresses?

Thanks, Turbo

"There is a fine line between confidence and conceit" - Ken Mai

 

[braindead2]

See the faq section of Access general forum

 

[Scoty]

Hey Turbo
This is how I sent mine

MyReport = "C:\SomeMiscFold\MyReport.rtf"
DoCmd.OutputTo acOutputReport, "MyReport", acFormatRTF, msa, False
DoCmd.SendObject acSendReport, "MyReport", acFormatRTF, "EveryOne@whereever.com", "EveryOneElse@whereever.com", "MyReport", "This is the MyReport for " & Now, False

Good Luck
Scoty :

 

[TheInsider]

Look into the MAPI ActiveX components. Rob Marriott
rob@career-connections.net

 

[sera]

Scoty,
Does this code work for many different types of e-mail. I am using lotus notes and have been told it is nearly impossible to e-mail from Access using Lotus Notes.
Thank You,
Sera

 

[Scoty]

I use groupwise. As far as the lotus notes. Have you tried IBM...Try a search for lotus notes. If worse comes to worse you can always try appactivate and sendkeys.
Scoty :

 

[Turbo]

All,

Thanks for your help I think this might help. Let me give it a try.

Thanks, Turbo

"There is a fine line between confidence and conceit" - Ken Mai

 

[Loekje]

Question for Scoty, or anyonone else.

My setup is GroupWise 5.5 on Win95 an MS-Access97, all localized in Dutch.

Sending mail through GW MAPI works, but I'm still stuck with a pop-up each time where I have to choose a profile (the only one: novell-standardprofile).
Crossing the checkbox as to set it default profile doesn't work.

How do I get rid of having to choose a profile???

TIA
Loek Gijben

 

[Scoty]

If you can get the name of the popup box (TRY task list while the box is up) you can use:

AppActivate "Novell PopUp" 
sendkey "~"

to get by that. I know it sounds kind of "pre-school" but with A97 vba sometime KISS (keep it simple, stupid) is call for.

Good luck
S2K :

 

[HenryAnthony]

Turbo,

I got the basis of this code from Kathryn (of this forum - she is awesome! Thanks again Kathryn) and use it to e-mail status reports to a list of recipients based on a query. It is activated from a commmand button. Basically, I am using 3 tables:

Projects = tblPro
Contacts = tblCon
Junction = tblJunction

tblJunction is used to set up a many-to-many relationship between Projects and Contacts.

Private Sub Command6_Click()

Dim dbs As Database
Dim strSQL As String
Dim qdf As QueryDef
Dim rst As Recordset
Dim qdfTemp As QueryDef

Set dbs = CurrentDb
strSQL = "SELECT TblJunction.ConID, TblCon.ConName, TblJunction.ProID, TblPro.ProName, TblCon.ConEmail FROM TblPro INNER JOIN (TblCon INNER JOIN TblJunction ON TblCon.ConID = TblJunction.ConID) ON TblPro.ProID = TblJunction.ProID WHERE (((TblJunction.ProID) = [ID]))ORDER BY TblJunction.ProID;"
Set qdf = dbs.CreateQueryDef("", strSQL)

qdf.Parameters!ID = Me!ProID 'fills the parameter

Set rst = qdf.OpenRecordset

rst.MoveFirst

Do Until rst.EOF

DoCmd.SendObject acSendReport, "Report1", acFormatRTF, _
rst!conemail, "", , _
"Project Status Report", , True, False

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing

End Sub

Best regards,

Henr¥

 

[pdupreez]

I have created the module emailer below to use whenever I want to e-mail something. I use Lotus Notes 4.63. If I have more than one user to e-mail to (on a regular basis) I have a database with email addresses and selection fields which will enable mailing specific reports when required. By using a do until rst.EOF (see below) you can e-mail selectively to specific users in your database.

The code requires Lotus Notes to be open and a user to be logged in. I must still figure out how to automatically login/assign a password.

************************
tblMailAddresses Table
************************
email Text Sets email address
Orders Yes/No Orders are mailed/Not
DailyReports Yes/No Reports are mailed/Not
etc....

***********************************
HOW TO SELECTIVELY E-MAIL USERS USING EMail FUNCTION
***********************************

Set rst = dbs.OpenRecordset("Select * from tblMailAddresses where Orders = True&quot
Do Until rst.EOF
Recipient = rst!EMail
EMail Subject, Bodytext, Recipient, Attachment, SaveIT
rst.MoveNext
Loop

***************************
EMail FUNCTION BELOW
***************************

Public Function EMail(Subject As String, Bodytext As String, Recipient As String, Attachment As String, SaveIT As Variant)

Dim Maildb As Object
Dim UserName As String
Dim MailDBName As String
Dim MailDoc As Object
Dim Session As Object
Dim AttachME As Object
Dim EmbedObj As Object

Set Session = CreateObject("Notes.NotesSession&quot
UserName = Session.UserName
MailDBName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " &quot)) & ".nsf"
Set Maildb = Session.getdatabase("", MailDBName)

If Maildb.isopen = True Then
Else
Maildb.openmail
End If

Set MailDoc = Maildb.createdocument
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = Bodytext
MailDoc.SavemessageonSend = SaveIT

If Attachment <> &quot;&quot; Then
Set AttachME = MailDoc.createrichtextitem(&quot;Attachment&quot
Set EmbedObj = AttachME.embedobject(1454, &quot;&quot;, Attachment, &quot;Attachment&quot
MailDoc.createrichtextitem (Attachment)
End If

MailDoc.send 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

Exit Function

If Err.number = 7063 Then
MsgBox &quot;Notes Mail System not open&quot; & Chr(13) & _
&quot;Please Activate Mail system and Retry again&quot;
Resume Next
End If

MsgBox Err.Description
Resume Next

End Function

 

[Quehay]

Henry,

Do you have McAfee or other virus-blocking software in place? These things get edgy when you send the same mail content/header repeatedly.

 

[HenryAnthony]

Quehay,

McAfee is in place but does not interfere. Please note, I know nothing about virus-blocking software issues.

Best regards,

Henr¥