Email Procedure Fine Tuning

  • Thread starter Thread starter Neil Greenough
  • Start date Start date
N

Neil Greenough

I have a VBA code which allows me to send a new email using Lotus Notes from
an MS Access form - the code is behind a button.

I am after help on editing this code so that the email is automatically
addressed to a specific field in the form. I have a field called "email"
with the email button next to this. What I want is for, when clicked, for
the email to address itself to the email address in the field at that time.

I believe I have to insert the following into the below code, but could
somebody tell me where abouts?

Call notesdoc.replaceitemvalue("Sendto", Me!Email)
Call notesrtf.embedObject(1454, "", Me!Email, "Mail.rtf")

Here is the code this needs inserting into:-

Option Compare DatabaseOption Explicit '
##################################################
################################### ' Tested to work with Windows NT 4 and
higher NT-Versions (2000 / XP / 2003)' Tested with Lotus Client 5.07 and
higher ' The Lotus client has to be installed' If a password, different form
the NT one is choosen, Lotus has to be open' in beforehand before this
script can be used. ' ##################################################
################################### Declare Function SetForegroundWindow Lib
"user32" (ByVal hwnd As Long) As LongDeclare Function ShowWindow& Lib
"user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) Declare Function
FindWindow Lib "user32" Alias "FindWindowA" _(ByVal lpClassName As String,
ByVal lpWindowName As String) As Long Private Function
CreateNotesSession&(Optional edit As Boolean = True)Const notesclass$ =
"NOTES"' "Neues Memo - Lotus Notes"Const SW_SHOWMAXIMIZED = 3Dim
Lotus_Session As Object Dim rc&Dim lotusWindow&Set Lotus_Session =
CreateObject("Notes.NotesSession") DoEventsDoEventslotusWindow =
FindWindow(notesclass, vbNullString)If lotusWindow <> 0 Then If edit =
True Then rc = ShowWindow(lotusWindow, SW_SHOWMAXIMIZED) rc =
SetForegroundWindow(lotusWindow) CreateNotesSession& = True Else
CreateNotesSession& = True End IfElse CreateNotesSession& = FalseEnd
IfEnd Function Sub CreateMailandAttachFileAdr(Optional IsSubject As String =
"", Optional ByRef SendToAdr As Variant, Optional CCToAdr As Variant,
Optional BCCToAdr As Variant = "", Optional IsBody As String, Optional
Attach1 As String = "", Optional Attach2 As String = "", Optional MailDB As
String = "", Optional edit As Boolean = True, Optional Servr as String = "",
Optional Acct as String = "")Const EMBED_ATTACHMENT As Integer = 1454Const
EMBED_OBJECT As Integer = 1453Const EMBED_OBJECTLINK As Integer = 1452 Dim s
As Object ' use back end classes to obtain mail database nameDim db As
Object 'Dim doc As Object ' front end documentDim beDoc As Object ' back end
documentDim workspace As Object ' use front end classes to display to
userDim bodypart As Object ' Call CreateNotesSession&(edit) Set s =
CreateObject("Notes.Notessession") 'create notes sessionSet db =
s.GETDATABASE(Servr, Acct) 'set db to server and file name, if not supplied
via code will automatically use the default mail db If db.IsOpen = True
Then Else Call db.OPENMAIL 'open db to send mail End
IfSet beDoc = db.CREATEDOCUMENTSet bodypart =
beDoc.CREATERICHTEXTITEM("Body") ' Filling the
fields'###################beDoc.Subject = IsSubject 'Subject linebeDoc.body
= IsBody 'Mail messagebeDoc.SendTo = SendToAdr 'To:beDoc.CopyTo = CCToAdr
'CC:beDoc.BlindCopyTo = BCCToAdr 'Bcc:beDoc.From = IsFrom 'Sender's name.
May be anything (literally). 'Will show in the "From" field in most
email agentsbeDoc.PRINCIPAL = IsPrincipal 'Sending email address, must be a
valid notes account namebeDoc.SAVEONSEND = True 'tells notes to put message
in sent folder '''''''''''''''''''''''''''For multiple email addresses you
just set beDoc.sendto (or CopyTo or''BlindCopyTo) to an array of variants
each of which will receive the message. So: 'Dim recip() as variant'z = item
count'Redim recip(z)'y = 0'For i = 0 to ctrl.itemcount' recip(y) =
"emailaddress1"' y = y + 1'next i 'beDoc.sendto =
recip'''''''''''''''''''''''' ' Attaches I'###########' Call
bodypart.EmbedObject(EMBED_ATTACHMENT, "", DirWithPathFileName, FileName)If
Len(Attach1) > 0 Then If Len(Dir(Attach1)) > 0 Then Call
bodypart.EMBEDOBJECT(EMBED_ATTACHMENT, "", Attach1, Dir(Attach1)) End
IfEnd If ' Attaches II'############If Len(Attach2) > 0 Then If
Len(Dir(Attach2)) > 0 Then Call
bodypart.EMBEDOBJECT(EMBED_ATTACHMENT, "", Attach2, Dir(Attach2)) End
IfEnd If If edit = True Then Set workspace =
CreateObject("Notes.NotesUIWorkspace") 'Position cursor in body of
email Call workspace.EditDocument(True, beDoc).GotoField("Body")Else
beDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
with current date and time Call beDoc.Save (True, True) beDoc.SEND 0,
SendToAdrEnd If Set s = NothingEnd Sub

Many thanks
 
The "CreateMailandAttachFile" sub is coded to do that for you. Just pass it
the variable(s) when you click on the button. In the button's code add
variable names to match the variables in the sub, assign the values
(including ="" to assign blanks). The code will do that rest.

ex:

dim strSubject as string, strTo as string (etc)

strSubject = "Subject Line Text Here"
strTo = NameOfFieldWithEmailAddress
strCC = ""
strBCC = ""
(etc)

CreateMailandAttachFile strSubject, strTo, strCC, strBCC, strBody,
FirstFile, SecondFile, strMailDB, bEditBeforeSend, strServer, strAcct

If you're still unsure, go back to the source code's website (Access World
Forums and look in the Code Repository) and review KeithIT's notes on using
the code. It's well documented.

(By the way, you can copy and paste code straight from the module. It's
much easier to read with the formatting still in place ;)
 
Back
Top