troubleshooting Question

Send custom emails from Access 2000 using Lotus Notes 7

Avatar of dabdowb
dabdowb asked on
Lotus IBMMicrosoft Access
13 Comments2 Solutions674 ViewsLast Modified:
OK, welcome to the insanity!  I have code below for an email routine I currently use in an Access 2000 database.  It works just fine as is, however, my department approached me with some new ideas and I am just not quite certain how to tackle it.  I checked a few other questions that have great solutions, just not quite what I need...I don't think.

Here is the task.  An operator will enter the database, and choose an item from a drop down list that they need to edit.  One item can have several emails identified with it (all to be stored in a table where there is one email per record in the table...each row consisting of item, location, customer, email address).  So if the operator needs to edit data for just one of say 5 customers, the desire is to have an autogenerated email (attached via VBA code with the 'On Event' trigger of the form) to be sent to each of the other 4 email addresses associated with that particular item, with a predetermined body of text inserted, and the subject line listed as the corresponding item, location, customer data associated with that email address specifically.  Instead of the email list below being hard coded as Recipient(0), Recipient(1),etc., I need this to be dynamic and fed from the results of a query to see what other contacts there are besides the one customer already edited.  

I have seen a few entries on this site that showed how to have a formatted email sent to multiple addresses, but what I am looking for here is an email being sent to each address individually....so if there are 4 email addresses, I should have 4 emails in my sent folder when the process is done, with the subject line of each email being specific to that email address' item, location and customer per the master email table.

There is discussion of being able to attach a set file to each email as well (an .xls spreadsheet most likely), but I figured one step at a time.  As mentioned above, the verbage in the actual body of the email will be set, so that at least can be as simple as what I have below, but just not wrapping my head around how to make the rest of it work.

Thoughts?

Thanks
Function EmailContacts()
Dim Recipient(13) As String
 
here2:
 'For email notification subroutine:
 
Recipient(0) = "Matt Fiedler/matmgt/sigma/sial"
'Recipient(1) = "Dave Dresch/salesup/resrch/sial"
 
Subject = "Daily Updates Completed..."
Message = "The Daily Updates have been made.  Please make Zbost file for planners."
GoSub SendMail
 
SendMail:
'************************************************************
'Sends Email Notification alerting group of update
'************************************************************
Dim Session As Object
Dim Database As Object
Dim Doc As Object
'session and database declared in general as object
Set Session = CreateObject("Notes.NotesSession") 'create notes session
Set Database = Session.GETDATABASE("", "") 'set db to database not yet named
Call Database.OPENMAIL 'Open the users mail database
recip = 0
For i = recip To 0
'i = 0
Set Doc = Database.CREATEDOCUMENT 'create a new document in mail database
Call Doc.replaceitemvalue("SendTo", Recipient(i)) 'create sendto field
Call Doc.replaceitemvalue("Subject", Subject) 'create subject field
Call Doc.replaceitemvalue("Body", Message) 'create body field
Call Doc.Send(False) 'send the message - if unsucessful, an error
Next
'Note: The Session object contains Database and Document. Destroying parent gets rid of others.
Set Session = Nothing ' close connection to free memory
'end of e-mail routine
'Return
 
DoCmd.SetWarnings True
End Function
ASKER CERTIFIED SOLUTION
AsishRaj

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros