asked on
Send custom emails from Access 2000 using Lotus Notes 7
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
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
Lotus IBMMicrosoft Access
Last Comment
dabdowb