Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Send custom emails from Access 2000 using Lotus Notes 7

Avatar of dabdowb
dabdowb asked on
Microsoft AccessLotus IBM
13 Comments1 Solution674 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
Avatar of AsishRaj
AsishRajFlag of Fiji image

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

Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answers